select

block_timestamp::date as date

,STAKE_POOL_NAME

,count(DISTINCT tx_id) as tx_count

,count(DISTINCT address) as stakers

,sum (amount/1e9) as total_volume

,avg (amount/1e9) as average_volume

,sum(tx_count) over(partition by STAKE_POOL_NAME order by date) as growth_tx_count

,sum(stakers) over(partition by STAKE_POOL_NAME order by date) as growth_stakers

,sum(Total_Volume) over(partition by STAKE_POOL_NAME order by date) as growth_Total_Volume

from solana.core.fact_stake_pool_actions

where block_timestamp::date >= CURRENT_DATE-7

and SUCCEEDED=true

and action ilike '%deposit%'

group by 1,2

order by 1

,lst_unstake as (

select

-- block_timestamp::date as date

STAKE_POOL_NAME

,count(DISTINCT tx_id) as tx_count

,count(DISTINCT address) as stakers

,sum (amount/1e9) as total_volume

,avg (amount/1e9) as average_volume

-- ,sum(tx_count) over(order by date) as growth_tx_count

-- ,sum(stakers) over(order by date) as growth_stakers

-- ,sum(Total_Volume) over(order by date) as growth_Total_Volume

from solana.core.fact_stake_pool_actions

where block_timestamp::date >= CURRENT_DATE-7

and SUCCEEDED=true

and (action ilike '%withdraw%' or action ilike '%unstake%')

group by 1

order by 1