CryptoLionGrant V3 Price Predictions - BTC
    Updated 2022-11-27
    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