SocioCrypto$SEI RSI
    Updated 2024-11-28
    -- 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