date_trunc('day', block_timestamp)::date as day,

iff(block_timestamp::date >= date('2022-11-08'), 'After FTX Crisis', 'Before FTX Crisis') as type,

pool_name,

pool_address,

count (distinct (tx_hash)) as swaps_number,

count (distinct (recipient)) as swappers_number,

sum(iff(amount0_usd > 0, amount0_usd , -1 * amount0_usd)) as total_amount_usd,

avg(iff(amount0_usd > 0, amount0_usd , -1 * amount0_usd)) as average_amount_usd,

median(iff(amount0_usd > 0, amount0_usd , -1 * amount0_usd)) as median_amount_usd,

row_number() over (partition by day order by total_amount_usd desc) as rank

from ethereum.uniswapv3.ez_swaps

where block_timestamp::date >= '2022-11-01'

and amount0_usd > 0 and pool_name is not null

group by pool_name, pool_address, day, type

qualify rank <= 10

select * from maintable

order by day asc, total_amount_usd desc