WITH dex_swaps AS ( SELECT swapper AS address , SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN -swap_from_amount ELSE swap_to_amount END) AS net_swap_amout , SUM(CASE WHEN swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_from_amount ELSE 0 END) AS swap_from_amount , SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN swap_to_amount ELSE 0 END) AS swap_to_amount , SUM(CASE WHEN swap_to_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' THEN 1 ELSE 0 END) AS n_swaps_to FROM solana.defi.fact_swaps WHERE block_timestamp >= '2022-12-24' AND block_timestamp <= '2023-03-24' AND ( swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' OR swap_from_mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' ) GROUP BY 1 ), cex_transfers AS ( SELECT CASE WHEN lf.label_type = 'cex' THEN t.tx_to ELSE t.tx_from END AS address , SUM(CASE WHEN lf.label_type = 'cex' THEN amount ELSE -amount END) AS net_cex_transfer_amout FROM solana.core.fact_transfers t LEFT JOIN solana.core.dim_labels lf ON lf.address = t.tx_from LEFT JOIN solana.core.dim_labels lt ON lt.address = t.tx_to WHERE block_timestamp >= '2022-12-24' AND block_timestamp <= '2023-03-24' AND mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' AND ( COALESCE(lf.label_type, '') = 'cex' OR COALESCE(lt.label_type, '') = 'cex' ) AND NOT ( COALESCE(lf.label_type, '') = 'cex' AND COALESCE(lt.label_type, '') = 'cex' ) GROUP BY 1 ), airdrops AS ( SELECT tx_to AS address , SUM(amount) AS airdrop_amount FROM solana.core.fact_transfers WHERE block_timestamp >= '2022-12-24' AND block_timestamp <= '2023-04-01' AND mint = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' AND tx_from IN ( '9AhKqLR67hwapvG8SA2JFXaCshXc9nALJjpKaHZrsbkw' -- original airdrop address , '6JZoszTBzkGsskbheswiS6z2LRGckyFY4SpEGiLZqA9p' -- new airdrop address ) AND amount < 40000000000 -- 40B GROUP BY 1 ), direct_transfers0 AS ( SELECT DISTINCT t.tx_id FROM airdrops a JOIN solana.core.fact_transactions t ON a.address = t.signers[0]::string WHERE t.block_timestamp >= '2022-12-24' AND t.block_timestamp <= '2023-03-24' AND succeeded AND ARRAY_SIZE(inner_instructions) = 0 AND ARRAY_SIZE(instructions) = 1 AND instructions[0]:parsed:info:source IS NOT NULL AND instructions[0]:parsed:info:destination IS NOT NULL AND LEFT(instructions[0]:parsed:info:source, 5) <> '11111' AND LEFT(instructions[0]:parsed:info:destination, 5) <> '11111' AND post_token_balances[0]:mint::string = 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' ), direct_transfers AS ( SELECT tx_from AS source , tx_to AS destination , SUM(amount) AS amount FROM direct_transfers0 dt JOIN solana.core.fact_transfers t ON dt.tx_id = t.tx_id WHERE t.block_timestamp >= '2022-12-24' AND t.block_timestamp <= '2023-03-24' GROUP BY 1, 2 ), n0 AS ( SELECT destination , COUNT(1) AS n_sources FROM direct_transfers GROUP BY 1 ), id_map0 AS ( SELECT a.*, d.destination, d.amount AS t_amt , ROW_NUMBER() OVER (PARTITION BY d.destination ORDER BY amount DESC) AS rn FROM direct_transfers d JOIN airdrops a ON a.address = d.source LEFT JOIN airdrops ad ON ad.address = d.destination JOIN n0 ON n0.destination = d.destination WHERE n_sources <= 5 AND ad.address IS NULL ), id_map AS ( SELECT d.address, d.destination, t_amt FROM id_map0 d WHERE rn = 1 ), sales AS ( SELECT COALESCE(d.address, c.address) AS address , SUM( COALESCE(d.net_swap_amout, 0) + COALESCE(c.net_cex_transfer_amout, 0) ) AS net_swap_amout FROM dex_swaps d FULL OUTER JOIN cex_transfers c ON c.address = d.address GROUP BY 1 ), t0 AS ( SELECT a.address , a.airdrop_amount , SUM( CASE WHEN net_swap_amout IS NULL THEN 0 WHEN net_swap_amout < 0 AND net_swap_amout < -t_amt THEN -t_amt ELSE net_swap_amout END ) AS net_swap_amout FROM airdrops a LEFT JOIN id_map i ON i.address = a.address LEFT JOIN sales s ON s.address = a.address OR s.address = i.destination GROUP BY 1, 2 ) SELECT ROUND(100 * AVG(CASE WHEN airdrop_amount + net_swap_amout >= 0.95 * airdrop_amount THEN 1 ELSE 0 END), 2) AS pct_held_95_pct , ROUND(SUM(GREATEST(0, airdrop_amount + LEAST(0, net_swap_amout)))) AS held_amt , ROUND(SUM(airdrop_amount)) AS tot_airdrop_amount , ROUND(100 * held_amt / tot_airdrop_amount, 2) AS pct_held_agg FROM t0

BONK!