Calendar days spanned by each rolling five-session window

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
    sessions AS
    (
        SELECT DISTINCT toDate(toTimeZone(window_start, 'America/New_York')) AS d
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= today() - 120
          AND window_start <  today() - 2
          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
    ),
    spans AS
    (
        SELECT
            d,
            dateDiff('day', min(d) OVER (ORDER BY d ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW), d) + 1 AS span_days,
            count()   OVER (ORDER BY d ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)                    AS sessions_in_window
        FROM sessions
    )
SELECT
    toString(d)                              AS session_date,
    toUInt16(span_days)                      AS calendar_days_spanned,
    toUInt16(max(span_days) OVER ())         AS longest_span_days
FROM spans
WHERE sessions_in_window = 5
ORDER BY d ASC