WITH swap_prep1_ AS ( -- swap transactions SELECT DISTINCT block_timestamp , tx_hash , origin_from_address AS swapper FROM avalanche.core.fact_event_logs WHERE 0=0 AND block_timestamp::DATE >= '{{start_date}}' AND tx_status = 'SUCCESS' AND event_name ilike 'swap' ) , swap_prep2_ AS ( -- transfer transactions SELECT block_timestamp , tx_hash , from_address , to_address , contract_address AS token_address , raw_amount FROM avalanche.core.fact_token_transfers WHERE 0=0 AND block_timestamp::DATE >= '{{start_date}}' ) , swap_ AS ( SELECT t1.block_timestamp , t1.tx_hash , t1.swapper , CASE WHEN t1.swapper = t2.from_address THEN 'from' WHEN t1.swapper = t2.to_address THEN 'to' END AS direction , t2.raw_amount/1e6 AS amount FROM swap_prep1_ AS t1 JOIN swap_prep2_ AS t2 ON t1.tx_hash = t2.tx_hash AND (t1.swapper = t2.from_address OR t1.swapper = t2.to_address) AND t2.token_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E') ) , transfer_ AS ( SELECT t1.block_timestamp , t1.tx_hash , t1.from_address , t1.to_address , t1.raw_amount/1e6 AS amount FROM swap_prep2_ AS t1 LEFT JOIN swap_prep1_ AS t2 ON t1.tx_hash = t2.tx_hash WHERE t2.tx_hash IS NULL AND t1.token_address = lower('0xB97EF9Ef8734C71904D8002F8b6Bc66Dd9c48a6E') ) , union_ AS ( SELECT block_timestamp, tx_hash, 'transfer' AS activity, from_address AS wallet_address, amount FROM transfer_ UNION SELECT block_timestamp, tx_hash, 'swap' AS activity, swapper AS wallet_address, amount FROM swap_ ) , sizing_ AS ( SELECT * , CASE WHEN amount <= 10 THEN 'size ≤ 10 USD' WHEN amount <= 100 THEN '10 < size ≤ 100 USD' WHEN amount <= 1000 THEN '100 < size ≤ 1k USD' WHEN amount <= 10000 THEN '1k < size ≤ 10k USD' WHEN amount <= 100000 THEN '10k < size ≤ 100k USD' ELSE '100k USD < size' END as type , CASE WHEN amount <= 10 THEN '1. size ≤ 10 USD' WHEN amount <= 100 THEN '2. 10 < size ≤ 100 USD' WHEN amount <= 1000 THEN '3. 100 < size ≤ 1k USD' WHEN amount <= 10000 THEN '4. 1k < size ≤ 10k USD' WHEN amount <= 100000 THEN '5. 10k < size ≤ 100k USD' ELSE '6. 100k USD < size' END as numbering FROM union_ ) SELECT activity , CASE WHEN activity = 'swap' THEN '1. swap' WHEN activity = 'transfer' THEN '2. transfer' END as activity_numbering , type , numbering , COUNT(DISTINCT(tx_hash)) AS tx_count FROM sizing_ GROUP BY 1, 3, 4 ORDER BY numbering, activity_numbering ------------------------------ -- PARAMETERS DESCRIPTION ------------------------------ -- {{start_date}} - the first date to be included in the analysis

Avalanche Swaps vs Transfers (Redux)