with near_event AS ( SELECT block_timestamp, tx_hash, receiver_id, replace(value, 'EVENT_JSON:') as logs_cleaned, split(logs_cleaned, ' ') as split_logs, try_parse_json(logs_cleaned):data as data_log, nvl(try_parse_json(logs_cleaned):event, try_parse_json(logs_cleaned):type) as event, case -- Staking NEAR when receiver_id IN ('aurora.pool.near', 'aurora.poolv1.near') and split_logs[1] = 'staking' then 'Stake NEAR (Aurora Pool)' when receiver_id NOT IN ('aurora.pool.near', 'aurora.poolv1.near') and receiver_id like '%pool%' and split_logs[1] = 'staking' then 'Stake NEAR (Other Pools)' when receiver_id = 'meta-pool.near' and event = 'STAKE' then 'Liquid Stake NEAR (Meta)' when receiver_id = 'linear-protocol.near' and event = 'stake' then 'Liquid Stake NEAR (Linear)' when receiver_id = 'nearx.stader-labs.near' and event = 'deposit_and_stake' then 'Liquid Stake NEAR (Stader)' else null end as event_action, case when event_action = 'Stake NEAR (Aurora Pool)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_aurora_stake, case when event_action = 'Stake NEAR (Other Pools)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_non_aurora_stake, try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) as native_stake, -- native staking pool relies on log try_parse_json(logs_cleaned):amount/pow(10,24) as meta_stake_amount, -- meta pool / relies on json data_log[0]:amount/pow(10,24) as stader_stake_amount, -- stader pool data_log[0]:staked_amount/pow(10,24) as linear_stake_amount, -- linear pool regexp_substr(status_value, 'Success') as reg_success, coalesce(meta_stake_amount, stader_stake_amount, linear_stake_amount) as liquid_near_staked, coalesce(native_stake, meta_stake_amount, stader_stake_amount, linear_stake_amount) as near_staked FROM near.core.fact_receipts, table(flatten(input =>logs)) WHERE reg_success IS NOT NULL -- filter out successful transactions ), STAKERS AS ( SELECT tx_signer, count(distinct receiver_id) as "Pools Delegated", count(distinct tx_hash) as "Tx Count", -- native sum(native_aurora_stake) as "Native Stake (Aurora Pool)", sum(native_non_aurora_stake) as "Native Stake (Non Aurora)", sum(native_stake) as "Native Stake (NEAR)", -- liquid sum(liquid_near_staked) as "Liquid NEAR staked", sum(meta_stake_amount) as "Meta", sum(linear_stake_amount) as "Linear", sum(stader_stake_amount) as "Stader", -- regardless sum(near_staked) as "Total NEAR staked" FROM near_event LEFT JOIN (SELECT tx_hash as tx_hash1, tx_signer, tx_receiver FROM near.core.fact_transactions) ON tx_hash = tx_hash1 WHERE event_action IS NOT NULL AND near_staked IS NOT NULL GROUP BY 1 ORDER BY "Total NEAR staked" DESC ) SELECT case when "Total NEAR staked" < 0.01 then '1. < 0.01 NEAR Staked' when "Total NEAR staked" >= 0.01 and "Total NEAR staked" <0.1 then '2. < 0.01-0.1 NEAR Staked' when "Total NEAR staked" >= 0.1 and "Total NEAR staked" < 1 then '3. < 0.1-1 NEAR Staked' when "Total NEAR staked" >= 1 and "Total NEAR staked" < 10 then '4. < 1-10 NEAR Staked' when "Total NEAR staked" >= 10 and "Total NEAR staked" < 100 then '5. < 10-100 NEAR Staked' when "Total NEAR staked" >= 100 and "Total NEAR staked" < 1000 then '6. < 100-1k NEAR Staked' when "Total NEAR staked" >= 1000 and "Total NEAR staked" < 10000 then '7. < 1k-10k NEAR Staked' when "Total NEAR staked" >= 10000 and "Total NEAR staked" < 100000 then '8. < 10k-100k NEAR Staked' when "Total NEAR staked" >= 100000 and "Total NEAR staked" < 1000000 then '9a. < 100k-1m NEAR Staked' when "Total NEAR staked" > 1000000 then '9b. > 1m NEAR Staked' end as "Stake Tier", sum("Liquid NEAR staked") as Liquid, -- total via liquid sum("Native Stake (NEAR)") as Native, -- total via native Liquid/Native*100 as "% Stake from Liquid", count(distinct tx_signer) as Stakers FROM STAKERS -- WHERE tx_signer IS NOT (relayers from liquid staking ) GROUP BY 1 ORDER BY 1 ASC

NEAR CITY STAKEHOLDERS