Partition on contract group by reputation scores table & chart

[noobmdev]

• [ ] GRS histogram of Contract group
• [x] Reputation Score Distribution on 5 partitions of Contract group
• [x] Average Score: Reputation, Total Transfer, Total Receive, In Degree, Out Degree, Total Volume, Total Gas Spent (ETH) on 5 partitions of Contract group
• [x] Variance: Reputation, Total Transfer, Total Receive, In Degree, Out Degree, Total Volume, Total Gas Spent (ETH) on 5 partitions of Contract group

[noobmdev]

Reputation Score Distribution on 5 partitions of Contract group

WITH eth_reputation_score_tmp AS (
    SELECT *
    FROM eth_reputation_score
    WHERE snapshot_block_number IN (
        SELECT MAX(number)
        FROM eth_block 
        WHERE toDate(timestamp) >= '2022-11-18' AND toDate(timestamp) <= '2023-08-01'
    ) AND contract = TRUE
), distribution_on_5_partitions_of_contract_group AS (
  SELECT 
    CASE WHEN reputation_score_log_scale > 680 then 'Excellent'
    WHEN reputation_score_log_scale <= 680 AND reputation_score_log_scale > 300 then 'High'
    WHEN reputation_score_log_scale <= 300 AND reputation_score_log_scale > 100 then 'Medium'
    WHEN reputation_score_log_scale <= 100 AND reputation_score_log_scale > 0 then 'Low'
    ELSE 'Zero-GRS' END Partition,
    COUNT() "Number of contract",
    ROUND("Number of contract" * 100 / (SELECT COUNT() FROM eth_reputation_score_tmp), 4) "No. of contract (%)",
    ROUND(SUM(reputation_score), 2) "Reputation Value",
    ROUND(divideDecimal(SUM(reputation_score) * 100, (SELECT SUM(reputation_score) FROM eth_reputation_score_tmp), 10), 2) "Reputation Value (%)"
  FROM eth_reputation_score_tmp
  GROUP BY Partition
  ORDER BY Partition
)
SELECT * FROM distribution_on_5_partitions_of_contract_group

Average Score: Reputation, Total Transfer, Total Receive, In Degree, Out Degree, Total Volume, Total Gas Spent (ETH) on 5 partitions of Contract group

WITH eth_reputation_score_tmp AS (
    SELECT *
    FROM eth_reputation_score
    WHERE snapshot_block_number IN (
        SELECT MAX(number)
        FROM eth_block 
        WHERE toDate(timestamp) >= '2022-11-18' AND toDate(timestamp) <= '2023-08-01'
    ) AND contract = TRUE
), distribution_on_5_partitions_of_contract_group AS (
  SELECT 
    CASE WHEN reputation_score_log_scale > 680 then 'Excellent'
    WHEN reputation_score_log_scale <= 680 AND reputation_score_log_scale > 300 then 'High'
    WHEN reputation_score_log_scale <= 300 AND reputation_score_log_scale > 100 then 'Medium'
    WHEN reputation_score_log_scale <= 100 AND reputation_score_log_scale > 0 then 'Low'
    ELSE 'Zero-GRS' END Partition,
    ROUND(SUM(reputation_score) / COUNT(), 3) "Reputation",
    ROUND(SUM(total_transfer) / COUNT(), 3) "Total Transfer",
    ROUND(SUM(total_receive) / COUNT(), 3) "Total Receive",
    ROUND(SUM(in_degree) / COUNT(), 3) "In Degree",
    ROUND(SUM(out_degree) / COUNT(), 3) "Out Degree",
    ROUND(SUM(total_volume) / COUNT(), 3) "Total Volume",
    ROUND(SUM(total_gas_spent) / COUNT(), 3) "Total Gas Spent"
  FROM eth_reputation_score_tmp
  GROUP BY Partition
  ORDER BY Partition
)
SELECT * FROM distribution_on_5_partitions_of_contract_group

Variance: Reputation, Total Transfer, Total Receive, In Degree, Out Degree, Total Volume, Total Gas Spent (ETH) on 5 partitions of Contract group

WITH eth_reputation_score_tmp AS (
    SELECT *
    FROM eth_reputation_score
    WHERE snapshot_block_number IN (
        SELECT MAX(number)
        FROM eth_block 
        WHERE toDate(timestamp) >= '2022-11-18' AND toDate(timestamp) <= '2023-08-01'
    ) AND contract = TRUE
), distribution_on_5_partitions_of_contract_group AS (
  SELECT 
    CASE WHEN reputation_score_log_scale > 680 then 'Excellent'
    WHEN reputation_score_log_scale <= 680 AND reputation_score_log_scale > 300 then 'High'
    WHEN reputation_score_log_scale <= 300 AND reputation_score_log_scale > 100 then 'Medium'
    WHEN reputation_score_log_scale <= 100 AND reputation_score_log_scale > 0 then 'Low'
    ELSE 'Zero-GRS' END Partition,
    VAR_SAMP(reputation_score) "Reputation",
    VAR_SAMP(total_transfer) "Total Transfer",
    VAR_SAMP(total_receive) "Total Receive",
    VAR_SAMP(in_degree) "In Degree",
    VAR_SAMP(out_degree) "Out Degree",
    VAR_SAMP(total_volume) "Total Volume",
    VAR_SAMP(total_gas_spent) "Total Gas Spent"
  FROM eth_reputation_score_tmp
  GROUP BY Partition
  ORDER BY Partition
)
SELECT 
*
FROM distribution_on_5_partitions_of_contract_group