WITH constants AS ( SELECT '0x111111111117dc0aa78b770fa6a738034120c302' AS c_contract_address , 'claim' AS c_origin_function_name ), airdrop_recipients AS ( SELECT to_address AS address , MIN(block_timestamp) AS rec_time , SUM(amount) AS airdrop_amt FROM ethereum.udm_events u JOIN constants c ON c.c_contract_address = u.contract_address AND c.c_origin_function_name = u.origin_function_name WHERE from_address IN ( '0xe295ad71242373c37c5fda7b57f26f9ea1088afe', '0x4ee7c0f5480eb1edd8902a5e8b991ed52992d5f5' ) AND block_timestamp < CURRENT_DATE - 90 GROUP BY 1 ), transfers_to_0 AS ( SELECT to_address AS address , SUM(amount) AS transfer_to_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS transfer_to_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS transfer_to_amt_7d FROM ethereum.udm_events u JOIN airdrop_recipients r ON r.address = u.to_address AND u.block_timestamp > r.rec_time JOIN constants c ON c.c_contract_address = u.contract_address WHERE from_label IS NULL GROUP BY 1 ), transfers_from_0 AS ( SELECT from_address AS address , SUM(amount) AS transfer_from_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS transfer_from_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS transfer_from_amt_7d FROM ethereum.udm_events u JOIN constants c ON c.c_contract_address = u.contract_address JOIN airdrop_recipients r ON r.address = u.from_address AND u.block_timestamp > r.rec_time WHERE to_label IS NULL GROUP BY 1 ), cex_transfers_to_0 AS ( SELECT to_address AS address , SUM(amount) AS cex_transfer_to_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS cex_transfer_to_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS cex_transfer_to_amt_7d FROM ethereum.udm_events u JOIN constants c ON c.c_contract_address = u.contract_address JOIN airdrop_recipients r ON r.address = u.to_address AND u.block_timestamp > r.rec_time AND from_label_type = 'cex' GROUP BY 1 ), cex_transfers_from_0 AS ( SELECT from_address AS address , SUM(amount) AS cex_transfer_from_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS cex_transfer_from_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS cex_transfer_from_amt_7d FROM ethereum.udm_events u JOIN constants c ON c.c_contract_address = u.contract_address JOIN airdrop_recipients r ON r.address = u.from_address AND u.block_timestamp > r.rec_time AND to_label_type = 'cex' GROUP BY 1 ), swaps_tx AS ( SELECT DISTINCT tx_id FROM ethereum.events_emitted e WHERE (event_name ilike '%swap%' AND tx_to_label_type = 'dex') ), swaps_to_0 AS ( SELECT u.to_address AS address , SUM(amount) AS swap_to_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS swap_to_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS swap_to_amt_7d FROM ethereum.udm_events u JOIN airdrop_recipients r ON r.address = u.to_address JOIN swaps_tx s ON s.tx_id = u.tx_id JOIN constants c ON c.c_contract_address = u.contract_address WHERE u.block_timestamp > r.rec_time AND u.from_label_type = 'dex' AND u.amount > 0 GROUP BY 1 ), swaps_from_0 AS ( SELECT u.from_address AS address , SUM(amount) AS swap_from_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS swap_from_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS swap_from_amt_7d FROM ethereum.udm_events u JOIN airdrop_recipients r ON r.address = u.from_address JOIN swaps_tx s ON s.tx_id = u.tx_id JOIN constants c ON c.c_contract_address = u.contract_address WHERE u.block_timestamp > r.rec_time AND u.to_label_type = 'dex' AND u.amount > 0 GROUP BY 1 ), swaps_0 AS ( SELECT COALESCE(t.address, f.address) AS address , COALESCE(swap_to_amt, 0) AS swap_to_amt , COALESCE(swap_from_amt, 0) AS swap_from_amt , COALESCE(swap_to_amt, 0) - COALESCE(swap_from_amt, 0) AS net_swap_amt , COALESCE(swap_to_amt_24h, 0) - COALESCE(swap_from_amt_24h, 0) AS net_swap_amt_24h , COALESCE(swap_to_amt_7d, 0) - COALESCE(swap_from_amt_7d, 0) AS net_swap_amt_7d FROM swaps_to_0 t FULL OUTER JOIN swaps_from_0 f ON f.address = t.address ), transfers_0 AS ( SELECT COALESCE(t.address, f.address) AS address , COALESCE(transfer_to_amt, 0) AS transfer_to_amt , COALESCE(transfer_from_amt, 0) AS transfer_from_amt , COALESCE(transfer_to_amt, 0) - COALESCE(transfer_from_amt, 0) AS net_transfer_amt , COALESCE(transfer_to_amt_24h, 0) - COALESCE(transfer_from_amt_24h, 0) AS net_transfer_amt_24h , COALESCE(transfer_to_amt_7d, 0) - COALESCE(transfer_from_amt_7d, 0) AS net_transfer_amt_7d FROM transfers_to_0 t FULL OUTER JOIN transfers_from_0 f ON f.address = t.address ), cex_transfers_0 AS ( SELECT COALESCE(t.address, f.address) AS address , COALESCE(cex_transfer_to_amt, 0) AS cex_transfer_to_amt , COALESCE(cex_transfer_from_amt, 0) AS cex_transfer_from_amt , COALESCE(cex_transfer_to_amt, 0) - COALESCE(cex_transfer_from_amt, 0) AS net_cex_transfer_amt , COALESCE(cex_transfer_to_amt_24h, 0) - COALESCE(cex_transfer_from_amt_24h, 0) AS net_cex_transfer_amt_24h , COALESCE(cex_transfer_to_amt_7d, 0) - COALESCE(cex_transfer_from_amt_7d, 0) AS net_cex_transfer_amt_7d FROM cex_transfers_to_0 t FULL OUTER JOIN cex_transfers_from_0 f ON f.address = t.address ), base_0 AS ( SELECT r.address , r.rec_time , r.airdrop_amt , COALESCE(swap_to_amt, 0) AS swap_to_amt , COALESCE(swap_from_amt, 0) AS swap_from_amt , COALESCE(net_swap_amt, 0) AS net_swap_amt , COALESCE(net_swap_amt_24h, 0) AS net_swap_amt_24h , COALESCE(net_swap_amt_7d, 0) AS net_swap_amt_7d , COALESCE(transfer_to_amt, 0) AS transfer_to_amt , COALESCE(transfer_from_amt, 0) AS transfer_from_amt , COALESCE(net_transfer_amt, 0) AS net_transfer_amt , COALESCE(net_transfer_amt_24h, 0) AS net_transfer_amt_24h , COALESCE(net_transfer_amt_7d, 0) AS net_transfer_amt_7d , COALESCE(cex_transfer_to_amt, 0) AS cex_transfer_to_amt , COALESCE(cex_transfer_from_amt, 0) AS cex_transfer_from_amt , COALESCE(net_cex_transfer_amt, 0) AS net_cex_transfer_amt , COALESCE(net_cex_transfer_amt_24h, 0) AS net_cex_transfer_amt_24h , COALESCE(net_cex_transfer_amt_7d, 0) AS net_cex_transfer_amt_7d FROM airdrop_recipients r LEFT JOIN swaps_0 s ON s.address = r.address LEFT JOIN cex_transfers_0 c ON c.address = r.address LEFT JOIN transfers_0 t ON t.address = r.address ), addys AS ( SELECT * , CASE WHEN net_swap_amt > 0 THEN 0 WHEN net_swap_amt <= -airdrop_amt THEN -airdrop_amt ELSE net_swap_amt END AS calc_swap_amt , CASE WHEN net_cex_transfer_amt > 0 THEN 0 WHEN net_cex_transfer_amt <= -airdrop_amt - calc_swap_amt THEN -airdrop_amt - calc_swap_amt ELSE net_cex_transfer_amt END AS calc_cex_transfer_amt , CASE WHEN net_transfer_amt > 0 THEN 0 WHEN net_transfer_amt <= -airdrop_amt - calc_swap_amt - calc_cex_transfer_amt THEN -airdrop_amt - calc_swap_amt - calc_cex_transfer_amt ELSE net_transfer_amt END AS calc_transfer_amt , CASE WHEN net_swap_amt_24h > 0 THEN 0 WHEN net_swap_amt_24h <= -airdrop_amt THEN -airdrop_amt ELSE net_swap_amt_24h END AS calc_swap_amt_24h , CASE WHEN net_cex_transfer_amt_24h > 0 THEN 0 WHEN net_cex_transfer_amt_24h <= -airdrop_amt - calc_swap_amt_24h THEN -airdrop_amt - calc_swap_amt_24h ELSE net_cex_transfer_amt_24h END AS calc_cex_transfer_amt_24h , CASE WHEN net_transfer_amt_24h > 0 THEN 0 WHEN net_transfer_amt_24h <= -airdrop_amt - calc_swap_amt_24h - calc_cex_transfer_amt_24h THEN -airdrop_amt - calc_swap_amt_24h - calc_cex_transfer_amt_24h ELSE net_transfer_amt_24h END AS calc_transfer_amt_24h , CASE WHEN net_swap_amt_7d > 0 THEN 0 WHEN net_swap_amt_7d <= -airdrop_amt THEN -airdrop_amt ELSE net_swap_amt_7d END AS calc_swap_amt_7d , CASE WHEN net_cex_transfer_amt_7d > 0 THEN 0 WHEN net_cex_transfer_amt_7d <= -airdrop_amt - calc_swap_amt_7d THEN -airdrop_amt - calc_swap_amt_7d ELSE net_cex_transfer_amt_7d END AS calc_cex_transfer_amt_7d , CASE WHEN net_transfer_amt_7d > 0 THEN 0 WHEN net_transfer_amt_7d <= -airdrop_amt - calc_swap_amt_7d - calc_cex_transfer_amt_7d THEN -airdrop_amt - calc_swap_amt_7d - calc_cex_transfer_amt_7d ELSE net_transfer_amt_7d END AS calc_transfer_amt_7d FROM base_0 ), t0 AS ( SELECT from_address AS address , block_timestamp , to_address , amount AS amt , SUM(amt) OVER (PARTITION BY u.from_address ORDER BY block_timestamp, to_address, amt) AS tot_transferred , CASE WHEN r.calc_transfer_amt > 0 THEN 0 ELSE -r.calc_transfer_amt END AS base_transfer_amt , CASE WHEN r.calc_transfer_amt_24h > 0 THEN 0 ELSE -r.calc_transfer_amt_24h END AS base_transfer_amt_24h , CASE WHEN r.calc_transfer_amt_7d > 0 THEN 0 ELSE -r.calc_transfer_amt_7d END AS base_transfer_amt_7d FROM ethereum.udm_events u JOIN addys r ON r.address = u.from_address AND u.block_timestamp > r.rec_time JOIN constants c ON c.c_contract_address = u.contract_address WHERE to_label IS NULL AND (r.calc_transfer_amt < 0 OR r.calc_transfer_amt_24h < 0 OR r.calc_transfer_amt_7d < 0) ORDER BY 1, 2 ), t1 AS ( SELECT to_address AS address , block_timestamp , LAG(tot_transferred, 1) OVER (PARTITION BY address ORDER BY tot_transferred) AS prv_transferred , CASE WHEN tot_transferred > base_transfer_amt AND base_transfer_amt - COALESCE(prv_transferred, 0) <= 0 THEN 0 WHEN tot_transferred > base_transfer_amt THEN base_transfer_amt - COALESCE(prv_transferred, 0) ELSE amt END AS transfer_amt , CASE WHEN tot_transferred > base_transfer_amt_24h AND base_transfer_amt_24h - COALESCE(prv_transferred, 0) <= 0 THEN 0 WHEN tot_transferred > base_transfer_amt_24h THEN base_transfer_amt_24h - COALESCE(prv_transferred, 0) ELSE amt END AS transfer_amt_24h , CASE WHEN tot_transferred > base_transfer_amt_7d AND base_transfer_amt_7d - COALESCE(prv_transferred, 0) <= 0 THEN 0 WHEN tot_transferred > base_transfer_amt_7d THEN base_transfer_amt_7d - COALESCE(prv_transferred, 0) ELSE amt END AS transfer_amt_7d FROM t0 ), rec_addys AS ( SELECT address , MIN(block_timestamp) AS rec_time , MAX(block_timestamp) AS rec_time_mx , SUM(transfer_amt) AS base_transfer_amt , SUM(transfer_amt_24h) AS base_transfer_amt_24h , SUM(transfer_amt_7d) AS base_transfer_amt_7d FROM t1 GROUP BY 1 ), cex_transfers_to AS ( SELECT to_address AS address , SUM(amount) AS cex_transfer_to_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS cex_transfer_to_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS cex_transfer_to_amt_7d FROM ethereum.udm_events u JOIN rec_addys r ON r.address = u.to_address AND u.block_timestamp > r.rec_time JOIN constants c ON c.c_contract_address = u.contract_address WHERE from_label_type = 'cex' GROUP BY 1 ), cex_transfers_from AS ( SELECT from_address AS address , SUM(amount) AS cex_transfer_from_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS cex_transfer_from_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS cex_transfer_from_amt_7d FROM ethereum.udm_events u JOIN rec_addys r ON r.address = u.from_address AND u.block_timestamp > r.rec_time JOIN constants c ON c.c_contract_address = u.contract_address WHERE to_label_type = 'cex' GROUP BY 1 ), swaps_to AS ( SELECT r.address , SUM(amount) AS swap_to_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS swap_to_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS swap_to_amt_7d FROM ethereum.udm_events u JOIN rec_addys r ON r.address = u.to_address JOIN constants c ON c.c_contract_address = u.contract_address JOIN swaps_tx s ON s.tx_id = u.tx_id WHERE from_label_type = 'dex' AND amount > 0 AND u.block_timestamp > r.rec_time GROUP BY 1 ), swaps_from AS ( SELECT r.address , SUM(amount) AS swap_from_amt , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 24 THEN amount ELSE 0 END) AS swap_from_amt_24h , SUM( CASE WHEN DATEDIFF(hours, rec_time, block_timestamp) < 168 THEN amount ELSE 0 END) AS swap_from_amt_7d FROM ethereum.udm_events u JOIN rec_addys r ON r.address = u.from_address JOIN constants c ON c.c_contract_address = u.contract_address JOIN swaps_tx s ON s.tx_id = u.tx_id WHERE to_label_type = 'dex' AND amount > 0 AND u.block_timestamp > r.rec_time GROUP BY 1 ), swaps AS ( SELECT COALESCE(t.address, f.address) AS address , COALESCE(swap_to_amt, 0) AS swap_to_amt , COALESCE(swap_from_amt, 0) AS swap_from_amt , COALESCE(swap_to_amt, 0) - COALESCE(swap_from_amt, 0) AS net_swap_amt , COALESCE(swap_to_amt_24h, 0) - COALESCE(swap_from_amt_24h, 0) AS net_swap_amt_24h , COALESCE(swap_to_amt_7d, 0) - COALESCE(swap_from_amt_7d, 0) AS net_swap_amt_7d FROM swaps_to t FULL OUTER JOIN swaps_from f ON f.address = t.address ), cex_transfers AS ( SELECT COALESCE(t.address, f.address) AS address , COALESCE(cex_transfer_to_amt, 0) AS cex_transfer_to_amt , COALESCE(cex_transfer_from_amt, 0) AS cex_transfer_from_amt , COALESCE(cex_transfer_to_amt, 0) - COALESCE(cex_transfer_from_amt, 0) AS net_cex_transfer_amt , COALESCE(cex_transfer_to_amt_24h, 0) - COALESCE(cex_transfer_from_amt_24h, 0) AS net_cex_transfer_amt_24h , COALESCE(cex_transfer_to_amt_7d, 0) - COALESCE(cex_transfer_from_amt_7d, 0) AS net_cex_transfer_amt_7d FROM cex_transfers_to t FULL OUTER JOIN cex_transfers_from f ON f.address = t.address ), base AS ( SELECT r.address , r.base_transfer_amt , r.base_transfer_amt_24h , r.base_transfer_amt_7d , COALESCE(swap_to_amt, 0) AS swap_to_amt , COALESCE(swap_from_amt, 0) AS swap_from_amt , COALESCE(net_swap_amt, 0) AS net_swap_amt , COALESCE(net_swap_amt_24h, 0) AS net_swap_amt_24h , COALESCE(net_swap_amt_7d, 0) AS net_swap_amt_7d , COALESCE(cex_transfer_to_amt, 0) AS cex_transfer_to_amt , COALESCE(cex_transfer_from_amt, 0) AS cex_transfer_from_amt , COALESCE(net_cex_transfer_amt, 0) AS net_cex_transfer_amt , COALESCE(net_cex_transfer_amt_24h, 0) AS net_cex_transfer_amt_24h , COALESCE(net_cex_transfer_amt_7d, 0) AS net_cex_transfer_amt_7d FROM rec_addys r LEFT JOIN swaps s ON s.address = r.address LEFT JOIN cex_transfers c ON c.address = r.address ), calc AS ( SELECT * , CASE WHEN net_swap_amt > 0 THEN 0 WHEN net_swap_amt <= -base_transfer_amt THEN -base_transfer_amt ELSE net_swap_amt END AS calc_swap_amt , CASE WHEN net_cex_transfer_amt > 0 THEN 0 WHEN net_cex_transfer_amt <= -base_transfer_amt - calc_swap_amt THEN -base_transfer_amt - calc_swap_amt ELSE net_cex_transfer_amt END AS calc_cex_transfer_amt , CASE WHEN net_swap_amt_24h > 0 THEN 0 WHEN net_swap_amt_24h <= -base_transfer_amt_24h THEN -base_transfer_amt_24h ELSE net_swap_amt_24h END AS calc_swap_amt_24h , CASE WHEN net_cex_transfer_amt_24h > 0 THEN 0 WHEN net_cex_transfer_amt_24h <= -base_transfer_amt_24h - calc_swap_amt_24h THEN -base_transfer_amt_24h - calc_swap_amt_24h ELSE net_cex_transfer_amt_24h END AS calc_cex_transfer_amt_24h , CASE WHEN net_swap_amt_7d > 0 THEN 0 WHEN net_swap_amt_7d <= -base_transfer_amt_7d THEN -base_transfer_amt_7d ELSE net_swap_amt_7d END AS calc_swap_amt_7d , CASE WHEN net_cex_transfer_amt_7d > 0 THEN 0 WHEN net_cex_transfer_amt_7d <= -base_transfer_amt_7d - calc_swap_amt_7d THEN -base_transfer_amt_7d - calc_swap_amt_7d ELSE net_cex_transfer_amt_7d END AS calc_cex_transfer_amt_7d FROM base ), avg_0 AS ( SELECT SUM(airdrop_amt) AS tot_airdrop_amt , SUM(calc_transfer_amt) AS tot_transfer_amt , SUM(calc_swap_amt) AS tot_swap_amt , SUM(calc_cex_transfer_amt) AS tot_cex_transfer_amt , SUM(calc_transfer_amt_24h) AS tot_transfer_amt_24h , SUM(calc_swap_amt_24h) AS tot_swap_amt_24h , SUM(calc_cex_transfer_amt_24h) AS tot_cex_transfer_amt_24h , SUM(calc_transfer_amt_7d) AS tot_transfer_amt_7d , SUM(calc_swap_amt_7d) AS tot_swap_amt_7d , SUM(calc_cex_transfer_amt_7d) AS tot_cex_transfer_amt_7d FROM addys ), avg_1 AS ( SELECT AVG(base_transfer_amt) AS avg_base_transfer_amt , AVG(calc_swap_amt) AS avg_swap_amt , AVG(calc_cex_transfer_amt) AS avg_cex_transfer_amt , SUM(base_transfer_amt) AS tot_base_transfer_amt , SUM(calc_swap_amt) AS tot_swap_amt , SUM(calc_cex_transfer_amt) AS tot_cex_transfer_amt , SUM(base_transfer_amt_24h) AS tot_base_transfer_amt_24h , SUM(calc_swap_amt_24h) AS tot_swap_amt_24h , SUM(calc_cex_transfer_amt_24h) AS tot_cex_transfer_amt_24h , SUM(base_transfer_amt_7d) AS tot_base_transfer_amt_7d , SUM(calc_swap_amt_7d) AS tot_swap_amt_7d , SUM(calc_cex_transfer_amt_7d) AS tot_cex_transfer_amt_7d FROM calc ), avg AS ( SELECT a.*, a0.tot_airdrop_amt FROM avg_1 a JOIN avg_0 a0 ON TRUE ), final AS ( SELECT -tot_swap_amt / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Swap' AS metric FROM avg_0 UNION SELECT -tot_cex_transfer_amt / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Cex Transfer' AS metric FROM avg_0 UNION SELECT (tot_airdrop_amt + tot_swap_amt + tot_cex_transfer_amt + tot_transfer_amt) / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Hold / LP' AS metric FROM avg_0 UNION SELECT -tot_swap_amt / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Direct Transfer -> Swap' AS metric FROM avg UNION SELECT -tot_cex_transfer_amt / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Direct Transfer -> CEX' AS metric FROM avg UNION SELECT (tot_base_transfer_amt + tot_swap_amt + tot_cex_transfer_amt) / tot_airdrop_amt AS pct, 'Current' AS timeframe, 'Direct Transfer -> Hold / LP / DT' AS metric FROM avg UNION SELECT -tot_swap_amt_24h / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Swap' AS metric FROM avg_0 UNION SELECT -tot_cex_transfer_amt_24h / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Cex Transfer' AS metric FROM avg_0 UNION SELECT (tot_airdrop_amt + tot_swap_amt_24h + tot_cex_transfer_amt_24h + tot_transfer_amt_24h) / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Hold / LP' AS metric FROM avg_0 UNION SELECT -tot_swap_amt_24h / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Direct Transfer -> Swap' AS metric FROM avg UNION SELECT -tot_cex_transfer_amt_24h / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Direct Transfer -> CEX' AS metric FROM avg UNION SELECT (tot_base_transfer_amt_24h + tot_swap_amt_24h + tot_cex_transfer_amt_24h) / tot_airdrop_amt AS pct, '24h' AS timeframe, 'Direct Transfer -> Hold / LP / DT' AS metric FROM avg UNION SELECT -tot_swap_amt_7d / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Swap' AS metric FROM avg_0 UNION SELECT -tot_cex_transfer_amt_7d / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Cex Transfer' AS metric FROM avg_0 UNION SELECT (tot_airdrop_amt + tot_swap_amt_7d + tot_cex_transfer_amt_7d + tot_transfer_amt_7d) / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Hold / LP' AS metric FROM avg_0 UNION SELECT -tot_swap_amt_7d / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Direct Transfer -> Swap' AS metric FROM avg UNION SELECT -tot_cex_transfer_amt_7d / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Direct Transfer -> CEX' AS metric FROM avg UNION SELECT (tot_base_transfer_amt_7d + tot_swap_amt_7d + tot_cex_transfer_amt_7d) / tot_airdrop_amt AS pct, '7d' AS timeframe, 'Direct Transfer -> Hold / LP / DT' AS metric FROM avg ) SELECT * FROM final ORDER BY pct DESC

1inch Airdrop