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

tx_receiver as governor,

COUNT(DISTINCT tx_signer) as number_of_unstakers,

COUNT(DISTINCT a.TX_HASH) as number_transactions,

try_parse_json(replace(args, '\\')):amount as amount, -- raw amount

amount/pow(10,24) as amount_near_unstaked,

iff(args::string LIKE '%,%',1,0) as check_sys,

amount_near_unstaked/COUNT(DISTINCT tx_signer) as near_per_unstaker

FROM near.core.fact_actions_events_function_call a

JOIN near.core.fact_transactions b

ON a.tx_hash = b.tx_hash

WHERE tx_receiver ilike '%.pool%'

AND method_name in ( 'unstake_all', 'unstake', 'withdraw_token')

AND check_sys = 0

AND a.block_timestamp::date < CURRENT_DATE

AND b.block_timestamp::date < CURRENT_DATE

GROUP BY date, governor, args

),

final as (SELECT

*,

sum(amount_near_unstaked) over (partition by governor order by date) as cumulative_undelegation

from unstaking_aggregates

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

and date< current_date()),

final_unstaking as (select

date,

sum(number_of_unstakers) as total_unstakers,

sum(number_transactions) as total_transactions,

sum(amount_near_unstaked) as total_near_unstaked,