Gap between the 3:50 p.m. price and the official close, July 2026 sessions

SELECT
    d.sym AS ticker,
    round(quantileDeterministic(0.5)(abs(d.official_close / m.px_1550 - 1) * 10000,
                                     toUInt64(toUnixTimestamp(d.session_date))), 1) AS median_move_bps,
    round(max(abs(d.official_close / m.px_1550 - 1) * 10000), 1)                    AS largest_move_bps
FROM
(
    SELECT
        ticker                AS sym,
        date                  AS session_date,
        max(toFloat64(close)) AS official_close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('JNJ', 'JPM', 'KO', 'PG', 'WMT', 'XOM')
      AND date >= '2026-07-01'
      AND date <  '2026-08-01'
    GROUP BY sym, session_date
) AS d
INNER JOIN
(
    SELECT
        ticker                                               AS sym,
        toDate(toTimeZone(window_start, 'America/New_York')) AS session_date,
        max(toFloat64(close))                                AS px_1550
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('JNJ', 'JPM', 'KO', 'PG', 'WMT', 'XOM')
      AND window_start >= '2026-07-01'
      AND window_start <  '2026-08-01'
      AND toHour(toTimeZone(window_start, 'America/New_York'))   = 15
      AND toMinute(toTimeZone(window_start, 'America/New_York')) = 50
    GROUP BY sym, session_date
) AS m ON m.sym = d.sym AND m.session_date = d.session_date
GROUP BY d.sym
ORDER BY median_move_bps DESC