3,214 answered market questions

WITH
    daily AS
    (
        SELECT
            date,
            any(toFloat64(open))  AS session_open,
            any(toFloat64(close)) AS session_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2016-01-01'
          AND date <  '2026-10-01'
        GROUP BY date
    ),
    gapped AS
    (
        SELECT
            date,
            session_open,
            lagInFrame(session_close) OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prior_close
        FROM daily
    )
SELECT
    formatDateTime(date, '%W')                          AS weekday,
    round(quantileDeterministic(0.5)(gap_pct, det), 3)  AS median_gap_pct,
    round(quantileDeterministic(0.95)(gap_pct, det), 3) AS p95_gap_pct
FROM
(
    SELECT
        date,
        toYYYYMMDD(date)                          AS det,
        abs(session_open / prior_close - 1) * 100  AS gap_pct
    FROM gapped
    WHERE prior_close > 0
)
GROUP BY weekday
ORDER BY median_gap_pct DESC