adriaparcerisasTop 25 token prices performance over the past 90 days copy
Updated 2023-10-18
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
›
⌄
-- forked from Top 25 token prices performance over the past 90 days @ https://flipsidecrypto.xyz/edit/queries/9dce82e8-823b-4ed5-bac1-5952def328b0
--For the top 25 tokens by market-cap,
--could we pull the average price of the each token over the last 12 months
--with the average deviation from that average over different periods (let’s say quarterly).
SELECT token,
AVG(close) AS avg_price,
(AVG(close) - AVG(CASE
WHEN recorded_hour BETWEEN DATEADD(day, -7, GETDATE()) AND GETDATE() THEN close
ELSE NULL
END)) / AVG(close) AS avg_deviation_quarterly_pct
FROM flow.price.fact_hourly_prices
WHERE recorded_hour BETWEEN DATEADD(month, -1, GETDATE()) AND GETDATE()
and token is not null
GROUP BY token order by avg_deviation_quarterly_pct desc
--select distinct id from crosschain.core.fact_hourly_prices
--order by 1 asc
Run a query to Download Data