with newtable as (

select mindate::date as day,

count (distinct user1) as New_Users,

sum (new_users) over (order by day) as Total_Users

from (

select instruction:accounts[1] as user1,

min (block_timestamp) as mindate

from solana.core.fact_events

where program_id in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o','4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')

and succeeded = 'TRUE'

group by 1)

group by 1),

maintable as (

select block_timestamp::date as date,

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

else 'Before FTX Collapse' end as timespan,

new_users,

total_users,

count (distinct tx_id) as TX_Count,

count (distinct instruction:accounts[1]) as Users_Count

from solana.core.fact_events t1 join newtable t2 on t1.block_timestamp::date = t2.day

where program_id in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o','4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')

and succeeded = 'TRUE'

and block_timestamp >= CURRENT_DATE - 21

group by 1,2,3,4)

select timespan,

avg (new_users) as Average_new_users,

avg (tx_count) as Average_TX_Count,

avg (users_count) as Average_Users_Count

from maintable

group by 1