case

when action ilike '%deposit%' then 'Deposit'

when action ilike '%withdraw%' then 'Withdraw'

-- when action ilike '%claim%' then 'Claim'

-- when action ilike '%order_unstake%' then 'Order Unstake'

end as action_type,

*

from

solana.core.fact_stake_pool_actions

succeeded and action_type is not null

), daily_pool_actions as (

select

action_type,

date_trunc('month', block_timestamp) as day,

count(distinct tx_id) as transactions_count,

count(distinct stake_pool_name) as pools_count,

sum(ifnull(amount, 0)/1e9) as volume

from

pool_actions

group BY

action_type, day

select

action_type,

day,

volume as daily_volume,

avg(daily_volume) over (partition by action_type order by day asc rows between 1 preceding and current row) as average_daily_volume,

transactions_count as daily_transactions_count,

avg(daily_transactions_count) over (partition by action_type order by day asc rows between 1 preceding and current row) as average_transactions_count,

pools_count as daily_pools_count,

avg(daily_pools_count) over (partition by action_type order by day asc rows between 1 preceding and current row) as average_pools_count

from

daily_pool_actions