feyikemicompetitive-tomato copy copy copy
Updated 2025-01-08
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
36
›
⌄
-- forked from Kruys-Collins / competitive-tomato copy copy @ https://flipsidecrypto.xyz/Kruys-Collins/q/DLPAqUT8T6wO/competitive-tomato-copy-copy
-- forked from competitive-tomato copy @ https://flipsidecrypto.xyz/studio/queries/15ab8b0b-58b9-4702-9a87-db7ef3b45b90
WITH CASH AS (
SELECT
date_trunc('day', hour) as date,
symbol,
AVG(price) as AVG_CASH_price,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS CASH_MA_7,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS CASH_MA_20,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS CASH_MA_50
FROM
aptos.price.ez_prices_hourly
WHERE
TOKEN_ADDRESS ILIKE '0x61ed8b048636516b4eaf4c74250fa4f9440d9c3e163d96aeb863fe658a4bdc67::CASH::CASH'
GROUP BY 1, 2
),
APT AS (
SELECT
date_trunc('day', hour) as date,
symbol,
TOKEN_ADDRESS,
AVG(price) as AVG_APT_price,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS APT_MA_7,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS APT_MA_20,
AVG(AVG(price)) OVER (ORDER BY date_trunc('day', hour) ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS APT_MA_50
FROM
aptos.price.ez_prices_hourly
WHERE
symbol = 'APT'
GROUP BY 1, 2, 3
)
SELECT
g.date,
g.symbol as CASH_symbol,
g.AVG_CASH_price as "AVG $CASH price",
QueryRunArchived: QueryRun has been archived