case

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

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

end as action_type,

*

from

solana.core.fact_stake_pool_actions

succeeded

), daily_pool_actions as (

select

action_type,

STAKE_POOL_NAME,

date_trunc('day', 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, STAKE_POOL_NAME

select

day,

STAKE_POOL_NAME,

sum(transactions_count) as tx_cnt,

avg(tx_cnt) over (partition by STAKE_POOL_NAME order by day asc rows between 7 preceding and current row) as avg_tx_cnt,

sum(tx_cnt) over (partition by STAKE_POOL_NAME order by day asc) as cum_tx_cnt

from

daily_pool_actions

group by

day, STAKE_POOL_NAME