fanta4The Decentralization of NEAR
Updated 2023-05-07
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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