with

withdrawals as (

SELECT

distinct address_name as pool,

block_timestamp,

tx_id,

tx_to,

amount*close as amount_usd

from solana.core.fact_transfers x

join solana.core.dim_labels y on x.tx_from=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%'

--and x.index like '%4%'

) ,

withdrawals2 as (

select * from withdrawals

where tx_id in (select tx_id from solana.core.fact_events where program_id='whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc')

)

SELECT

trunc(block_timestamp,'week') as week,

pool,

sum(1) as daily_withdrawals,

sum(daily_withdrawals) over (partition by pool order by week) as cum_withdrawals,

LAG(daily_withdrawals,1) IGNORE NULLS OVER (partition by pool ORDER BY week) as last_withdrawals,

case when last_withdrawals>0 then ((daily_withdrawals-last_withdrawals)/last_withdrawals)*100 else 0 end as daily_withdrawals_change,

count(distinct tx_to) as daily_users,

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

LAG(daily_users,1) IGNORE NULLS OVER (partition by pool ORDER BY week) 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 week asc) as cum_amount,

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

case when last_amount>0 then ((daily_amount-last_amount)/last_amount)*100 else 0 end as daily_amount_change,