MEDIAN()

avg(p.price_lower_0_1_USD) price_lower_0_1_USD,

sum(p.price_lower_0_1_USD * (f.amount0_usd+amount1_USD)) / sum(f.amount0_usd + amount1_USD) weighted_lower,

avg(p.price_upper_0_1_USD) price_upper_0_1_USD,

sum(p.price_upper_0_1_USD * (f.amount0_usd + amount1_USD)) / sum(f.amount0_usd + amount1_USD) weighted_upper,

sum(f.amount0_usd) amount0_usd,

sum(f.amount1_USD) amount1_usd,

date_trunc('hour', p.block_timestamp) as hour_,

date_trunc('day', p.block_timestamp) as day_,

avg(e.price) as price

from uniswapv3.positions p

inner join uniswapv3.position_collected_fees f on f.tx_id = p.tx_id

left join ethereum.token_prices_hourly e on e.hour = date_trunc('hour', p.block_timestamp) and e.symbol = 'ETH'

where

p.pool_name like 'USD%_WETH%' --'USDC_WETH%'

and f.amount1_usd > 0

and f.amount0_USD > 0

and p.block_timestamp >= '2021-05-11T15:00:00Z'

group by

date_trunc('hour', p.block_timestamp) ,

date_trunc('day', p.block_timestamp),

e.price

order by

amount1_usd desc