SocioCrypto$SEI RSI
Updated 2024-11-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
›
⌄
-- forked from RSI @ https://flipsidecrypto.xyz/edit/queries/fc947be5-6f4d-4655-87f9-0c189df9f9ec
WITH
sei_price as (
SELECT TO_TIMESTAMP(value[0]::string) as dates,
value[1] as price
FROM (
SELECT livequery.live.udf_api(
'https://api.coingecko.com/api/v3/coins/sei-network/market_chart?vs_currency=usd&days=90&interval=daily&precision=3') as response
),LATERAL FLATTEN (input => response:data:prices)
WHERE dates < current_date
),
tb as (
SELECT
dates,
price,
((price-LAG(price) over (ORDER BY dates))/LAG(price) over (ORDER BY dates))*100 as daily_change_SEI,
CASE WHEN daily_change_SEI > 0 THEN daily_change_SEI ELSE 0 END AS gain_SEI,
CASE WHEN daily_change_SEI < 0 THEN ABS(daily_change_SEI) ELSE 0 END AS loss_SEI
FROM SEI_price
),
tb1 as (
SELECT
dates,
price,
daily_change_SEI,
AVG(gain_SEI) over (ORDER BY dates ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as avg_gain_SEI,
AVG(loss_SEI) over (ORDER BY dates ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as avg_loss_SEI
FROM tb
)
SELECT dates,
price,
100-(100 / (1 + (avg_gain_SEI / avg_loss_SEI))) AS RSI_SEI
FROM tb1
WHERE dates >= DATEADD('year', -2, current_date)
HAVING avg_loss_SEI >0
QueryRunArchived: QueryRun has been archived