trunc(block_timestamp,'week') as weeks,

vote_account,

validator_rank as validator,

count(distinct tx_id) as tx,

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

from solana.core.ez_staking_lp_actions

where post_tx_staked_balance > pre_tx_staked_balance

and event_type = 'delegate'

and succeeded = 'TRUE'

and block_timestamp >= '2022-01-01'

and node_pubkey is not null

group by 1,2,3),

totals as (

SELECT

weeks,

sum(near_staked) as week_near_staked,

sum(week_near_staked) over (order by weeks)as total_near_staked

from weekly

group by 1 order by 1

),

ranking as (

SELECT

weeks,

validator,

sum(tx) as txs,

sum(near_staked) as total_near_delegated,

sum(total_near_delegated) over (partition by validator order by weeks) as cumulative_near_delegated

FROM weekly

group by 1,2

select

x.weeks,

total_near_staked,

count(distinct validator) as n_validators