Reputation Value distribution in different group table & chart

[noobmdev]

• [x] Size of three main groups(contracts/EOAs/non-zero EOAs) base on %
• [x] Historical changes of Top entities varying different reputation ranking periods

[noobmdev]

Size of three main groups(contracts/EOAs/non-zero EOAs) base on %

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-05-23'
    )
)
SELECT 
  'Non-zero EOAs' Groups,
  COUNT() "No. of addresses",
  ROUND("No. of addresses" * 100 / (SELECT COUNT() FROM eth_reputation_score_tmp), 2) "No. of addresses (%)",
  SUM(reputation_score) "Total Reputation Score",
  ROUND(divideDecimal("Total Reputation Score" * 100, (SELECT SUM(reputation_score) FROM eth_reputation_score_tmp), 10), 2) "Total Reputation Score (%)"
FROM eth_reputation_score_tmp 
WHERE contract = FALSE AND reputation_score != 0
UNION ALL 
SELECT 
  'Zero EOAs' Groups,
  COUNT() "No. of addresses",
  ROUND("No. of addresses" * 100 / (SELECT COUNT() FROM eth_reputation_score_tmp), 2) "No. of addresses (%)",
  0 "Total Reputation Score",
  0 "Total Reputation Score (%)"
FROM eth_reputation_score_tmp 
WHERE contract = FALSE AND reputation_score = 0
UNION ALL 
SELECT 
  'Contracts' Groups,
  COUNT() "No. of addresses",
  ROUND("No. of addresses" * 100 / (SELECT COUNT() FROM eth_reputation_score_tmp), 2) "No. of addresses (%)",
  SUM(reputation_score) "Total Reputation Score",
  ROUND(divideDecimal("Total Reputation Score" * 100, (SELECT SUM(reputation_score) FROM eth_reputation_score_tmp), 10), 2) "Total Reputation Score (%)"
FROM eth_reputation_score_tmp 
WHERE contract = TRUE