52-week high और low वाले स्टॉक्स

WITH universe AS (
    SELECT ticker
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
      AND ticker IN (SELECT arrayJoin(tickers) FROM global_markets.stocks_income_statements
                     WHERE period_end >= today() - 400)
      AND ticker 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')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 460 AND today())
    GROUP BY ticker
    HAVING sum(toFloat64(close) * toFloat64(volume)) >= 2000000000
),
last_session AS (
    SELECT max(toDate(et)) AS d
    FROM (
        SELECT toTimeZone(window_start, 'America/New_York') AS et
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= now() - INTERVAL 12 DAY
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 955
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
),
daily AS (
    SELECT ticker, toDate(et) AS dt, argMax(c, et) AS c, count() AS nbars
    FROM (
        SELECT ticker, toTimeZone(window_start, 'America/New_York') AS et, toFloat64(close) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 375 DAY
          AND ticker IN (SELECT ticker FROM universe)
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 930
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
    GROUP BY ticker, dt
    HAVING nbars >= 20 AND dt > (SELECT d FROM last_session) - 365
),
ranged AS (
    SELECT ticker,
           argMax(c, dt) AS last_close,
           max(c) AS hi,
           min(c) AS lo,
           argMaxIf(c, dt, dt <= toDate('2025-12-31')) AS base_close,
           count() AS n_sessions,
           min(dt) AS first_dt,
           max(dt) AS last_dt
    FROM daily
    GROUP BY ticker
    HAVING n_sessions >= 200
       AND countIf(dt <= toDate('2025-12-31')) > 0
       AND last_dt = (SELECT d FROM last_session)
       AND first_dt <= (SELECT d FROM last_session) - 350
)
SELECT ticker,
       round(last_close, 2) AS close,
       round((last_close / base_close - 1) * 100, 1) AS ytd_return_pct
FROM ranged
WHERE last_close >= hi * 0.99
ORDER BY ytd_return_pct DESC, ticker ASC
LIMIT 12

WITH universe AS (
    SELECT ticker
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
      AND ticker IN (SELECT arrayJoin(tickers) FROM global_markets.stocks_income_statements
                     WHERE period_end >= today() - 400)
      AND ticker 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')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 460 AND today())
    GROUP BY ticker
    HAVING sum(toFloat64(close) * toFloat64(volume)) >= 2000000000
),
last_session AS (
    SELECT max(toDate(et)) AS d
    FROM (
        SELECT toTimeZone(window_start, 'America/New_York') AS et
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= now() - INTERVAL 12 DAY
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 955
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
),
daily AS (
    SELECT ticker, toDate(et) AS dt, argMax(c, et) AS c, count() AS nbars
    FROM (
        SELECT ticker, toTimeZone(window_start, 'America/New_York') AS et, toFloat64(close) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 375 DAY
          AND ticker IN (SELECT ticker FROM universe)
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 930
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
    GROUP BY ticker, dt
    HAVING nbars >= 20 AND dt > (SELECT d FROM last_session) - 365
),
ranged AS (
    SELECT ticker,
           argMax(c, dt) AS last_close,
           max(c) AS hi,
           min(c) AS lo,
           argMaxIf(c, dt, dt <= toDate('2025-12-31')) AS base_close,
           count() AS n_sessions,
           min(dt) AS first_dt,
           max(dt) AS last_dt
    FROM daily
    GROUP BY ticker
    HAVING n_sessions >= 200
       AND countIf(dt <= toDate('2025-12-31')) > 0
       AND last_dt = (SELECT d FROM last_session)
       AND first_dt <= (SELECT d FROM last_session) - 350
)
SELECT ticker,
       round(last_close, 2) AS close,
       round((last_close / base_close - 1) * 100, 1) AS ytd_return_pct
FROM ranged
WHERE last_close <= lo * 1.01
ORDER BY ytd_return_pct ASC, ticker ASC
LIMIT 12

WITH universe AS (
    SELECT ticker
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
      AND ticker IN (SELECT arrayJoin(tickers) FROM global_markets.stocks_income_statements
                     WHERE period_end >= today() - 400)
      AND ticker 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')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 460 AND today())
    GROUP BY ticker
    HAVING sum(toFloat64(close) * toFloat64(volume)) >= 2000000000
),
last_session AS (
    SELECT max(toDate(et)) AS d
    FROM (
        SELECT toTimeZone(window_start, 'America/New_York') AS et
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= now() - INTERVAL 12 DAY
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 955
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
),
daily AS (
    SELECT ticker, toDate(et) AS dt, argMax(c, et) AS c, count() AS nbars
    FROM (
        SELECT ticker, toTimeZone(window_start, 'America/New_York') AS et, toFloat64(close) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 425 DAY
          AND ticker IN (SELECT ticker FROM universe)
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 930
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
    GROUP BY ticker, dt
    HAVING nbars >= 20 AND dt <= (SELECT d FROM last_session)
),
rolled AS (
    SELECT ticker, dt, c,
           max(c) OVER w AS hi,
           min(c) OVER w AS lo,
           count() OVER w AS n_sessions,
           min(dt) OVER (PARTITION BY ticker) AS first_dt
    FROM daily
    WINDOW w AS (PARTITION BY ticker ORDER BY dt RANGE BETWEEN 364 PRECEDING AND CURRENT ROW)
)
SELECT formatDateTime(dt, '%b %e, %Y') AS date,
       countIf(c >= hi * 0.99) AS at_52w_high,
       countIf(c <= lo * 1.01) AS at_52w_low,
       countIf(c >= hi * 0.99) - countIf(c <= lo * 1.01) AS net_highs
FROM rolled
WHERE n_sessions >= 200
  AND first_dt <= dt - 350
  AND dt > (SELECT d FROM last_session) - 43
GROUP BY dt
ORDER BY dt

WITH universe AS (
    SELECT ticker
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE window_start >= now() - INTERVAL 20 DAY
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 570
      AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
      AND ticker IN (SELECT arrayJoin(tickers) FROM global_markets.stocks_income_statements
                     WHERE period_end >= today() - 400)
      AND ticker 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')
      AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                         WHERE execution_date BETWEEN today() - 460 AND today())
    GROUP BY ticker
    HAVING sum(toFloat64(close) * toFloat64(volume)) >= 2000000000
),
last_session AS (
    SELECT max(toDate(et)) AS d
    FROM (
        SELECT toTimeZone(window_start, 'America/New_York') AS et
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= now() - INTERVAL 12 DAY
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 955
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
),
daily AS (
    SELECT ticker, toDate(et) AS dt, argMax(c, et) AS c, count() AS nbars
    FROM (
        SELECT ticker, toTimeZone(window_start, 'America/New_York') AS et, toFloat64(close) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 375 DAY
          AND ticker IN (SELECT ticker FROM universe)
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 930
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
    GROUP BY ticker, dt
    HAVING nbars >= 20 AND dt > (SELECT d FROM last_session) - 365
),
ranged AS (
    SELECT ticker,
           argMax(c, dt) AS last_close,
           max(c) AS hi,
           min(c) AS lo,
           argMaxIf(c, dt, dt <= toDate('2025-12-31')) AS base_close,
           count() AS n_sessions,
           min(dt) AS first_dt,
           max(dt) AS last_dt
    FROM daily
    GROUP BY ticker
    HAVING n_sessions >= 200
       AND countIf(dt <= toDate('2025-12-31')) > 0
       AND last_dt = (SELECT d FROM last_session)
       AND first_dt <= (SELECT d FROM last_session) - 350
)
SELECT multiIf(p < 1, 'At the high (under 1%)',
               p < 5, '1% to 5% below',
               p < 10, '5% to 10% below',
               p < 20, '10% to 20% below',
               p < 35, '20% to 35% below',
               'More than 35% below') AS bucket,
       count() AS stocks,
       round(100.0 * count() / sum(count()) OVER (), 1) AS share_pct,
       round(quantileExact(0.5)(ytd), 1) AS median_ytd_pct
FROM (
    SELECT (1 - last_close / hi) * 100 AS p,
           (last_close / base_close - 1) * 100 AS ytd,
           multiIf(p < 1, 0, p < 5, 1, p < 10, 2, p < 20, 3, p < 35, 4, 5) AS ord
    FROM ranged
)
GROUP BY bucket, ord
ORDER BY ord

WITH last_session AS (
    SELECT max(toDate(et)) AS d
    FROM (
        SELECT toTimeZone(window_start, 'America/New_York') AS et
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY' AND window_start >= now() - INTERVAL 12 DAY
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 955
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
),
daily AS (
    SELECT ticker, toDate(et) AS dt, argMax(c, et) AS c, count() AS nbars
    FROM (
        SELECT ticker, toTimeZone(window_start, 'America/New_York') AS et, toFloat64(close) AS c
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 375 DAY
          AND ticker IN ('SPY', 'QQQ', 'DIA', 'IWM', 'RSP')
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) >= 930
          AND toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York')) < 960
    )
    GROUP BY ticker, dt
    HAVING nbars >= 20
       AND dt > (SELECT d FROM last_session) - 365
       AND dt <= (SELECT d FROM last_session)
),
ranged AS (
    SELECT ticker,
           argMax(c, dt) AS last_close,
           max(c) AS hi,
           min(c) AS lo,
           argMaxIf(c, dt, dt <= toDate('2025-12-31')) AS base_close,
           count() AS n_sessions,
           min(dt) AS first_dt,
           max(dt) AS last_dt
    FROM daily
    GROUP BY ticker
    HAVING n_sessions >= 200
       AND countIf(dt <= toDate('2025-12-31')) > 0
       AND last_dt = (SELECT d FROM last_session)
       AND first_dt <= (SELECT d FROM last_session) - 350
)
SELECT multiIf(ticker = 'SPY', 'S&P 500 (SPY)',
               ticker = 'QQQ', 'Nasdaq 100 (QQQ)',
               ticker = 'DIA', 'Dow 30 (DIA)',
               ticker = 'RSP', 'S&P 500 equal weight (RSP)',
               'Russell 2000 (IWM)') AS label,
       round((1 - last_close / hi) * 100, 2) AS pct_below_high,
       round((last_close / lo - 1) * 100, 1) AS pct_above_low,
       round((last_close / base_close - 1) * 100, 1) AS ytd_return_pct
FROM ranged
ORDER BY pct_below_high ASC