pricet as (

select

recorded_hour::date as day,

token_address,

avg(close) as price_usd

from solana.core.ez_token_prices_hourly

group by 1, 2

),

t2 as (

pricet.day,

address_name as pool,

iff(block_timestamp >= '2022-12-24', 'After BONK', 'Before BONK') as timespan,

count(distinct tx_from) as users_count,

sum(amount * price_usd) as volume_deposited_usd,

avg(amount * price_usd) as avg_amount_deposited_usd,

median(amount * price_usd) as median_amount_deposited_usd,

count(distinct tx_id) as deposits_count

from solana.core.fact_transfers a

join solana.core.dim_labels c

join pricet

on a.tx_to = c.address

and a.mint = pricet.token_address

and pricet.day = date_trunc('day', block_timestamp)

where label_type = 'dex'

and label_subtype = 'pool'

and label ='orca'

and address_name not like '%aquafarm%'

and exists (

select * from solana.core.fact_events b

where b.tx_id = a.tx_id

and program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc'

)

and block_timestamp::date between date('2022-12-24') - 21 and date('2022-12-24') + 21