kidaDaily OOB
    Updated 2021-08-25
    WITH daily_price AS (
    SELECT
    TRUNC(hour, 'day') as date,
    token_address,
    AVG(price) as price
    FROM
    ethereum.token_prices_hourly
    WHERE hour > '2021-05-01' -- uniswapv3 launched in May
    GROUP BY 1, 2
    )

    SELECT
    p.date,
    SUM(IFF((p.price / p2.price) > price_upper_1_0 OR (p.price / p2.price) < price_lower_1_0, 1, 0)) as oob,
    SUM(IFF((p.price / p2.price) > price_upper_1_0 OR (p.price / p2.price) < price_lower_1_0, 0, 1)) as in_range
    FROM
    uniswapv3.positions ps
    JOIN
    daily_price p
    ON
    TRUNC(ps.block_timestamp, 'day') = p.date
    AND token0_address = p.token_address
    JOIN
    daily_price p2
    ON
    TRUNC(ps.block_timestamp, 'day') = p2.date
    AND token1_address = p2.token_address
    WHERE
    is_active = True
    AND p.date = p2.date
    AND pool_address = '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8'
    GROUP BY 1
    Run a query to Download Data