Flipside TeamSeat Price & Active Validators Over Time
    Updated 2024-09-26
    -- forked from 2023-07-30 01:32 PM @ https://flipsidecrypto.xyz/edit/queries/c7b77393-f275-4238-a302-ad93d455299a

    -- Forked & Credit: RMAS | https://flipsidecrypto.xyz/rmas/q/fYS9OfHnfp51/dash-daily-seat-price-since-2022

    WITH epochs AS (
    SELECT epoch_id,
    min(block_id) AS min_block_id,
    max(block_id) AS max_block_id,
    count(*) AS blocks,
    count(distinct block_author) AS block_producers,
    min(block_timestamp) AS start_time,
    max(block_timestamp) AS end_time,
    max(total_supply) / 1e24 AS total_near_supply,
    row_number() OVER (order by min_block_id asc) - 1 AS epoch_num
    FROM near.core.fact_blocks AS b
    WHERE epoch_id != (SELECT epoch_id FROM near.core.fact_blocks QUALIFY row_number() OVER (order by block_timestamp desc) = 1) -- exclude current epoch
    GROUP BY 1),

    staking_actions AS (
    SELECT r.tx_hash,
    r.block_timestamp,
    r.receiver_id AS validator_address,
    replace(split(l.value::string, ': Contract received total')[0], 'Epoch ', '')::integer AS epoch_num,
    split(split(l.value::string, 'New total staked balance is ')[1], '. Total number of shares')[0]::bigint / 1e24 AS staked_balance
    FROM near.core.fact_receipts AS r , lateral flatten( input => r.logs ) AS l
    WHERE ( right(receiver_id, 12) = '.poolv1.near' OR right(receiver_id, 10) = '.pool.near' )
    AND r.tx_hash IN (
    SELECT tx_hash
    FROM near.core.fact_actions_events_function_call
    WHERE method_name IN ('ping','stake','unstake','stake_all','unstake_all','deposit_and_stake') )
    AND left(l.value::string, 6) = 'Epoch '
    QUALIFY row_number() OVER (partition by epoch_num, validator_address order by block_timestamp desc) = 1),

    validator_proposals AS (
    WITH parsed_proposals AS (
    SELECT b.block_id,
    QueryRunArchived: QueryRun has been archived