with stakeTb as (
select
node_id,
sum(amount) as Staked_Volume
from flow.core.ez_staking_actions
where tx_succeeded = 'TRUE'
and action in ('DelegatorTokensCommitted','TokensCommitted')
group by 1
)
, unstakeTb as (
select
node_id,
sum(amount) as UnStaked_Volume
from flow.core.ez_staking_actions
where tx_succeeded = 'TRUE'
and action in ('DelegatorUnstakedTokensWithdrawn','UnstakedTokensWithdrawn')
group by 1
)
select
node_id,
Staked_Volume - UnStaked_Volume as Net_Staked_Vol,
row_number() over (order by Net_Staked_Vol DESC) as rank,
rank || '. ' || left(node_id, 4) || '...' || right(node_id, 4) as validator
from stakeTb a join unstakeTb b USING(node_id)
-- group by 1