date_trunc('day', a.block_timestamp) as date,

tx_receiver as governor,

COUNT(DISTINCT tx_signer) as number_of_stakers,

COUNT(DISTINCT a.TX_HASH) as number_transactions,

SUM(b.tx:actions[0]:FunctionCall:deposit/pow(10,24)) amount_near_staked,

SUM(b.tx:actions[0]:FunctionCall:deposit/pow(10,24))/COUNT(DISTINCT tx_signer) as near_per_staker

FROM near.core.fact_actions_events_function_call a

JOIN near.core.fact_transactions b

ON a.tx_hash = b.tx_hash

WHERE method_name = 'deposit_and_stake'

AND b.block_timestamp::date < CURRENT_DATE

AND a.block_timestamp::date < CURRENT_DATE

GROUP BY date, governor

SELECT

*,

sum(amount_near_staked) over (partition by governor order by date) as cumulative_delegation

from staking_aggregates