WITH raw AS (

SELECT

DATE_TRUNC('day', block_timestamp) AS day,

tx_hash,

event_name,

decoded_log:account::STRING AS claimer,

decoded_log:amount::INT / 1e18 AS AMOUNT

FROM

ethereum.core.fact_decoded_event_logs

WHERE

LOWER(contract_address) IN (

LOWER('0x035bdAeaB85E47710C27EdA7FD754bA80aD4ad02'),

LOWER('0xF532a5A35007804A9cA79E7Fa15D8f648F6D7F28'),

LOWER('0xa105C3AbeDBAf4295AC6149BF24D5311F629934c')

)

AND event_name = 'Claimed'

AND block_timestamp >= '2024-05-01'

),

summary_table AS (

SELECT

CASE

WHEN ROUND(AMOUNT, 2) >= 1000000 THEN '1M+'

WHEN ROUND(AMOUNT, 2) BETWEEN 100000 AND 999999 THEN '100k-1M'

WHEN ROUND(AMOUNT, 2) BETWEEN 10000 AND 99999.9 THEN '10k-100k'

WHEN ROUND(AMOUNT, 2) BETWEEN 1000 AND 9999.9 THEN '1k-10k'

WHEN ROUND(AMOUNT, 2) BETWEEN 100 AND 999.9 THEN '100-1k'

WHEN ROUND(AMOUNT, 2) < 100 THEN 'Less than 100'

END AS category_claims,

ROUND(SUM(AMOUNT), 2) AS total_token_claimed,

COUNT(*) AS wallet_count,

ROUND(SUM(AMOUNT) / COUNT(*), 2) AS avg_token_per_cat

FROM raw

GROUP BY 1

)