select block_timestamp::date as date,

case when date >= '2022-11-08' then 'During and After FTX & Alameda Collapse'

else 'Before Collapse' end as timespan,

case when pool_name ilike '%usd%' or pool_name ilike '%dai%' or pool_name ilike '%mai%' or pool_name ilike '%eur%' or pool_name ilike '%frax%' then 'Stablecoins Pools'

else 'Non Stablecoins Pools' end as pool_type,

count (distinct tx_hash) as Swaps_Count,

count (distinct recipient) as Swappers_Count,

abs (sum (amount0_usd)) as Total_Volume,

abs (avg (amount0_usd)) as Average_Volume

from ethereum.uniswapv3.ez_swaps

where block_timestamp >= CURRENT_DATE - 30

group by 1,2,3)

select timespan,

pool_type,

avg (swaps_count) as Average_Swaps_Count,

avg (Swappers_Count) as Average_Swapper_Count,

avg (total_volume) as Average_Volume

from maintable

group by 1,2