select block_timestamp::date as date,

case when action ilike '%deposit%' then 'Stake/Deposit'

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

when action ilike 'order_unstake' then 'Order to Unstake'

when action ilike 'claim' then 'Claim Rewards'

else null end as action_type,

count (distinct tx_id) as TX_Count,

count (distinct address) as Users_Count,

sum (amount/1e9) as Total_Volume,

avg (amount/1e9) as Average_Volume,

sum (TX_Count) over (partition by action_type order by date ) as Cumulative_TX_Count,

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

from solana.core.fact_stake_pool_actions

where succeeded = 'TRUE'

and block_timestamp >= CURRENT_DATE - 30

and action_type is not null

group by 1,2)

select action_type,

avg (tx_count) as Average_TX_Count,

avg (users_count) as Average_Users_Count,

avg (total_volume) as Average_Volume

from maintable

group by 1