New 52-week highs against new lows, daily, over the past six weeks

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

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(date) AS d FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY' AND date >= today() - 12 AND date < today()
),
daily AS (
    SELECT ticker, date AS dt, toFloat64(close) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE date < today()
      AND ticker IN (SELECT ticker FROM universe)
      AND date <= (SELECT d FROM last_session)
      AND date >= (SELECT d FROM last_session) - 425
),
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