select date(BLOCK_TIMESTAMP) as swap_date,tx_id,SWAPPER,SWAP_FROM_AMOUNT,SWAP_FROM_MINT

from solana.core.fact_swaps

where BLOCK_TIMESTAMP::date>=CURRENT_DATE()-60

),

price2 as(

select date(BLOCK_TIMESTAMP) as date,s.SWAP_FROM_MINT,sum(SWAP_TO_AMOUNT)/sum(s.SWAP_FROM_AMOUNT) as price

from solana.core.fact_swaps s join price1 p on s.SWAP_FROM_MINT=p.SWAP_FROM_MINT

where s.SWAP_FROM_MINT=p.SWAP_FROM_MINT and SWAP_TO_MINT='EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and SWAP_TO_AMOUNT>0 and s.SWAP_FROM_AMOUNT>0 and date>=CURRENT_DATE()-60

group by 1,2

),

price_usd as (

select swap_date,tx_id, swapper,p1.SWAP_FROM_AMOUNT*price as amount_usd

from price1 p1 left outer join price2 p2 on swap_date=p2.date

where p1.SWAP_FROM_MINT=p2.SWAP_FROM_MINT

)

select --swap_date,

case when swap_date >'2022-11-07' then 'After FTX' else 'Befor FTX' end as time,

count(distinct tx_id) as count_tx, count(distinct SWAPPER) as users,

sum(amount_usd) as volume,avg(amount_usd) as avg_usd --,sum(users) over (order by swap_date) as cum_user

from price_usd

group by 1