SG Finviz

WITH earnings AS (
    SELECT ticker, prk, rk FROM EARS
),
opt AS (
    SELECT symbol, evr FROM optslam
),
wkly as (
    SELECT symbol,etf, week FROM weekly
),
sg_latest_file AS (
    SELECT MAX(file_name) KEEP (DENSE_RANK LAST ORDER BY etl_datetime) AS file_name
    FROM spot_gamma_metrics_full
),
sg_ranked AS (
    SELECT m.*,
           ROW_NUMBER() OVER (
               PARTITION BY m.symbol
               ORDER BY m.etl_datetime DESC, m.id DESC
           ) AS rn
    FROM spot_gamma_metrics_full m
    JOIN sg_latest_file f ON f.file_name = m.file_name
),
sg AS (
    SELECT id, symbol, key_gamma_strike, key_delta_strike, hedge_wall,
           ROUND(call_wall, 2) AS call_wall,
           ROUND(put_wall, 2) AS put_wall,
           ROUND(options_impact, 2) AS options_impact,
           file_name, etl_datetime
    FROM sg_ranked
    WHERE rn = 1
),
fv_ranked AS (
    SELECT f.*,
           ROW_NUMBER() OVER (
               PARTITION BY f.symbol
               ORDER BY f.etl_datetime DESC, f.id DESC
           ) AS rn
    FROM finviz_metrics_full f
),
fv AS (
    SELECT id, symbol, relative_volume, average_true_range, gap, earnings_date,
           price, short_float, short_ratio, high, low, prev_close,
           ROUND(market_cap_b, 2) AS market_cap_b,
           perf_1m, perf_2m, perf_3m, perf_5m, perf_10m, perf_15m, perf_1h,
           change_from_open, change, beta, open, etl_datetime
    FROM fv_ranked
    WHERE rn = 1
),
fv_etf_ranked AS (
    SELECT f.*,
           ROW_NUMBER() OVER (
               PARTITION BY f.symbol
               ORDER BY f.etl_datetime DESC, f.id DESC
           ) AS rn
    FROM finviz_etf_metrics f
    WHERE f.etl_datetime >= SYSDATE - (5 / 1440)
),
fv_etf AS (
    SELECT symbol, relative_volume, 
    average_volume, volume, gap, price, change as change_per
    FROM fv_etf_ranked
    WHERE rn = 1
)
SELECT 
       e.prk AS "PRK",
       e.rk AS "RK",
       w.week AS "Wk",
       w.etf AS "ETF",
       fv_etf.gap AS "ETF Gap",
       fv_etf.price AS "ETF Price",
       fv_etf.relative_volume AS "ETF Rel Vol",
       fv_etf.change_per AS "ETF Change",
       fv.change AS "Chng",
       fv.relative_volume AS "Rel Vol",
       e.ticker AS "Ticker",
       o.evr AS "EVR",
       fv.average_true_range AS "ATR",
       abs(GREATEST(fv.prev_close, fv.high)) - abs(LEAST(fv.prev_close, fv.low)) AS "T_atr",
       fv.gap AS "Gap",
       fv.change_from_open AS "chng frmOpn",
       fv.change AS "Chng_",
       fv.beta AS "Beta",
       fv.open AS "Open",
       fv.high AS "High",
       fv.price AS "Price",
       fv.low AS "Low",
       fv.short_float AS "Short Float",
       fv.short_ratio AS "Short Ratio",
       fv.market_cap_b*1000 AS "Market Cap (B)",
       fv.earnings_date AS "Earnings",
       sg.call_wall AS "Call Wall",
       fv.price AS "Pricee",
       sg.put_wall AS "Put Wall",
       sg.options_impact AS "Options Impact",
       sg.hedge_wall AS "Hedge Wall",
       sg.key_gamma_strike AS "Key Gamma Strike",
       sg.key_delta_strike AS "Key Delta Strike",
       sg.id AS "SG Id",
       fv.perf_1m AS "Perf 1m",
       fv.perf_2m AS "Perf 2m",
       fv.perf_3m AS "Perf 3m",
       fv.perf_5m AS "Perf 5m",
       fv.perf_10m AS "Perf 10m",
       fv.perf_15m AS "Perf 15m",
       fv.perf_1h AS "Perf 1h",
       fv.id AS "FV Id"
FROM earnings e
LEFT JOIN sg ON sg.symbol = e.ticker
LEFT JOIN fv ON fv.symbol = e.ticker
LEFT JOIN opt o ON o.symbol = e.ticker
LEFT JOIN wkly w ON w.symbol = e.ticker
LEFT JOIN fv_etf ON upper(fv_etf.symbol) = upper(w.etf)
where sg.call_wall IS NOT NULL
and e.rk = 2
ORDER BY fv.relative_volume DESC NULLS LAST