SELECT

FROM_ADDRESS,

AMOUNT AS eth_transferred,

CASE

WHEN AMOUNT >= 0.05 AND AMOUNT <= 8 AND BLOCK_TIMESTAMP < '2024-04-10 20:01:00.000' THEN 'Eligible🟢'

ELSE 'Ineligible🔴'

END AS eligibility_status

FROM base.core.ez_native_transfers

WHERE TO_ADDRESS IN (

LOWER('0x3e672a9B15e364171F943dD94981178664ba284D')

-- LOWER('0x69aCa1c07486ae35DD0CD4F4d308948ea6bFefc9')

)

-- AND BLOCK_TIMESTAMP < '2024-04-10 20:01:00.000'

),

aggregated_stats AS (

SELECT

eligibility_status,

count (distinct from_address) AS users,

COUNT(*) AS total_transactions,

ROUND(SUM(eth_transferred),2) AS total_eth_transferred,

ROUND(AVG(eth_transferred),2) AS average_eth_transferred,

MIN(eth_transferred) AS min_eth_transferred,

MAX(eth_transferred) AS max_eth_transferred

FROM categorized_transfers

GROUP BY eligibility_status

SELECT * FROM aggregated_stats;

ORDER BY 1 ASC