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 >= '{{start_date}}' AND b.block_timestamp::date <= '{{end_date}}'

AND a.block_timestamp::date >= '{{start_date}}' AND b.block_timestamp::date <= '{{end_date}}'

GROUP BY date, governor, args

SELECT

*,

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

from unstaking_aggregates