select mindate::date as day, count (distinct newusers) as New_Users

from (select signers[0] as newusers, min (block_timestamp) as mindate from solana.core.fact_transactions group by 1)

where mindate >= CURRENT_DATE - 21

group by 1),

maintable as (

select block_timestamp::date as date,

case when date >= '2022-11-08' then 'After Collapse'

else 'Before Collapse' end as timespan,

new_users,

count (distinct tx_id) as TX_Count,

count (distinct signers[0]) as Users_Count

from solana.core.fact_transactions t1 join newtable t2 on t1.block_timestamp::Date = t2.day

where block_timestamp >= CURRENT_DATE - 21

and succeeded = 'TRUE'

group by 1,2,3)

select timespan,

avg (Tx_count) as Average_TX_Count,

avg (users_count) as Average_Users_count,

avg (new_users) as Average_New_Users

from maintable

group by 1