date_trunc('day',block_timestamp) as day,

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 'WBTC-WETH%' THEN 'WBTC-WETH'

END as pool,

sum(amount0_usd + amount1_usd) as fees_collected,

count(liquidity_provider) as collections

FROM uniswapv3.position_collected_fees

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

AND (pool_name LIKE 'USDC-WETH%'

OR pool_name LIKE 'WETH-USDT%'

OR pool_name LIKE 'USDC-USDT%'

OR pool_name LIKE 'WBTC-WETH%')

GROUP BY day,pool

ORDER BY DAY