staking as (

SELECT

trunc (block_timestamp,'day') as date,

stake_pool_name as pool,

--case when x.block_timestamp < '2022-11-08' then 'Previous to FTX/Alameda news' else 'After the FTX/Alameda news' end as period,

count(distinct tx_id) as staking_txs,

count(distinct address) as staking_users

from solana.core.fact_stake_pool_actions

WHERE block_timestamp >= '2022-11-01' and succeeded=TRUE and action in ('deposit','deposit_stake')

GROUP BY 1, 2

order by 1 asc

),

unstaking as (

SELECT

trunc (block_timestamp,'day') as date,

stake_pool_name as pool,

--case when x.block_timestamp < '2022-11-08' then 'Previous to FTX/Alameda news' else 'After the FTX/Alameda news' end as period,

count(distinct tx_id) as unstaking_txs,

count(distinct address) as unstaking_users

from solana.core.fact_stake_pool_actions

WHERE block_timestamp >= '2022-11-01' and succeeded=TRUE and action in ('withdraw','withdraw_stake')

GROUP BY 1, 2

order by 1 asc

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

ifnull(x.pool,y.pool) as pools,

ifnull(staking_txs,0) as staking_transactions,ifnull(unstaking_txs,0) as unstaking_transactions,

staking_transactions-unstaking_transactions as net_txs,

ifnull(staking_users,0) as staking_user,ifnull(unstaking_users,0) as unstaking_user,

staking_user-unstaking_user as net_users

from staking x

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

order by 1 asc