with airdrop_claims as ( SELECT TX_ID from solana.core.fact_events where succeeded and block_timestamp >= '2024-01-31' and program_id = 'meRjbQXFNf5En86FXT2YPz1dQzLj4Yb3xK8u1MVgqpb' ), claims_as_transfers as ( SELECT AMOUNT, TX_TO AS AIRDROP_WALLET_RECIPIENT, BLOCK_ID, BLOCK_TIMESTAMP, TX_ID from solana.core.fact_transfers where block_timestamp >= '2024-01-31' and mint = 'JUPyiwrYJFskUPiHa7hkeR8VUtAeFoSYbKedZNsDvCN' and TX_ID IN ( SELECT TX_ID FROM airdrop_claims ) ), claims_grouped as ( SELECT AIRDROP_WALLET_RECIPIENT, SUM(AMOUNT) AS AMOUNT FROM claims_as_transfers GROUP BY AIRDROP_WALLET_RECIPIENT ), categorized_airdrops as ( SELECT AMOUNT, AIRDROP_WALLET_RECIPIENT, CASE WHEN AMOUNT >= 0 AND AMOUNT <= 200 THEN '[0-200]' WHEN AMOUNT > 200 AND AMOUNT <= 500 THEN '(200-500]' WHEN AMOUNT > 500 AND AMOUNT <= 1000 THEN '(500-1000]' WHEN AMOUNT > 1000 AND AMOUNT <= 1400 THEN '(1000 - 1400]' WHEN AMOUNT > 1400 AND AMOUNT <= 2000 THEN '(1400 - 2000]' WHEN AMOUNT > 2000 AND AMOUNT <= 4000 THEN '(2000 - 4000]' WHEN AMOUNT > 4000 AND AMOUNT <= 20000 THEN '(6000 - 20000]' WHEN AMOUNT > 20000 AND AMOUNT <= 30000 THEN '(20000 - 30000]' WHEN AMOUNT > 30000 AND AMOUNT <= 100000 THEN '(30000 - 100000]' ELSE '_[100,000 - Unknown)' END AS CATEGORY, CASE WHEN AMOUNT >= 0 AND AMOUNT <= 200 THEN 0 WHEN AMOUNT > 200 AND AMOUNT <= 500 THEN 1 WHEN AMOUNT > 500 AND AMOUNT <= 1000 THEN 2 WHEN AMOUNT > 1000 AND AMOUNT <= 1400 THEN 3 WHEN AMOUNT > 1400 AND AMOUNT <= 2000 THEN 4 WHEN AMOUNT > 2000 AND AMOUNT <= 4000 THEN 5 WHEN AMOUNT > 4000 AND AMOUNT <= 20000 THEN 6 WHEN AMOUNT > 20000 AND AMOUNT <= 30000 THEN 7 WHEN AMOUNT > 30000 AND AMOUNT <= 100000 THEN 8 ELSE 9 END AS POSITION FROM claims_grouped ) SELECT COUNT(AMOUNT), (COUNT(AMOUNT) / (SELECT COUNT(DISTINCT AIRDROP_WALLET_RECIPIENT) FROM claims_as_transfers)) * 100 AS PERCENT, POSITION, CATEGORY FROM categorized_airdrops GROUP BY CATEGORY, POSITION

JUP Airdrop Dissected