gap_by_week

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.

SELECT
    toString(toStartOfWeek(d, 1))     AS week_start,
    min(open_hour_israel) - 9         AS ny_gap_hrs,
    countIf(open_hour_israel = 15)    AS openings_at_1530
FROM
(
    SELECT
        d,
        toHour(toTimeZone(toDateTime(concat(toString(d), ' 09:30:00'), 'America/New_York'), 'Asia/Jerusalem')) AS open_hour_israel
    FROM
    (
        SELECT date AS d
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= toDate('2026-01-01')
          AND date <= toDate('2026-12-31')
        GROUP BY date
        UNION ALL
        SELECT d
        FROM (SELECT toDate('2026-01-01') + arrayJoin(range(365)) AS d)
        WHERE d > (SELECT max(date) FROM global_markets.stocks_daily_aggs WHERE ticker = 'SPY')
          AND d <= toDate('2026-12-31')
          AND toDayOfWeek(d) BETWEEN 1 AND 5
          AND d NOT IN (SELECT date FROM global_markets.stocks_market_holidays WHERE status = 'closed')
    )
)
GROUP BY week_start
ORDER BY week_start