SELECT tx_hash

FROM near.core.fact_actions_events_function_call

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

)

, prs_log as (

SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

tx:receipt[0]:outcome:logs as logs,

SUBSTRING(logs, CHARINDEX('Total number of shares ', logs), LEN(logs)) as Validator_shares,

TRIM(REGEXP_REPLACE(Validator_shares, '[a-z/-/A-z/./#/*"]', '')) as raw_staked,

try_to_numeric(raw_staked) as amount

FROM near.core.fact_transactions

WHERE tx_hash IN (SELECT tx_hash from stake_tx)

AND block_timestamp::date < CURRENT_DATE - 1

, validator AS (

SELECT

block_timestamp as date,

tx_hash,

tx_receiver,

amount,

amount/1e24 as staked_amt

FROM prs_log

WHERE amount is not null

, lst_info AS ( SELECT

date,

tx_receiver as validator,

staked_amt,

row_number() over (partition by validator order by date DESC) as rank

FROM validator

WHERE tx_receiver like '%pool%'