kasadeghUntitled Query
Updated 2022-11-08
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
›
⌄
with near_miner_info as
(
select BLOCK_AUTHOR as miner,
count(distinct BLOCK_HASH)/(select count(distinct BLOCK_HASH ) from near.core.fact_blocks where BLOCK_TIMESTAMP::date>=DATEADD(day,-365,CURRENT_DATE()) ) as fraction_of_total_blocks_mined
from near.core.fact_blocks
where BLOCK_TIMESTAMP::date>=DATEADD(day,-180,CURRENT_DATE())
group by miner
having fraction_of_total_blocks_mined>0
)
,
near_total_miner as
(
select count(distinct miner) as total_miner
from near_miner_info
where fraction_of_total_blocks_mined!=0
)
,
near_gini_coefficient AS
(
select (sum(abs(t1.fraction_of_total_blocks_mined-t2.fraction_of_total_blocks_mined))
/ ((select sum(fraction_of_total_blocks_mined) from near_miner_info) * 2 * (select * from near_total_miner)) ) as coefficient
from near_miner_info as t1, near_miner_info as t2
)
,
near_metrics as
(
select 'Near' as Blockchain,
POWER(sum(fraction_of_total_blocks_mined),2)/(sum(power(fraction_of_total_blocks_mined,2))*(select * from near_total_miner)) as fairness,
(FAIRNESS-(1/(select * from near_total_miner)))/(1-(1/(select * from near_total_miner))) as normalized_fairness,
sum(-1* fraction_of_total_blocks_mined * (log(2,fraction_of_total_blocks_mined))) / log(2,(select * from near_total_miner)) as normalized_entropy,
(select coefficient from near_gini_coefficient) as gini_coefficient,
SQRT( sum( power(fraction_of_total_blocks_mined-(1/(select * from near_total_miner)),2) ) ) as euclidean_distance,
sum( fraction_of_total_blocks_mined * (log(10,fraction_of_total_blocks_mined/(1/(select * from near_total_miner)))) ) as KL_divergence
from near_miner_info
where fraction_of_total_blocks_mined!=0
Run a query to Download Data