binhachonDoes Time of Day Affect Price?
    Updated 2022-05-12
    with top10_asset as (
    select
    asset_id,
    asset_name,
    sum({{ranking_metrics}}) as frequency
    from algorand.prices_swap
    where block_hour >= getdate() - interval'{{number_of_days}} days'
    and {{ranking_metrics}} is not null
    group by 1, 2
    order by frequency desc
    limit 10
    ),
    price_hour as (
    select
    date_trunc('day', block_hour) as time,
    block_hour,
    date_part(hour, block_hour) as hourpart,
    asset_id,
    asset_name,
    price_usd,
    sum(case when date_part(hour, block_hour) = 0 then price_usd else 0 end) over (partition by time, asset_id, asset_name) as open_price,
    (price_usd - open_price) / open_price * 100 as percent_increase
    from algorand.prices_swap
    where block_hour >= date_trunc('day', getdate()) - interval'{{number_of_days}} days'
    and asset_id in (select distinct asset_id from top10_asset)
    )
    select
    asset_id,
    asset_name,
    hourpart,
    avg(percent_increase) as avg_percent_increase,
    case when abs(avg_percent_increase) > {{threshold}} then avg_percent_increase / abs(avg_percent_increase) * {{threshold}} else avg_percent_increase end as normalize_value
    from price_hour
    group by 1, 2, 3

    Run a query to Download Data