with cte_volume as (

select nullif(last_value (p.liquidity_adjusted) over (partition by p.pool_address, p.nf_token_id, p.liquidity_provider,date_trunc('hour', p.block_timestamp) order by p.liquidity_provider ),0) as lv_liquidity,

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

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

from uniswapv3.positions p

where

p.pool_name like 'LINK_WETH%' --'USDC_WETH%'

--and price_upper_1_0_USD < 9000

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

select

coalesce((avg((price_lower_1_0_USD * lv_liquidity) / (lv_liquidity)) + avg((price_upper_1_0_USD * lv_liquidity) / (lv_liquidity))) / 2 ,

(avg(price_lower_1_0_USD) + avg(price_upper_1_0_USD)) / 2 ) as Median_Eth_Price,

coalesce((sum(price_lower_1_0_USD * lv_liquidity) / sum(lv_liquidity)) ,avg(p.price_lower_1_0_USD)) as Min_ETH_Price,

coalesce((sum(price_upper_1_0_USD * lv_liquidity) / sum(lv_liquidity)) ,avg(p.price_upper_1_0_USD)) as Max_ETH_Price,

avg(p.price_lower_1_0_USD) price_lower_1_0_USD,

(avg(price_lower_1_0_USD) + avg(price_upper_1_0_USD)) / 2 as middle,

avg(p.price_upper_1_0_USD) price_upper_1_0_USD,

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

-- sum(p.price_upper_1_0_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 ETH_Price,

avg(lv_liquidity) liquidity

from uniswapv3.positions p

left 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 = 'LINK'

left join cte_volume v on v.hour_ = date_trunc('hour', p.block_timestamp)

where

p.pool_name like 'LINK_WETH%' --'USDC_WETH%'

--and f.amount1_usd > 0

--and f.amount0_USD > 0

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

-- and p.tx_id <> '0x1a9f328b61b81c52c119b56985e1533abca1c791e4bc48dbfbe4fd8fc97dda8d'

and price_upper_1_0_USD < 100

--and price_lower_1_0_USD > 50

group by

date_trunc('hour', p.block_timestamp) ,

date_trunc('day', p.block_timestamp),

e.price

order by

liquidity desc