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 liquidity,

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

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

price_lower_0_1_USD,

price_upper_0_1_USD

from uniswapv3.positions p

where

( p.pool_name like 'UNI_WETH%' or p.pool_name like 'USDC_WETH%' or

p.pool_name like 'WBTC_WETH%' or p.pool_name like 'WETH_USDT%'

)

--and price_upper_0_1_USD > 9000

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

-- ,

-- cte_group as (

-- select

-- price_lower_0_1_USD,

-- price_upper_0_1_USD

-- coalesce((avg((price_lower_0_1_USD * lv_liquidity) / (lv_liquidity)) + avg((price_upper_0_1_USD * lv_liquidity) / (lv_liquidity))) / 2 ,

-- (avg(price_lower_0_1_USD) + avg(price_upper_0_1_USD)) / 2 ) as Volume_middle,

-- coalesce((sum(price_lower_0_1_USD * lv_liquidity) / sum(lv_liquidity)) ,avg(p.price_lower_0_1_USD)) as Volume_lower,

-- coalesce((sum(price_upper_0_1_USD * lv_liquidity) / sum(lv_liquidity)) ,avg(p.price_upper_0_1_USD)) as Volume_upper,

-- avg(p.price_lower_0_1_USD) price_lower_0_1_USD,

-- (avg(price_lower_0_1_USD) + avg(price_upper_0_1_USD)) / 2 as middle,

-- avg(p.price_upper_0_1_USD) price_upper_0_1_USD,

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

-- -- 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,

-- sum(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 = 'ETH'

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

-- where

-- p.pool_name like 'USDC_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_0_1_USD > 9000

-- group by

-- date_trunc('hour', p.block_timestamp) ,

-- date_trunc('day', p.block_timestamp),

-- e.price

-- )

, cte_bins as (

select

liquidity,

case when price_lower_0_1_USD < 2000 and price_upper_0_1_USD > 2100 THEN liquidity ELSE 0 END AS e2000,

case when price_lower_0_1_USD < 2100 and price_upper_0_1_USD > 2200 THEN liquidity ELSE 0 END AS e2100,

case when price_lower_0_1_USD < 2200 and price_upper_0_1_USD > 2300 THEN liquidity ELSE 0 END AS e2200,

case when price_lower_0_1_USD < 2300 and price_upper_0_1_USD > 2400 THEN liquidity ELSE 0 END AS e2300,

case when price_lower_0_1_USD < 2400 and price_upper_0_1_USD > 2500 THEN liquidity ELSE 0 END AS e2400,

case when price_lower_0_1_USD < 2500 and price_upper_0_1_USD > 2600 THEN liquidity ELSE 0 END AS e2500,

case when price_lower_0_1_USD < 2600 and price_upper_0_1_USD > 2700 THEN liquidity ELSE 0 END AS e2600,

case when price_lower_0_1_USD < 2700 and price_upper_0_1_USD > 2800 THEN liquidity ELSE 0 END AS e2700,

case when price_lower_0_1_USD < 2800 and price_upper_0_1_USD > 2900 THEN liquidity ELSE 0 END AS e2800,

case when price_lower_0_1_USD < 2900 and price_upper_0_1_USD > 3000 THEN liquidity ELSE 0 END AS e2900,

case when price_lower_0_1_USD < 3000 and price_upper_0_1_USD > 3100 THEN liquidity ELSE 0 END AS e3000,

case when price_lower_0_1_USD < 3100 and price_upper_0_1_USD > 3200 THEN liquidity ELSE 0 END AS e3100,

case when price_lower_0_1_USD < 3200 and price_upper_0_1_USD > 3300 THEN liquidity ELSE 0 END AS e3200,

case when price_lower_0_1_USD < 3300 and price_upper_0_1_USD > 3400 THEN liquidity ELSE 0 END AS e3300,

case when price_lower_0_1_USD < 3400 and price_upper_0_1_USD > 3500 THEN liquidity ELSE 0 END AS e3400,

case when price_lower_0_1_USD < 3500 and price_upper_0_1_USD > 3600 THEN liquidity ELSE 0 END AS e3500,

case when price_lower_0_1_USD < 3600 and price_upper_0_1_USD > 3700 THEN liquidity ELSE 0 END AS e3600,

case when price_lower_0_1_USD < 3700 and price_upper_0_1_USD > 3800 THEN liquidity ELSE 0 END AS e3700,

case when price_lower_0_1_USD < 3800 and price_upper_0_1_USD > 3900 THEN liquidity ELSE 0 END AS e3800,

case when price_lower_0_1_USD < 3900 and price_upper_0_1_USD > 4000 THEN liquidity ELSE 0 END AS e3900,

case when price_lower_0_1_USD < 4000 and price_upper_0_1_USD > 4100 THEN liquidity ELSE 0 END AS e4000,

case when price_lower_0_1_USD < 4100 and price_upper_0_1_USD > 4200 THEN liquidity ELSE 0 END AS e4100,

case when price_lower_0_1_USD < 4200 and price_upper_0_1_USD > 4300 THEN liquidity ELSE 0 END AS e4200,

case when price_lower_0_1_USD < 4300 and price_upper_0_1_USD > 4400 THEN liquidity ELSE 0 END AS e4300,

case when price_lower_0_1_USD < 4400 and price_upper_0_1_USD > 4500 THEN liquidity ELSE 0 END AS e4400,

case when price_lower_0_1_USD < 4500 and price_upper_0_1_USD > 4600 THEN liquidity ELSE 0 END AS e4500,

case when price_lower_0_1_USD < 4600 and price_upper_0_1_USD > 4700 THEN liquidity ELSE 0 END AS e4600,

case when price_lower_0_1_USD < 4700 and price_upper_0_1_USD > 4800 THEN liquidity ELSE 0 END AS e4700,

case when price_lower_0_1_USD < 4800 and price_upper_0_1_USD > 4900 THEN liquidity ELSE 0 END AS e4800,

case when price_lower_0_1_USD < 4900 and price_upper_0_1_USD > 5000 THEN liquidity ELSE 0 END AS e4900,

case when price_lower_0_1_USD < 5000 and price_upper_0_1_USD > 5100 THEN liquidity ELSE 0 END AS e5000,

case when price_lower_0_1_USD < 5100 and price_upper_0_1_USD > 5200 THEN liquidity ELSE 0 END AS e5100,

case when price_lower_0_1_USD < 5200 and price_upper_0_1_USD > 5300 THEN liquidity ELSE 0 END AS e5200,

case when price_lower_0_1_USD < 5300 and price_upper_0_1_USD > 5400 THEN liquidity ELSE 0 END AS e5300,

case when price_lower_0_1_USD < 5400 and price_upper_0_1_USD > 5500 THEN liquidity ELSE 0 END AS e5400,

case when price_lower_0_1_USD < 5500 and price_upper_0_1_USD > 5600 THEN liquidity ELSE 0 END AS e5500,

price_lower_0_1_USD,

price_upper_0_1_USD,

hour_

from cte_volume

order by hour_

,

cte_sum as (

select

hour_,

sum(e2000)as e2000,

sum(e2100)as e2100,

sum(e2200)as e2200,

sum(e2300)as e2300,

sum(e2400)as e2400,

sum(e2500)as e2500,

sum(e2600)as e2600,

sum(e2700)as e2700,

sum(e2800)as e2800,

sum(e2900)as e2900,

sum(e3000)as e3000,

sum(e3100)as e3100,

sum(e3200)as e3200,

sum(e3300)as e3300,

sum(e3400)as e3400,

sum(e3500)as e3500,

sum(e3600)as e3600,

sum(e3700)as e3700,

sum(e3800)as e3800,

sum(e3900)as e3900,

sum(e4000)as e4000,

sum(e4100)as e4100,

sum(e4200)as e4200,

sum(e4300)as e4300,

sum(e4400)as e4400,

sum(e4500)as e4500,

sum(e4600)as e4600,

sum(e4700)as e4700,

sum(e4800)as e4800,

sum(e4900)as e4900,

sum(e5000)as e5000,

sum(e5100)as e5100,

sum(e5200)as e5200,

sum(e5300)as e5300,

sum(e5400)as e5400,

sum(e5500)as e5500

from cte_bins

group by hour_

select Hour_, Ltrim(price_bin,'E') as Price_bin , liquidity from cte_sum

unpivot(liquidity for price_bin in (

e2000

,e2100

,e2200

,e2300

,e2400

,e2500

,e2600

,e2700

,e2800

,e2900

,e3000

,e3100

,e3200

,e3300

,e3400

,e3500

,e3600

,e3700

,e3800

,e3900

,e4000

,e4100

,e4200

,e4300

,e4400

,e4500

,e4600

,e4700

,e4800

,e4900

,e5000

,e5100

,e5200

,e5300

,e5400

,e5500

))

order by price_bin