with pricet as (

select block_timestamp::date as day,

swap_from_mint as token,

median (swap_to_amount/swap_from_amount) as USDPrice

from solana.fact_swaps

where swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') --USDC,USDT

and swap_to_amount > 0

and swap_from_amount > 0

and block_timestamp >= CURRENT_DATE - 28

and succeeded = 'TRUE'

group by 1,2),

srmtable as (

select tx_id

from solana.core.fact_transactions

where instructions[0]:programId in ('9xQeWvG816bUx9EPjHmaT23yvVM2ZWbrrpZb9PusVFin','EUqojwWA2rd19FZrzeBncJsm38Jm1hEhE3zsmX3bRc2o','4ckmDgGdxQoPDLUkDT3vHgSAkzA3QRdNq5ywwY4sUSJn','BJ3jrUzddfuSrZHXSCxMUUQsjKEyLmuuyZebkcaFp2fg')

and succeeded = 'TRUE'

and block_timestamp >= CURRENT_DATE - 28)

select block_timestamp::date as date,

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

else 'Before Collapse' end as timespan,

sum (amount*usdprice) as Volume,

sum(Volume) over (order by date) as Cum_volume

from solana.core.fact_transfers t1 join pricet t2 on t1.block_timestamp::Date = t2.day and t1.mint = t2.token

where tx_id in (select tx_id from srmtable)

and block_timestamp >= CURRENT_DATE - 28

group by 1,2

order by 1