staking as (

SELECT

date_trunc('month',block_timestamp) as date,

delegator,

sum(amount) as volume,

sum(volume) over (partition by delegator order by date) as cum_volume,

avg(amount) as avg_volume,

median(amount) as median_volume,

avg(volume) over (partition by delegator order by date rows between 6 preceding and current row) as avg_7d_ma_volume

from flow.core.ez_staking_actions where action in ('DelegatorTokensCommitted','TokensCommitted')

group by 1,2 order by 1 asc

),

unstaking as (

SELECT

date_trunc('month',block_timestamp) as date,

delegator,

sum(amount) as volume,

sum(volume) over (partition by delegator order by date) as cum_volume,

avg(amount) as avg_volume,

median(amount) as median_volume,

avg(volume) over (partition by delegator order by date rows between 6 preceding and current row) as avg_7d_ma_volume

from flow.core.ez_staking_actions where action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn')

group by 1,2 order by 1 asc

),

final as (

ifnull(x.date,y.date) as dates,

ifnull(x.delegator,y.delegator) as delegator,

ifnull(x.cum_volume,0) as total_staked_volume,

ifnull(y.cum_volume*(-1),0) as total_unstaked_volume,

total_staked_volume+total_unstaked_volume as total_net_staked_volume,

rank () over (partition by dates order by total_net_staked_volume desc ) as pos

from staking x

left outer join unstaking y on x.date=y.date and x.delegator=y.delegator

order by 1 asc