fanta4The Decentralization of NEAR
    Updated 2023-05-07
    -- forked from 1The Decentralization of NEAR @ https://flipsidecrypto.xyz/edit/queries/f77fef85-487d-47b5-8203-a6595d52e0de

    with staking as (
    select ADDRESS , DATE , avg(BALANCE) as avg_balance
    from near.core.fact_staking_pool_daily_balances
    group by ADDRESS , DATE
    )
    , ratio as (
    SELECT
    ADDRESS , DATE , avg_balance ,
    (sum(avg_balance) OVER (PARTITION BY ADDRESS ORDER BY DATE))
    / (sum(avg_balance) OVER (ORDER BY DATE)) as ratio
    FROM staking
    ORDER BY ADDRESS , ratio DESC
    )
    , Nakamotot1 as (
    SELECT
    ADDRESS , DATE , avg_balance ,
    sum(ratio) OVER (PARTITION BY DATE order BY ratio desc) as t1,
    COUNT(*) OVER (PARTITION BY DATE order BY ratio desc) as t2
    FROM ratio
    )
    , Nakamotot2 as (
    SELECT DATE , min(t1) as min_t1 from Nakamotot1
    WHERE t1 > 0.33
    GROUP by DATE )
    , Nakamoto_coefficient as (
    SELECT t1 , t2 , Nakamotot1.DATE
    FROM Nakamotot1
    LEFT outer JOIN Nakamotot2
    ON Nakamotot1.DATE = Nakamotot2.DATE
    AND min_t1 = t1
    WHERE not Nakamotot2.DATE is NULL
    )

    , top_validator_NOW as (select top 10 ADDRESS , avg_balance from staking
    Run a query to Download Data