The window and the screened universe behind every board on this page

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 window_days AS (
    SELECT d
    FROM (
        SELECT d, max(d) OVER () AS last_full
        FROM (
            SELECT d
            FROM (
                SELECT d, bars, medianExact(bars) OVER () AS typical_bars
                FROM (
                    SELECT toDate(toTimeZone(window_start, 'America/New_York')) AS d, count() AS bars
                    FROM global_markets.delayed_stocks_minute_aggs
                    WHERE window_start >= now() - INTERVAL 34 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
                    GROUP BY d
                    HAVING d < toDate(toTimeZone(now(), 'America/New_York'))
                )
            )
            WHERE bars >= 0.85 * typical_bars
        )
    )
    WHERE d > last_full - 30
),
screened AS (
    SELECT ticker
    FROM (
        SELECT ticker,
               toDate(toTimeZone(window_start, 'America/New_York')) AS d,
               argMin(toFloat64(open), window_start) AS o,
               sum(toFloat64(close) * toFloat64(volume)) AS dollars
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE window_start >= now() - INTERVAL 34 DAY
          AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM window_days)
          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 NOT IN ('SPCX')
          AND ticker NOT IN ('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 IN (SELECT ticker FROM global_markets.stocks_ratios
                         WHERE market_cap > 0)
          AND ticker NOT IN (SELECT ticker FROM global_markets.stocks_splits
                             WHERE execution_date BETWEEN today() - 45 AND today())
        GROUP BY ticker, d
    )
    GROUP BY ticker
    HAVING sum(dollars) >= 1000000000 AND argMin(o, d) >= 10 AND count() >= 15
)
SELECT formatDateTime(w.first_d, '%b %e') AS first_session_date,
       formatDateTime(w.last_d, '%b %e') AS last_session_date,
       w.n AS sessions,
       (SELECT count() FROM global_markets.stocks_ratios WHERE market_cap > 0) AS companies_on_file,
       (SELECT count() FROM screened) AS companies_screened
FROM (SELECT min(d) AS first_d, max(d) AS last_d, count() AS n FROM window_days) AS w