staking AS (

SELECT

trunc(block_timestamp, 'month') AS date,

count(distinct tx_id) AS staking_transactions,

count(distinct delegator) AS staking_delegators,

sum(amount) AS staked_volume

FROM flow.gov.ez_staking_actions

WHERE action IN ('DelegatorTokensCommitted', 'TokensCommitted')

GROUP BY trunc(block_timestamp, 'month')

ORDER BY trunc(block_timestamp, 'month') ASC

),

unstaking AS (

SELECT

trunc(block_timestamp, 'month') AS date,

count(distinct tx_id) AS unstaking_transactions,

count(distinct delegator) AS unstaking_delegators,

sum(amount) AS unstaked_volume

FROM flow.gov.ez_staking_actions

WHERE action IN ('UnstakedTokensWithdrawn', 'DelegatorUnstakedTokensWithdrawn')

GROUP BY trunc(block_timestamp, 'month')

ORDER BY trunc(block_timestamp, 'month') ASC

),

combined AS (

SELECT

COALESCE(s.date, u.date) AS date,

COALESCE(staking_transactions, 0) AS staking_transactions,

COALESCE(staking_delegators, 0) AS staking_delegators,

COALESCE(staked_volume, 0) AS staked_volume,

COALESCE(unstaking_transactions, 0) AS unstaking_transactions,

COALESCE(unstaking_delegators, 0) AS unstaking_delegators,

COALESCE(unstaked_volume, 0) AS unstaked_volume

FROM staking s

FULL OUTER JOIN unstaking u ON s.date = u.date

ORDER BY COALESCE(s.date, u.date) ASC

)