granadohoNumber of Days Above and Below $52
    Updated 2022-02-26
    with closed_price as (
    select
    *
    from (
    select
    symbol,
    date(block_timestamp) as date,
    block_timestamp,
    price_usd,
    dense_rank() over ( partition by date order by block_timestamp desc) as index
    from terra.oracle_prices
    where symbol = 'LUNA'
    )
    where index = 1 and EXTRACT(HOUR FROM block_timestamp) = 23 and date >= '2021-11-01'
    order by date
    )

    select
    a.currency,
    a.average_price,
    a.number_of_days_above_52,
    b.number_of_days_below_52
    from (
    select
    symbol as currency,
    count(distinct date) as number_of_days_above_52,
    avg(price_usd) as average_price
    from closed_price
    where price_usd > 52
    group by currency
    ) a
    inner join (
    select
    symbol as currency,
    count(distinct date) as number_of_days_below_52
    from closed_price
    Run a query to Download Data