SELECT

DISTINCT t.tx_hash

, t.block_timestamp as dtime

, method_name

, t.tx_status

, logs

, outcome

, logs[0] as line

, 0 as amt_un

, CASE WHEN method_name in ('stake', 'deposit_and_stake')

THEN (TO_NUMBER(coalesce(regexp_substr(line, 'staking\\s(\\d+)', 1, 1, 'e', 1), regexp_substr(line, 'deposited\\s(\\d+)', 1, 1, 'e', 1)))/1e24)

ELSE (TO_NUMBER(regexp_substr(line, 'staking\\s(\\d+)', 1, 1, 'e', 1))/1e24)*(-1) END as amt

, t.tx_signer

, r.receiver_id

, rank() OVER (PARTITION by t.tx_signer, r.receiver_id ORDER BY dtime DESC) as rank

FROM near.core.fact_actions_events_function_call a

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

JOIN near.core.fact_receipts r ON a.tx_hash = r.tx_hash

WHERE 1=1

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

AND coalesce(logs[2], logs[1]) LIKE ('Contract total staked%')

AND t.tx_status = 'Success'

--AND t.tx_signer = 'xixihaha.near'

,

stakers as (

SELECT

SUM(coalesce(amt,amt_un)) as cur_stake

, MIN(dtime) as min_dtime

, MAX(dtime) as max_dtime

, tx_signer as staker

from base