pool,

sum(1) as daily_deposits,

sum(daily_deposits) over (partition by pool order by day) as cum_deposits,

LAG(daily_deposits,1) IGNORE NULLS OVER (partition by pool ORDER BY day) as last_deposits,

case when last_deposits>0 then ((daily_deposits-last_deposits)/last_deposits)*100

else 0 end as daily_deposits_change,

count(distinct tx_from) as daily_users,

sum(daily_users) over (partition by pool order by day) as cum_users,

LAG(daily_users,1) IGNORE NULLS OVER (partition by pool ORDER BY day) as last_users,

case when last_users>0 then ((daily_users-last_users)/last_users)*100

else 0 end as daily_users_change,

sum(amount_usd) as daily_amount,

sum(daily_amount) over (partition by pool order by day asc) as cum_amount,

LAG(daily_amount,1) IGNORE NULLS OVER (partition by pool ORDER BY day) as last_amount,

case when last_amount>0 then ((daily_amount-last_amount)/last_amount)*100

else 0 end as daily_amount_change,

avg(amount_usd) as avg_amount_deposited

from (select *

from (SELECT distinct address_name as pool,

block_timestamp,

tx_id,

tx_from,

amount*close as amount_usd

from solana.core.fact_transfers x

join solana.core.dim_labels y on x.tx_to=y.address

join solana.core.ez_token_prices_hourly z on x.mint=z.token_address and trunc(x.block_timestamp,'hour')=z.recorded_hour

where block_timestamp>='2022-03-23' -- orca whirlpools launch

and label_type = 'dex'

and label_subtype = 'pool'

and label ='orca'

and address_name not like '%aquafarm%'

)

where tx_id in (select tx_id

from solana.core.fact_events

where program_id='whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc')