Eman-RazRange of Price Change (RoPC) & Average % of Price Changes per Month
Updated 2024-07-26
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
›
⌄
WITH TABLE1 AS (select date_trunc('DAY',hour) as "Date", max(high)-min(low) as "RoPC"
FROM flow.price.fact_prices_ohlc_hourly
WHERE ASSET_ID='flovatar-dust' AND PROVIDER='coingecko'
group by 1
order by 1),
TABLE2 AS (with tab1 as (SELECT date_trunc('DAY',hour) as date, open as open_price
FROM flow.price.fact_prices_ohlc_hourly
WHERE ASSET_ID='flovatar-dust' AND PROVIDER='coingecko' and HOUR ilike '%00:00:00.000'
ORDER BY 1),
tab2 as (SELECT date_trunc('DAY',hour) as date, close as close_price
FROM flow.price.fact_prices_ohlc_hourly
WHERE ASSET_ID='flovatar-dust' AND PROVIDER='coingecko' and HOUR ilike '%23:00:00.000'
ORDER BY 1)
select tab1.date as "Date", ((close_price-open_price)/open_price)*100 as "%Price Change"
from tab1 left join tab2 on tab1.date=tab2.date
order by 1)
select DATE_TRUNC('MONTH',table1."Date") as "Date", AVG("RoPC") AS "RoPC", AVG("%Price Change") AS "%Price Change"
from table1 left join table2 on table1."Date"=table2."Date"
WHERE "%Price Change" IS NOT NULL
GROUP BY 1
order by 1
Auto-refreshes every 24 hours
QueryRunArchived: QueryRun has been archived