Zero DTE

WITH configured_symbols as (
    SELECT distinct symbol, etf from config where metric = '0DTE'
),
earnings as(
    select * from EARS
),
sg_latest_file AS (
    SELECT MAX(file_name) KEEP (DENSE_RANK LAST ORDER BY etl_datetime) AS file_name
    FROM spot_gamma_metrics
),
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 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
    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 f
    WHERE f.etl_datetime >= SYSDATE - (5 / 1440)
),
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
),
gx_ranked AS (
    SELECT g.*,
           ROW_NUMBER() OVER (
               PARTITION BY g.symbol
               ORDER BY g.etl_datetime DESC, g.id DESC
           ) AS rn
    FROM gexbot_metrics g
    WHERE g.etl_datetime >= SYSDATE - (5 / 1440)
),
gx AS (
    SELECT id, symbol, zero_gamma, positive_gamma, negative_gamma,
           net_gex_volume, major_call_gamma, major_put_gamma, major_long_gamma,
           major_short_gamma, regime, strike, color, etl_datetime
    FROM gx_ranked
    WHERE rn = 1
)
SELECT  
       e.PRK AS "PRK",
       e.RK AS "RK",
       fvetf.gap AS "ETF Gap",
       fvetf.price AS "ETF Price",
       fvetf.relative_volume AS "ETF Rel Vol",
       fvetf.change_per AS "ETF Change",
       fv.change AS "Chng_",
       fv.relative_volume AS "Reli Vol",
       fvetf.symbol AS "ETF Symbol",
       gx.color AS "colour",
       sg.options_impact AS "Option Impact",

       c.symbol AS "symbol",
       fv.relative_volume AS "Rel Vol",
       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.price AS "Pricee_",
       fv.beta AS "Beta",
       fv.open AS "Open",
       fv.prev_close AS "Prev Close",
       fv.high AS "High",
       fv.price AS "Price",
       fv.low AS "Low",

       gx.positive_gamma AS "+_gm",
       gx.zero_gamma AS "0_gm",
       fv.price AS "Pricee",
       gx.negative_gamma AS "-_gm",
       gx.net_gex_volume AS "net gex_vlm",
       case 
           when gx.net_gex_volume < 0 and fv.price < gx.zero_gamma then 1 
           when gx.net_gex_volume > 0 and fv.price > gx.zero_gamma then 1 
           else 0 
       end as "chkgma",
       gx.major_call_gamma AS "call_mlns",
       gx.major_long_gamma AS "major call_gm",
       fv.price AS "Price_",
       gx.major_short_gamma AS "major put_gm",
       gx.major_put_gamma AS "put_mlns",
       
       sg.call_wall AS "Call Wall",
       fv.price AS "Price__",
       sg.put_wall AS "Put Wall",
       sg.hedge_wall AS "Hedge Wall",
       sg.options_impact AS "Options Impact",
       sg.key_gamma_strike AS "Key Gamma Strike",
       sg.key_delta_strike AS "Key Delta Strike",
       fv.short_float AS "Short Float",
       fv.short_ratio AS "Short Ratio",
       fv.market_cap_b AS "Market Cap (B)",
       fv.earnings_date AS "Earnings",
       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",
       gx.regime AS "regime",
       gx.strike AS "strike",
       gx.color AS "color"
FROM configured_symbols c
LEFT JOIN earnings e ON e.ticker = c.symbol
LEFT JOIN fv_etf fvetf ON lower(trim(fvetf.symbol)) = lower(trim(c.etf))
LEFT JOIN sg ON sg.symbol = c.symbol
LEFT JOIN fv ON fv.symbol = c.symbol
LEFT JOIN gx ON gx.symbol = c.symbol
ORDER BY fv.relative_volume desc