with nearpricet as (

select timestamp::date as day,

avg (price_usd) as usdprice

from near.core.fact_prices

where symbol = 'wNEAR'

group by 1),

solpricet as (

select block_timestamp::date as day,

median (swap_to_amount/swap_from_amount) as USDPrice

from solana.fact_swaps

where swap_from_mint = 'So11111111111111111111111111111111111111112'

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

and swap_to_amount > 0

and swap_from_amount > 0

and succeeded = 'TRUE'

group by 1

)

select 'NEAR' as chain,

date_trunc (day,block_timestamp) as date,

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

else 'Before Collapse' end as timespan,

count (distinct tx_hash) as TX_Count,

count (distinct tx_signer) as Users_Count,

sum ((stake_amount*usdprice)/1e24) as Total_Volume,

avg ((stake_amount*usdprice)/1e24) as Average_Volume,

sum (Total_Volume) over (order by date) as Cumulative_Volume

from near.core.dim_staking_actions t1

join nearpricet t2 on t1.block_timestamp::date = t2.day

where action = 'Unstake'

and block_timestamp >= CURRENT_DATE - 30

group by 1,2,3

union ALL