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

),

near_validator AS (

SELECT

date_trunc('day', block_timestamp) as date,

tx_hash,

tx_receiver,

amount,

amount/pow(10,24) as near

FROM staking_tx

WHERE amount is not null

),

VALIDATORS_RANKED AS (

SELECT

date,

tx_receiver,

count(distinct tx_hash) as tx_count, -- no of tx per validator

median(near) as "NEAR Staked", -- median amount staked monthly

sum("NEAR Staked") over (partition by date order by "NEAR Staked" DESC) as cumulative_near_month, -- count cumulative NEAR ordered by top validators

row_number() over (partition by date order by "NEAR Staked" DESC) as rank -- rank for GINI calculation

FROM near_validator