binhachonDoes Time of Day Affect Price?
Updated 2022-05-12
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
›
⌄
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