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

),

final as (SELECT

*,

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

from staking_aggregates

where date >= current_date() - {{n_days}}

and date< current_date()),

final_staking as (select

date,

sum(number_of_stakers) as total_stakers,

sum(number_transactions) as total_transactions,

sum(amount_near_staked) as total_near_staked,

avg(near_per_staker) as total_avg_near_per_staker,

avg(total_stakers) OVER(ORDER BY date

ROWS BETWEEN 8 PRECEDING AND CURRENT ROW )

as "7d_MA_stakers",

avg(total_transactions) OVER(ORDER BY date

ROWS BETWEEN 8 PRECEDING AND CURRENT ROW )

as "7d_MA_tx",