--credit to https://flipsidecrypto.xyz/pinehearst/near-city-stakeholders-p22Vqu 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 when receiver_id = 'linear-protocol.near' and event = 'stake' then 'Liquid Stake NEAR (Linear)' else null end as event_action, 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]:staked_amount/pow(10,24) as linear_stake_amount, -- linear pool regexp_substr(status_value, 'Success') as reg_success --coalesce(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 AND receiver_id NOT IN ('nearton-staking.near', 'xtoken.ref-finance.near') ), STAKERS AS (SELECT case when block_timestamp:: date >= '2023-01-16' then 'after pheonix' else 'before pheonix' end as period, tx_signer, sum(linear_stake_amount) as "Linear" 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 block_timestamp:: date >= current_date - 150 AND tx_signer NOT IN ('operator.meta-pool.near', 'operator.linear-protocol.near', 'conveyor-w-meta-pool.near', 'stader-epoch-runner.near', 'stader-operator.near', 'stader-labs.near') -- redelegations GROUP BY 1 , 2 ) SELECT period, case when "Linear" < 0.01 then '1. < 0.01 NEAR Staked' when "Linear" >= 0.01 and "Linear" <0.1 then '2. < 0.01-0.1 NEAR Staked' when "Linear" >= 0.1 and "Linear" < 1 then '3. < 0.1-1 NEAR Staked' when "Linear" >= 1 and "Linear" < 10 then '4. < 1-10 NEAR Staked' when "Linear" >= 10 and "Linear" < 100 then '5. < 10-100 NEAR Staked' when "Linear" >= 100 and "Linear" < 1000 then '6. < 100-1k NEAR Staked' when "Linear" >= 1000 and "Linear" < 10000 then '7. < 1k-10k NEAR Staked' when "Linear" >= 10000 and "Linear" < 100000 then '8. < 10k-100k NEAR Staked' when "Linear" >= 100000 and "Linear" < 1000000 then '9a. < 100k-1m NEAR Staked' when "Linear" > 1000000 then '9b. > 1m NEAR Staked' end as "Stake Tier", count (tx_signer) as user FROM STAKERS GROUP BY 1 , 2

Phoenix on LiNear Growth