date_trunc('day', block_timestamp) as day,

stake_authority,

sum(post_tx_staked_balance/1e9 - pre_tx_staked_balance/1e9) as sol_delegated

from solana.core.ez_staking_lp_actions

where post_tx_staked_balance > pre_tx_staked_balance

and pre_tx_staked_balance = 0

and event_type = 'delegate'

and succeeded = 'TRUE'

and day >= '2022-01-01'

and node_pubkey is not null

group by 1,2),

base2 as (select

date_trunc('day', block_timestamp) as day,

withdraw_destination,

sum(withdraw_amount/1e9) as sol_withdrawn

from solana.core.ez_staking_lp_actions

where event_type = 'withdraw'

and day >= '2022-01-01'

and succeeded = 'TRUE'

and node_pubkey is not null

group by 1,2),

base3 as (select ifnull(a.day, b.day) as days,

ifnull(a.stake_authority, b.withdraw_destination) as stake_authoritys,

ifnull(sol_delegated, 0) as sol_delegatedz,

ifnull(sol_withdrawn, 0) as sol_withdrawnz,

sum(sol_delegatedz) over (partition by stake_authoritys order by days) as cumulative_sol_delegated,

sum(sol_withdrawnz) over (partition by stake_authoritys order by days) as cumulative_sol_withdrawn,

ifnull(cumulative_sol_delegated, 0) - ifnull(cumulative_sol_withdrawn,0) as cumulative_net_sol_delegated,

case

when cumulative_net_sol_delegated > 0 then 'Active' else 'Inactive' end as activeness,

case

when cumulative_sol_delegated = sol_delegatedz and activeness = 'Active' then 1

when cumulative_sol_delegated > 0 and activeness = 'Active' then 0