Global Reputation Score Analysis (Contract Group) table & chart

[noobmdev]

• [x] Reputation Score Distribution on categories of Contract groups
• [x] Top 10 Contracts by Reputation Score

[noobmdev]

Reputation Score Distribution on categories of Contract groups

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
    ORDER BY rank
    LIMIT 2000
) 
SELECT 
  category "Contract categories",
  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 Percentage"
FROM label_eth_contract le
JOIN eth_reputation_score_tmp er ON LOWER(le.address) = (er.address)
GROUP BY category
ORDER BY category