PapasotChoice coin hourly and daily average
Updated 2022-05-10
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
›
⌄
with daily_average AS
(SELECT
date_trunc('day',block_hour) as block_day,
asset_name,
avg(price_usd) as daily_average
from algorand.prices_swap
where asset_name = 'Choice Coin'
and block_hour > '2022-04-01'
and block_hour < '2022-04-05'
group by block_day,asset_name
)
select
a.block_hour as date,
a.asset_name,
price_usd,
daily_average,
avg(price_usd) OVER(ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW )
as moving_average,
((price_usd-moving_average)/price_usd)*100 as percentage_change,
volatility_measure,
swaps_in_hour,
volume_usd_in_hour
from algorand.prices_swap a
full outer join daily_average b on block_day = a.block_hour
where a.asset_name = 'Choice Coin'
and a.block_hour >= '2022-04-01'
and a.block_hour < '2022-04-05'
Run a query to Download Data