SELECT

date_trunc('day',hour) as day,

avg(price) as price_avg,

token_address,

symbol

FROM ethereum.token_prices_hourly

WHERE hour >= getdate() - interval '14 days'

GROUP BY day, token_address, symbol

ORDER BY day

),

t1 as (

SELECT

dp.day as day,

-- avg(price_lower_0_1_usd) as low0,

-- avg(price_upper_0_1_usd) as up0,

COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 < 5 THEN 1 END) as under_5_percent,

COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 <= 30 THEN 1 END) - under_5_percent as under_30_percent,

COUNT(CASE WHEN (price_upper_0_1_usd - price_avg)/price_avg*100 > 50 THEN 1 END) as over_50_percent,

COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 < 5 THEN 1 END) as under_5_percent_low,

COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 <= 30 THEN 1 END) - under_5_percent_low as under_30_percent_low,

COUNT(CASE WHEN (abs(price_lower_0_1_usd - price_avg))/price_avg*100 > 50 THEN 1 END) as over_50_percent_low,

-- avg(price_lower_1_0_usd) as low1,

-- avg(price_upper_1_0_usd) as up1,

count(liquidity_provider) as lp,

token1_address

FROM uniswapv3.positions

INNER JOIN dp on dp.day = date_trunc('day',block_timestamp) AND token_address = token1_address

WHERE block_timestamp >= getdate() - interval '14 days'

AND token1_symbol = 'UNI'

AND price_upper_0_1_usd > price_avg

AND price_lower_0_1_usd < price_avg

GROUP BY day, token1_address

ORDER BY day

),

t0 as (

SELECT

dp.day as day,-- date_trunc('day',block_timestamp) as day,

avg(price_lower_1_0_usd) as low1,

avg(price_upper_1_0_usd) as up1,

COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 < 5 THEN 1 END) as under_5_percent,

COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 <= 30 THEN 1 END) - under_5_percent as under_30_percent,

COUNT(CASE WHEN (price_upper_1_0_usd - price_avg)/price_avg*100 > 50 THEN 1 END) as over_50_percent,

COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 < 5 THEN 1 END) as under_5_percent_low,

COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 <= 30 THEN 1 END) - under_5_percent_low as under_30_percent_low,

COUNT(CASE WHEN (abs(price_lower_1_0_usd - price_avg))/price_avg*100 > 50 THEN 1 END) as over_50_percent_low,

count(liquidity_provider) as lp,

token0_address

FROM uniswapv3.positions

INNER JOIN dp on dp.day = date_trunc('day',block_timestamp) AND token_address = token0_address

WHERE block_timestamp >= getdate() - interval '14 days'

AND token0_symbol = 'UNI'

AND price_upper_1_0_usd > price_avg

AND price_lower_1_0_usd < price_avg

GROUP BY day, token0_address

ORDER BY day

)

SELECT

dp.day, dp.symbol, price_avg, under_5_percent+under_30_percent+over_50_percent as sum_u, under_5_percent_low+under_30_percent_low+over_50_percent_low as sum_l, under_5_percent, under_30_percent, over_50_percent, under_5_percent_low, under_30_percent_low, over_50_percent_low,

price_avg*1.05 as u10,

price_avg*1.3 as u20,

price_avg*1.5 as u30,

price_avg*0.95 as l10,

price_avg*0.7 as l20,

price_avg*0.5 as l30,

lp

FROM dp

INNER JOIN t0 on dp.day = t0.day

-- INNER JOIN t0 on dp.day = t0.day

WHERE dp.token_address = t0.token0_address

-- AND dp.token_address = t0.token1_address

ORDER BY dp.day