WITH OP_AIRDROP4_CLAIMS AS ( SELECT TX_HASH from optimism.core.fact_event_logs where block_timestamp >= '2024-02-20' AND TX_STATUS = 'SUCCESS' AND ORIGIN_FUNCTION_SIGNATURE = '0x2e7ba6ef' and contract_address = '0xfb4d5a94b516df77fbdbcf3cfeb262baaf7d4db7' ), OP_AIRDROP_TRANSFERS AS ( SELECT TRANSFERS.* FROM optimism.core.ez_token_transfers TRANSFERS INNER JOIN OP_AIRDROP4_CLAIMS USING(TX_HASH) WHERE block_timestamp >= '2024-02-20' ), categorized_airdrops as ( SELECT AMOUNT, CASE WHEN AMOUNT >= 0 AND AMOUNT < 33 THEN '[0 - 33)' WHEN AMOUNT >= 33 AND AMOUNT < 65 THEN '[33 - 65)' WHEN AMOUNT >= 65 AND AMOUNT < 125 THEN '[65 - 125)' WHEN AMOUNT >= 125 AND AMOUNT < 250 THEN '[125 - 250)' WHEN AMOUNT >= 250 AND AMOUNT < 500 THEN '[250 - 500)' WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN '[500 - 1000)' WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN '[1000 - 2000)' WHEN AMOUNT >= 2000 AND AMOUNT < 3000 THEN '[2000 - 3000)' WHEN AMOUNT >= 3000 AND AMOUNT < 4000 THEN '[3000 - 4000)' WHEN AMOUNT >= 4000 AND AMOUNT < 5000 THEN '[4000 - 5000)' WHEN AMOUNT >= 5000 AND AMOUNT < 6000 THEN '[5000 - 6000)' WHEN AMOUNT = 6000 THEN '[6000]' END AS CATEGORY, CASE WHEN AMOUNT >= 0 AND AMOUNT < 33 THEN 1 WHEN AMOUNT >= 33 AND AMOUNT < 65 THEN 2 WHEN AMOUNT >= 65 AND AMOUNT < 125 THEN 3 WHEN AMOUNT >= 125 AND AMOUNT < 250 THEN 4 WHEN AMOUNT >= 250 AND AMOUNT < 500 THEN 5 WHEN AMOUNT >= 500 AND AMOUNT < 1000 THEN 6 WHEN AMOUNT >= 1000 AND AMOUNT < 2000 THEN 7 WHEN AMOUNT >= 2000 AND AMOUNT < 3000 THEN 8 WHEN AMOUNT >= 3000 AND AMOUNT < 4000 THEN 9 WHEN AMOUNT >= 4000 AND AMOUNT < 5000 THEN 10 WHEN AMOUNT >= 5000 AND AMOUNT < 6000 THEN 11 WHEN AMOUNT = 6000 THEN 12 END AS POSITION FROM OP_AIRDROP_TRANSFERS ) SELECT COUNT(AMOUNT), (COUNT(AMOUNT) / 22000) * 100 AS PERCENT, CATEGORY, POSITION FROM categorized_airdrops GROUP BY CATEGORY, POSITION

Optimism Airdrop 4 Insights