date_trunc('day', block_timestamp) as date,

'stake' as type,

count(distinct tx_id) as tx_count,

count(distinct address) as unique_user,

sum(amount/1e9) as tx_volume,

avg(amount/1e9) as avg_volume,

-- ma7

avg(tx_count) over (order by date, date rows between 6 preceding and current row) as ma7_tx_count,

avg(unique_user) over (order by date, date rows between 6 preceding and current row) as ma7_unique_user,

avg(tx_volume) over (order by date, date rows between 6 preceding and current row) as ma7_tx_volume,

avg(avg_volume) over (order by date, date rows between 6 preceding and current row) as ma7_avg_volume,

-- cum

sum(tx_count) over (order by date) as cum_tx_count,

sum(tx_volume) over (order by date) as cum_tx_volume

from solana.core.fact_stake_pool_actions

where

date >= current_date - 7 and

action ilike '%deposit%' and

succeeded = 'TRUE'

group by 1

union all

select

date_trunc('day', block_timestamp) as date,

'Unstake' as type,

count(distinct tx_id) as tx_count,

count(distinct address) as unique_user,

sum(amount/1e9) as tx_volume,

avg(amount/1e9) as avg_volume,

-- ma7

avg(tx_count) over (order by date, date rows between 6 preceding and current row) as ma7_tx_count,