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

)

select block_timestamp::date as date,

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

else 'Before FTX crisis' end as period,

New_Users,

Total_Users,

count (distinct tx_id) as trxs,

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

from solana.core.fact_events a join newuser b on a.block_timestamp::Date = b.day

where program_id in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o',

'4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')

and succeeded = 'TRUE'

and block_timestamp >= CURRENT_DATE - 24

group by 1, 2, 3, 4