SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

tx:actions[0]:FunctionCall:deposit/pow(10,24) near_staked,

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

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

regexp_substr(logs, 'Contract total staked balance is\\W+\\w+') as result,

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

try_to_numeric(validator_stake_pre) as amount

FROM near.core.fact_transactions

WHERE tx_hash IN ( SELECT tx_hash

FROM near.core.fact_actions_events_function_call

WHERE method_name IN ('deposit_and_stake')) AND validator_stake_pre IS NOT NULL

),

near_validator AS (

SELECT

block_timestamp as date,

tx_hash,

tx_receiver,

amount,

amount/pow(10,24) as near

FROM staking_tx

WHERE amount is not null

),

final AS (

SELECT

tx_receiver,

date,

near,

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

FROM near_validator

WHERE tx_receiver like '%pool%'

ORDER BY 1, rank ASC