Tobi_1permanent-olive
Updated 2024-09-28
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 daily_price_trend AS (
SELECT
TOKEN_ADDRESS,
SYMBOL,
HOUR::DATE AS date, -- Convert HOUR to a date format
AVG(PRICE) AS avg_daily_price,
LAG(AVG(PRICE)) OVER (PARTITION BY TOKEN_ADDRESS ORDER BY HOUR::DATE) AS previous_avg_daily_price
FROM
base.price.ez_prices_hourly;
WHERE
TOKEN_ADDRESS IN (
'0x532f27101965dd16442e59d40670faf5ebb142e4',
'0xafb89a09d82fbde58f18ac6437b3fc81724e4df6',
'0xac1bd2486aaf3b5c0fc3fd868558b082a531b2b4',
'0x4ed4e862860bed51a9570b96d89af5e1b0efefed',
'0x6921b130d297cc43754afba22e5eac0fbf8db75b',
'0xc48e605c7b722a57277e087a6170b9e227e5ac0a',
'0x50da645f148798f68ef2d7db7c1cb22a6819bb2c',
'0xf0268c5f9aa95baf5c25d646aabb900ac12f0800',
'0xdfd579dd6aeb232e95a15d964a135a61925b5c93',
'0xbc45647ea894030a4e9801ec03479739fa2485f0'
)
AND HOUR >= DATEADD(DAY, -30, CURRENT_TIMESTAMP) -- Filter for the last 30 days
GROUP BY
TOKEN_ADDRESS, SYMBOL, HOUR::DATE
)
-- Final query to calculate percentage change and display the trend
SELECT
TOKEN_ADDRESS,
SYMBOL,
date,
avg_daily_price,
previous_avg_daily_price,
CASE
WHEN previous_avg_daily_price IS NOT NULL THEN
QueryRunArchived: QueryRun has been archived