select distinct

-- date_trunc('week',block_timestamp) as time,

pool_address,

pool_name,

last_value(token0_balance_usd) over (partition by pool_name order by block_timestamp) as one,

last_value(token1_balance_usd) over (partition by pool_name order by block_timestamp) as two,

one+two as tvl

from uniswapv3.pool_stats

where block_timestamp >= getdate() - interval '2 days'

and token0_balance_usd is not null

and token1_balance_usd is not null

order by tvl desc

limit 5)

select

-- tx_id,

date_trunc('hour',block_timestamp) as time,

pools.pool_name,

sum(case when amount0_usd < 0 then amount0_usd*(-1) else amount0_usd end) as a0,

sum(case when amount1_usd < 0 then amount1_usd*(-1) else amount1_usd end) as a1,

round((a0-a1)/a0*100,2) as slippage,

median(abs(slippage)) over (partition by pools.pool_name) as median_slippage

from uniswapv3.swaps

inner join pools on pools.pool_address = uniswapv3.swaps.pool_address

where block_timestamp >= getdate() - interval '3 months'

and amount0_usd is not null

and amount1_usd is not null

group by 1,2

order by 1 desc