t1 as (

select

block_timestamp::date as day,

count(distinct tx_id) as staked_count,

sum(amount/1e6) as staked_volume,

avg(amount/1e6) as avg_staked_volume,

count(distinct delegator_address) as stakers_count,

sum(staked_count) over (order by day) as cumulative_staked_count,

sum(staked_volume) over (order by day) as cumulative_staked_volume,

sum(stakers_count) over (order by day) as cumulative_stakers_count

from osmosis.core.fact_staking

where action = 'delegate'

and tx_succeeded = 1

group by 1

),

t2 as (

select

block_timestamp::date as day,

count(distinct tx_id) as unstaked_count,

sum(amount/1e6) as unstaked_volume,

avg(amount/1e6) as avg_unstaked_volume,

count(distinct delegator_address) as unstakers_count,

sum(unstaked_count) over (order by day) as cumulative_unstaked_count,

sum(unstaked_volume) over (order by day) as cumulative_unstaked_volume,

sum(unstakers_count) over (order by day) as cumulative_unstakers_count

from osmosis.core.fact_staking

where action = 'undelegate'

and tx_succeeded = 1

group by 1

select *,

staked_volume - unstaked_volume as net_staked,

avg_staked_volume - avg_unstaked_volume as avg_net_staked