WITH

flow_sol_base as (

SELECT

trunc(block_timestamp,'month') as month,

node_id as validator,

sum(amount) as flow_delegated

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

group by 1,2 order by 1 asc

),

flow_sol_base2 as (

SELECT

trunc(block_timestamp,'month') as month,

node_id as validator,

sum(amount) as flow_withdrawn

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

group by 1,2 order by 1 asc

),

flow_sol_base3 as(select

ifnull(a.month, b.month) as months,

ifnull(a.validator, b.validator) as vote_accounts,

contract_name as validator_names,

ifnull(flow_delegated, 0) as flow_delegatedz,

ifnull(flow_withdrawn, 0) as flow_withdrawnz,

ifnull(flow_delegatedz, 0) - ifnull(flow_withdrawnz,0) as net_flow_delegated,

rank() over (partition by months order by net_flow_delegated desc) as validator_ranks

from flow_sol_base a

full outer join flow_sol_base2 b

on a.month=b.month and a.validator=b.validator --and a.vote_account = b.vote_account and a.validator_rank = b.validator_rank and stake_authority = withdraw_destination

left join flow.core.dim_contract_labels c