Key metrics table

[noobmdev]

• [x] Total blocks
• [x] Total transactions
• [x] Total gas spent (ETH)
• [x] Total address
• [x] Total contract address
• [x] Total contract with zero-GRS
• [x] Total EOA
• [x] Total EOA with zero-GRS
• [x] Average GRS of contract group
• [x] Average GRS of EOA group
• [x] Average GRS of non-zero EOA group
• [x] GRS variance of contract group
• [x] GRS variance of EOA group

[longthanhta]

Total gas spent = Total gas of all txn Token Transaction = total row in eth_token_transfer and total row in eth_transaction

[longthanhta]

Ignore all transactions involving burn addresses: 0x000000000000000000000000000000000000dEaD 0x0000000000000000000000000000000000000000

[noobmdev]

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 
  'Total blocks' categories,
  toString(MAX(number) - MIN(number) + 1) value 
FROM eth_block 
WHERE toDate(timestamp) >= '2022-11-18' AND toDate(timestamp) <= '2023-05-23'
UNION ALL
SELECT 
  'Total transactions' categories,
  toString(SUM(total_transaction)) value 
FROM (
    SELECT count() total_transaction
    FROM eth_transaction -- eth_transaction | bsc_transaction
    WHERE toDate(block_timestamp) >= '2022-11-18' AND toDate(block_timestamp) <= '2023-05-23'
    UNION ALL 
    SELECT count() total_transaction
    FROM eth_log --  eth_log | bsc_log 
    WHERE LENGTH(topics) = 3 AND arrayElement(topics, 1) = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
        AND toDate(block_timestamp) >= '2022-11-18' AND toDate(block_timestamp) <= '2023-05-23'
)   
UNION ALL
SELECT 
  'Total gas spent (ETH)' categories,
  toString(SUM((receipt_gas_used * gas_price) / POWER(10, 18))) AS total_gas_spent
FROM bsc_transaction FINAL -- bsc_transaction | eth_transaction
WHERE toDate(block_timestamp) >= '2022-11-18' AND toDate(block_timestamp) <= '2023-05-23'
UNION ALL
SELECT 
  'Total address' categories,
  toString(COUNT())  value 
FROM eth_reputation_score_tmp 
UNION ALL
SELECT 
  'Total contracts' categories,
  toString(COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = TRUE
UNION ALL 
SELECT 
  'Total contracts with zero-GRS' categories,
  toString(COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = TRUE AND reputation_score = 0
UNION ALL 
SELECT 
  'Total EOAs' categories,
  toString(COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = FALSE
UNION ALL 
SELECT 
  'Total EOAs with zero-GRS' categories,
  toString(COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = FALSE AND reputation_score = 0
UNION ALL 
SELECT 
  'Average GRS (Contract Group)' categories,
  toString(SUM(reputation_score) / COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = TRUE
UNION ALL 
SELECT 
  'Average GRS (EOA Group)' categories,
  toString(SUM(reputation_score) / COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = FALSE
UNION ALL 
SELECT 
  'Average GRS (Non-zero EOA Group)' categories,
  toString(SUM(reputation_score) / COUNT())  value 
FROM eth_reputation_score_tmp 
WHERE contract = FALSE AND reputation_score != 0
UNION ALL
SELECT 
  'Variance (Contract Group)' categories,
  toString(VAR_SAMP(reputation_score))  value 
FROM eth_reputation_score_tmp 
WHERE contract = TRUE
UNION ALL
SELECT 
  'Variance (EOA Group)' categories,
  toString(VAR_SAMP(reputation_score))  value 
FROM eth_reputation_score_tmp 
WHERE contract = FALSE