kasadeghUntitled Query
Updated 2022-11-09
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
›
⌄
with near_miner_info as
(
select BLOCK_AUTHOR as miner,
count(distinct BLOCK_HASH)/(select count(distinct BLOCK_HASH ) from near.core.fact_blocks) as fraction_of_total_blocks_mined
from near.core.fact_blocks
group by miner
)
,
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 count(distinct miner) from near_miner_info )) ) 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 count(distinct miner) from near_miner_info )) as fairness,
(FAIRNESS-(1/(select count(distinct miner) from near_miner_info where fraction_of_total_blocks_mined!=0)))/(1-(1/(select count(distinct miner) from near_miner_info where fraction_of_total_blocks_mined!=0))) as normalized_fairness,
sum(-1* fraction_of_total_blocks_mined * (log(2,fraction_of_total_blocks_mined))) / log(2,(select count(distinct miner) from near_miner_info where fraction_of_total_blocks_mined!=0)) as normalized_entropy,
(select coefficient from near_gini_coefficient) as gini_coefficient,
SQRT( sum( power(fraction_of_total_blocks_mined-(1/(select count(distinct miner) from near_miner_info where fraction_of_total_blocks_mined!=0)),2) ) ) as euclidean_distance,
sum( fraction_of_total_blocks_mined * (log(10,fraction_of_total_blocks_mined/(1/(select count(distinct miner) from near_miner_info where fraction_of_total_blocks_mined!=0)))) ) as KL_divergence
from near_miner_info
where fraction_of_total_blocks_mined!=0
)
select * from near_metrics
Run a query to Download Data