US sessions each year that land an hour earlier in Germany

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(toYear(session_date))     AS year,
    countIf(gap_hours = 5)             AS mismatch_sessions,
    anyIf(german_open, gap_hours = 5)  AS mismatch_open_german,
    anyIf(german_close, gap_hours = 5) AS mismatch_close_german,
    anyIf(german_open, gap_hours = 6)  AS aligned_open_german,
    anyIf(german_close, gap_hours = 6) AS aligned_close_german
FROM
(
    SELECT
        date AS session_date,
        formatDateTime(toTimeZone(toDateTime(concat(toString(date), ' 09:30:00'), 'America/New_York'), 'Europe/Berlin'), '%H:%M') AS german_open,
        formatDateTime(toTimeZone(toDateTime(concat(toString(date), ' 16:00:00'), 'America/New_York'), 'Europe/Berlin'), '%H:%M') AS german_close,
        toHour(toTimeZone(toDateTime(concat(toString(date), ' 09:30:00'), 'America/New_York'), 'Europe/Berlin')) - 9            AS gap_hours
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= toStartOfYear(today() - INTERVAL 5 YEAR)
      AND date <  toStartOfYear(today())
)
GROUP BY year
ORDER BY year