block_timestamp,

tx_id as tx,

stake_authority as validator,

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 node_pubkey is not null

),

monthly as (

SELECT

trunc(block_timestamp,'day') as days,

tx,

validator,

sol_delegated

--amount_staked/pow(10,24) as sol_delegated

FROM stakes WHERE sol_delegated is not null

),

totals as (

SELECT

days,

sum(sol_delegated) as month_sol_delegated,

sum(month_sol_delegated) over (order by days)as total_sol_delegated

from monthly

group by 1 order by 1

),

ranking as (

SELECT

days,

validator,

count(distinct tx) as txs,

sum(sol_delegated) as total_near_delegated,