SELECT

date_trunc('day',block_timestamp) as day, platform,

CASE

WHEN pool_name LIKE 'USDC-WETH%' THEN 'USDC-WETH'

WHEN pool_name LIKE 'WETH-USDT%' THEN 'WETH-USDT'

WHEN pool_name LIKE 'USDC-USDT%' THEN 'USDC-USDT'

WHEN pool_name LIKE 'DAI-WETH%' THEN 'DAI-WETH'

WHEN pool_name LIKE 'WBTC-WETH%' THEN 'WBTC-WETH'

END as pool,

sum(amount_usd) as swapvolume,

-- avg(liquidity_adjusted) as liqudity,

count(DISTINCT from_address) as swappers

FROM ethereum.dex_swaps

WHERE block_timestamp >= getdate() - interval '7 days'

AND amount_usd is not null

AND platform IN ('uniswap-v3')

-- AND sender<>recipient

AND (pool_name LIKE 'USDC-WETH%'

OR pool_name LIKE 'WETH-USDT%'

OR pool_name LIKE 'USDC-USDT%'

OR pool_name LIKE 'DAI-WETH%'

OR pool_name LIKE 'WBTC-WETH%')

GROUP BY day, pool, platform

ORDER BY swapvolume DESC)

UNION ALL

SELECT

date_trunc('day',block_timestamp) as day, platform,

CASE

WHEN pool_name LIKE 'USDC-WETH%' THEN 'USDC-WETH'

WHEN pool_name LIKE 'WETH-USDT%' THEN 'WETH-USDT'

WHEN pool_name LIKE 'USDC-USDT%' THEN 'USDC-USDT'

WHEN pool_name LIKE 'DAI-WETH%' THEN 'DAI-WETH'

WHEN pool_name LIKE 'WBTC-WETH%' THEN 'WBTC-WETH'