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

🦄 User Behaviour on Fee Changes