kasadeghUntitled Query
    Updated 2022-11-09
    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