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 AND block_timestamp < '2022-08-01'

),

near_validator AS (

SELECT

date_trunc('month', 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

date,

tx_receiver,

count(distinct tx_hash) as tx_count, -- to check

median(near) as "NEAR Staked",

row_number() over (partition by date order by "NEAR Staked" DESC) as rank

FROM near_validator

WHERE tx_receiver like '%pool%'

GROUP BY 1,2