2,173 answered market questions

WITH sessions AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
           argMin(toFloat64(open), window_start) AS session_open,
           argMax(toFloat64(close), window_start) AS session_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'TSLA', 'KO', 'SPY')
      AND window_start >= toDateTime('2024-08-01 00:00:00')
      AND window_start < toDateTime('2026-08-01 00:00:00')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session_date
),
linked AS (
    SELECT ticker,
           session_date,
           session_open,
           any(session_close) OVER (PARTITION BY ticker ORDER BY session_date
                                    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_close,
           any(session_date) OVER (PARTITION BY ticker ORDER BY session_date
                                   ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_date
    FROM sessions
),
gaps AS (
    SELECT ticker,
           session_date,
           abs(session_open / prev_close - 1) * 100 AS gap_pct
    FROM linked
    WHERE prev_close > 0
      AND dateDiff('day', prev_date, session_date) >= 3
)
SELECT ticker,
       count() AS gap_count,
       round(quantileDeterministic(0.5)(gap_pct, cityHash64(session_date)), 2) AS median_weekend_gap_pct,
       round(quantileDeterministic(0.95)(gap_pct, cityHash64(session_date)), 2) AS p95_weekend_gap_pct,
       round(max(gap_pct), 2) AS largest_weekend_gap_pct
FROM gaps
GROUP BY ticker
ORDER BY p95_weekend_gap_pct DESC