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