WITH dp as ( 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 = 'WBTC' 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 = 'WBTC' 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, t1.under_5_percent+t1.under_30_percent+t1.over_50_percent+t0.under_5_percent+t0.under_30_percent+t0.over_50_percent as sum_u, t1.under_5_percent_low+t1.under_30_percent_low+t1.over_50_percent_low+t0.under_5_percent_low+t0.under_30_percent_low+t0.over_50_percent_low as sum_l, t1.under_5_percent + t0.under_5_percent as under_5_percent, t1.under_30_percent + t0.under_30_percent as under_30_percent, t1.over_50_percent+t0.over_50_percent as over_50_percent, t1.under_5_percent_low+t0.under_5_percent as under_5_percent_low, t1.under_30_percent_low+t0.under_30_percent_low as under_30_percent_low, t1.over_50_percent_low+t0.over_50_percent_low as 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, t0.lp+t1.lp as lp FROM dp INNER JOIN t0 on dp.day = t0.day INNER JOIN t1 on dp.day = t1.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