SELECT

a.block_timestamp as dtime,

tx_signer as staker,

tx_receiver as gov,

a.tx_hash,

method_name as act

FROM near.core.fact_actions_events_function_call a

JOIN near.core.fact_transactions t ON a.tx_hash = t.tx_hash

WHERE 1=1

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

ORDER BY staker, dtime

),

refine as (

SELECT

*,

lag(gov) OVER (PARTITION BY staker ORDER BY dtime) as pgov,

lag(act) OVER (PARTITION BY staker ORDER BY dtime) as pact,

lag(tx_hash) OVER (PARTITION BY staker ORDER BY dtime) as ptx,

lag(dtime) OVER (PARTITION BY staker ORDER BY dtime) as ptime,

TIMEDIFF(seconds, ptime, dtime) as time_delta

from actions

),

restakes as (

SELECT * from refine

WHERE 1=1

AND (pact = 'unstake_all' AND act ='deposit_and_stake')

AND (gov!=pgov)

pgov as ex_gov,

COUNT(DISTINCT staker) as totals

from restakes