with

t1 as (

SELECT

x.block_timestamp as week,

method_name,

tx_signer,

tx_receiver

FROM near.core.fact_actions_events_function_call x

JOIN near.core.fact_transactions y ON x.tx_hash = y.tx_hash

WHERE method_name IN ('deposit_and_stake','unstake_all')

AND coalesce(tx:receipt[0]:outcome:logs[2], tx:receipt[0]:outcome:logs[1]) LIKE ('Contract total staked%')

),

t2 as (

SELECT

trunc(week,'week') as date,

tx_receiver as validator,

count(distinct tx_signer) as stakers

from t1 where method_name='deposit_and_stake'

group by 1,2

),

t3 as (

SELECT

trunc(week,'week') as date,

tx_receiver as validator,

count(distinct tx_signer) as unstakers

from t1 where method_name='unstake_all'

group by 1,2

)

ifnull(t2.date,t3.date) as date,

ifnull(t2.validator,t3.validator) as validator,

ifnull(stakers,0) as stakerss, ifnull(unstakers*(-1),0) as unstakerss,stakerss+unstakerss as net_stakers

from t2

join t3 on t2.date=t3.date and t2.validator=t3.validator