WITH base_stat AS ( SELECT 'Ethereum' AS chain, 'L1' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM ethereum.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'Arbitrum' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM arbitrum.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'Avalanche' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM avalanche.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'Base' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM base.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'BSC' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM bsc.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'Optimism' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM optimism.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL UNION ALL SELECT 'Polygon' AS chain, 'L2' AS chain_type, symbol_in, symbol_out, CONCAT (symbol_in, '-', symbol_out) AS token_pair, CASE WHEN block_timestamp::date < '2023-10-17' THEN 'Before_hike' WHEN block_timestamp::date BETWEEN '2023-10-17' AND '2024-04-10' THEN 'First_hike' WHEN block_timestamp::date >= '2024-04-10' THEN 'Second_hike' END AS time_range, amount_in_usd AS volume, tx_hash, sender, block_timestamp FROM polygon.defi.ez_dex_swaps WHERE platform LIKE 'uniswap%' AND block_timestamp::date >= current_date - 365 HAVING volume IS NOT NULL ) SELECT time_range, chain, chain_type, -- block_timestamp::date AS timespan, CASE WHEN volume < 10 THEN 'a. <10' WHEN volume >= 10 AND volume < 50 THEN 'b. 10-50' WHEN volume >= 50 AND volume < 100 THEN 'c. 50-100' WHEN volume >= 100 AND volume < 500 THEN 'd. 100-500' WHEN volume >= 500 AND volume < 1000 THEN 'e. 500-1000' WHEN volume >= 1000 AND volume < 5000 THEN 'f. 1000-5000' WHEN volume >= 5000 AND volume < 10000 THEN 'g. 5000-10000' WHEN volume >= 10000 THEN 'h. >10000' END AS distribution, COUNT (DISTINCT tx_hash) AS tx, -- sum (tx) over (PARTITION BY chain ORDER BY timespan) AS cumul_tx, -- COUNT (DISTINCT sender) AS users, -- sum (users) over (PARTITION BY chain ORDER BY timespan) AS cumul_users, -- -- token_pair, -- -- pair_type, sum (volume) AS total_volume -- sum (total_volume) over (PARTITION BY chain ORDER BY timespan) AS cumul_volume -- sum ((0.15/100) * volume) AS interface_fees FROM base_stat GROUP BY 1, 2, 3, 4 -- ORDER BY timespan -- LIMIT 300

Query	Creator	Created
Daily Uniswap Interface Fees uniswap kyberswap woofi hashflow quickswap	zackmendel	Jul 16, 2024
Distribution by Swap Size uniswap kyberswap woofi hashflow quickswap	zackmendel	Jul 17, 2024
Uniswap User Retention Rate by Chain uniswap kyberswap woofi hashflow quickswap	zackmendel	Jul 17, 2024
Uniswap User Retention Rate uniswap kyberswap woofi hashflow quickswap	zackmendel	Jul 17, 2024

zackmendel