datavortexPRICE CHANGE 24hr
Updated 2024-09-18
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
›
⌄
WITH prices AS (
SELECT
symbol,
hour,
price,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS rn
FROM optimism.price.ez_prices_hourly
),
latest_prices AS (
SELECT
symbol,
price AS price_previous_hour,
hour AS hour_previous
FROM prices
WHERE rn = 1
),
previous_hour_prices AS (
SELECT
symbol,
price AS price_previous_hour
FROM prices
WHERE rn = 2
),
day_ago_prices AS (
SELECT
symbol,
price AS price_day_ago,
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS day_rn
FROM prices
WHERE hour < (SELECT MAX(hour) FROM prices WHERE rn = 1)
AND hour >= (SELECT MAX(hour) FROM prices WHERE rn = 1) - INTERVAL '1 DAY'
)
SELECT
l.symbol,
l.price_previous_hour AS price_previous_hour,
d.price_day_ago AS price_day_ago,
QueryRunArchived: QueryRun has been archived