بیشترین رتبه IV در بازار سهام

WITH per_session AS (
    SELECT underlying_symbol AS u,
           date AS d,
           quantileExact(0.5)(implied_volatility) AS iv,
           sum(volume) AS vol
    FROM global_markets.options_greeks
    WHERE date >= (SELECT max(date) FROM global_markets.options_greeks) - 380
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
      AND underlying_symbol NOT IN ('SPCX','KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT','AMDL','NUAI','VXX','VIXY')
    GROUP BY u, d
    HAVING count() >= 10
),
ranked AS (
    SELECT u, d, iv, vol,
           row_number() OVER w AS rn,
           first_value(iv) OVER w AS iv_latest,
           first_value(d) OVER w AS d_latest
    FROM per_session
    WINDOW w AS (PARTITION BY u ORDER BY d DESC)
),
agg AS (
    SELECT u,
           any(iv_latest) AS iv_cur,
           any(d_latest) AS last_d,
           count() AS sessions,
           min(iv) AS iv_lo,
           max(iv) AS iv_hi,
           countIf(iv < iv_latest) AS below_now,
           sumIf(vol, rn <= 20) AS vol_20d
    FROM ranked
    WHERE rn <= 252
    GROUP BY u
)
SELECT u AS ticker,
       round(100 * iv_cur, 1) AS iv_now_pct,
       round(100 * (iv_cur - iv_lo) / (iv_hi - iv_lo), 1) AS iv_rank,
       round(100 * below_now / sessions, 1) AS iv_percentile,
       round(100 * iv_lo, 1) AS iv_52w_low_pct,
       round(100 * iv_hi, 1) AS iv_52w_high_pct,
       toString(last_d) AS session_date
FROM agg
WHERE sessions >= 200
  AND vol_20d >= 20000
  AND iv_hi > iv_lo
  AND last_d = (SELECT max(date) FROM global_markets.options_greeks)
ORDER BY iv_rank DESC, vol_20d DESC, ticker
LIMIT 15

WITH per_session AS (
    SELECT underlying_symbol AS u,
           date AS d,
           quantileExact(0.5)(implied_volatility) AS iv,
           sum(volume) AS vol
    FROM global_markets.options_greeks
    WHERE date >= (SELECT max(date) FROM global_markets.options_greeks) - 380
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
      AND underlying_symbol NOT IN ('SPCX','KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT','AMDL','NUAI','VXX','VIXY')
    GROUP BY u, d
    HAVING count() >= 10
),
ranked AS (
    SELECT u, d, iv, vol,
           row_number() OVER w AS rn,
           first_value(iv) OVER w AS iv_latest,
           first_value(d) OVER w AS d_latest
    FROM per_session
    WINDOW w AS (PARTITION BY u ORDER BY d DESC)
),
agg AS (
    SELECT u,
           any(iv_latest) AS iv_cur,
           any(d_latest) AS last_d,
           count() AS sessions,
           min(iv) AS iv_lo,
           max(iv) AS iv_hi,
           countIf(iv < iv_latest) AS below_now,
           sumIf(vol, rn <= 20) AS vol_20d
    FROM ranked
    WHERE rn <= 252
    GROUP BY u
),
board AS (
    SELECT u,
           round(100 * iv_cur, 1) AS iv_now_pct,
           round(100 * (iv_cur - iv_lo) / (iv_hi - iv_lo), 1) AS iv_rank,
           round(100 * below_now / sessions, 1) AS iv_percentile,
           round(100 * iv_hi, 1) AS iv_52w_high_pct
    FROM agg
    WHERE sessions >= 200
      AND vol_20d >= 20000
      AND iv_hi > iv_lo
      AND last_d = (SELECT max(date) FROM global_markets.options_greeks)
)
SELECT u AS ticker,
       iv_now_pct,
       iv_rank,
       iv_percentile,
       round(iv_percentile - iv_rank, 1) AS percentile_minus_rank,
       iv_52w_high_pct
FROM board
ORDER BY percentile_minus_rank DESC, iv_now_pct DESC, ticker
LIMIT 12

WITH per_session AS (
    SELECT underlying_symbol AS u,
           date AS d,
           quantileExact(0.5)(implied_volatility) AS iv,
           sum(volume) AS vol
    FROM global_markets.options_greeks
    WHERE date >= (SELECT max(date) FROM global_markets.options_greeks) - 380
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
      AND underlying_symbol NOT IN ('SPCX','KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT','AMDL','NUAI','VXX','VIXY')
    GROUP BY u, d
    HAVING count() >= 10
),
ranked AS (
    SELECT u, d, iv, vol,
           row_number() OVER w AS rn,
           first_value(iv) OVER w AS iv_latest,
           first_value(d) OVER w AS d_latest
    FROM per_session
    WINDOW w AS (PARTITION BY u ORDER BY d DESC)
),
agg AS (
    SELECT u,
           any(iv_latest) AS iv_cur,
           any(d_latest) AS last_d,
           count() AS sessions,
           min(iv) AS iv_lo,
           max(iv) AS iv_hi,
           sumIf(vol, rn <= 20) AS vol_20d
    FROM ranked
    WHERE rn <= 252
    GROUP BY u
),
leader AS (
    SELECT u, iv_lo, iv_hi
    FROM agg
    WHERE sessions >= 200
      AND vol_20d >= 20000
      AND iv_hi > iv_lo
      AND last_d = (SELECT max(date) FROM global_markets.options_greeks)
    ORDER BY round(100 * (iv_cur - iv_lo) / (iv_hi - iv_lo), 1) DESC, vol_20d DESC, u
    LIMIT 1
)
SELECT toString(toMonday(r.d)) AS week,
       round(100 * quantileExact(0.5)(r.iv), 1) AS iv_pct,
       round(100 * any(l.iv_hi), 1) AS iv_52w_high_pct,
       round(100 * any(l.iv_lo), 1) AS iv_52w_low_pct
FROM ranked AS r
INNER JOIN leader AS l ON r.u = l.u
WHERE r.rn <= 252
GROUP BY week
HAVING uniqExact(r.d) >= 3
    OR max(r.d) = (SELECT max(date) FROM global_markets.options_greeks)
ORDER BY week

WITH per_session AS (
    SELECT underlying_symbol AS u,
           date AS d,
           quantileExact(0.5)(implied_volatility) AS iv,
           sum(volume) AS vol
    FROM global_markets.options_greeks
    WHERE date >= (SELECT max(date) FROM global_markets.options_greeks) - 380
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
      AND underlying_symbol NOT IN ('SPCX','KORU','SOXL','SOXS','TQQQ','SQQQ','NVDL','NVDS','NVD','TSLL','TSLQ','TSLZ','SPXL','SPXS','UPRO','SPXU','LABU','LABD','FAS','FAZ','TNA','TZA','YINN','YANG','UDOW','SDOW','BOIL','KOLD','UCO','SCO','USD','SSO','SDS','QLD','QID','ERX','ERY','DRN','DRV','CURE','SOXY','MUU','SNXX','UVXY','SVXY','UVIX','SVIX','BULZ','WEBL','WEBS','DPST','DRIP','GUSH','AGQ','ZSL','BITX','ETHU','MSTX','MSTU','CONL','DUST','JNUG','JDST','NUGT','AMDL','NUAI','VXX','VIXY')
    GROUP BY u, d
    HAVING count() >= 10
),
ranked AS (
    SELECT u, d, iv, vol,
           row_number() OVER w AS rn,
           first_value(iv) OVER w AS iv_latest,
           first_value(d) OVER w AS d_latest
    FROM per_session
    WINDOW w AS (PARTITION BY u ORDER BY d DESC)
),
agg AS (
    SELECT u,
           any(iv_latest) AS iv_cur,
           any(d_latest) AS last_d,
           count() AS sessions,
           min(iv) AS iv_lo,
           max(iv) AS iv_hi,
           sumIf(vol, rn <= 20) AS vol_20d
    FROM ranked
    WHERE rn <= 252
    GROUP BY u
),
screened AS (
    SELECT least(intDiv(toUInt16(floor(100 * (iv_cur - iv_lo) / (iv_hi - iv_lo))), 10), 9) AS b,
           100 * iv_cur AS iv_now_pct
    FROM agg
    WHERE sessions >= 200
      AND vol_20d >= 20000
      AND iv_hi > iv_lo
      AND last_d = (SELECT max(date) FROM global_markets.options_greeks)
),
buckets AS (
    SELECT b,
           count() AS names,
           round(quantileExact(0.5)(iv_now_pct), 1) AS median_iv_pct,
           round(min(iv_now_pct), 1) AS lowest_iv_pct,
           round(max(iv_now_pct), 1) AS highest_iv_pct
    FROM screened
    GROUP BY b
)
SELECT concat(toString(b * 10), '-', toString(b * 10 + 10)) AS iv_rank_bucket,
       names,
       sum(names) OVER (ORDER BY b DESC) AS names_cumulative,
       median_iv_pct,
       round(median_iv_pct - first_value(median_iv_pct) OVER (ORDER BY b DESC), 1) AS median_gap_to_top_pct,
       lowest_iv_pct,
       highest_iv_pct
FROM buckets
ORDER BY b DESC

WITH per_session AS (
    SELECT underlying_symbol AS u,
           date AS d,
           quantileExact(0.5)(implied_volatility) AS iv
    FROM global_markets.options_greeks
    WHERE date >= (SELECT max(date) FROM global_markets.options_greeks) - 380
      AND underlying_symbol IN ('SPY','QQQ','AAPL','MSFT','AMZN','META','NVDA','AMD','TSLA','COIN','MSTR','PLTR')
      AND iv_converged AND implied_volatility BETWEEN 0.02 AND 5
      AND abs(strike_price / underlying_close - 1) <= 0.05
      AND expiration_date BETWEEN date + 20 AND date + 60
    GROUP BY u, d
    HAVING count() >= 10
),
ranked AS (
    SELECT u, d, iv,
           row_number() OVER w AS rn,
           first_value(iv) OVER w AS iv_latest,
           first_value(d) OVER w AS d_latest
    FROM per_session
    WINDOW w AS (PARTITION BY u ORDER BY d DESC)
),
agg AS (
    SELECT u,
           any(iv_latest) AS iv_cur,
           any(d_latest) AS last_d,
           count() AS sessions,
           min(iv) AS iv_lo,
           max(iv) AS iv_hi,
           countIf(iv < iv_latest) AS below_now
    FROM ranked
    WHERE rn <= 252
    GROUP BY u
)
SELECT u AS ticker,
       round(100 * iv_cur, 1) AS iv_now_pct,
       round(100 * (iv_cur - iv_lo) / (iv_hi - iv_lo), 1) AS iv_rank,
       round(100 * below_now / sessions, 1) AS iv_percentile,
       round(100 * iv_lo, 1) AS iv_52w_low_pct,
       round(100 * iv_hi, 1) AS iv_52w_high_pct,
       toString(last_d) AS session_date
FROM agg
WHERE sessions >= 200
  AND iv_hi > iv_lo
  AND last_d = (SELECT max(date) FROM global_markets.options_greeks)
ORDER BY iv_rank DESC, ticker