NVDA's largest gap-down opens and the 30 minutes that followed

WITH sessions AS
(
    SELECT
        date,
        toFloat64(open)  AS open_px,
        toFloat64(close) AS close_px,
        row_number() OVER (ORDER BY date)     AS session_n,
        row_number() OVER (ORDER BY date) + 1 AS next_session_n
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'NVDA'
      AND date >= '2021-08-01'
      AND date <  '2026-08-01'
),
gap_days AS
(
    SELECT
        cur.date                                           AS d,
        round(100 * (cur.open_px / prior.close_px - 1), 2) AS gap_pct
    FROM sessions AS cur
    INNER JOIN sessions AS prior ON cur.session_n = prior.next_session_n
    WHERE cur.date NOT IN
    (
        SELECT execution_date
        FROM global_markets.stocks_splits
        WHERE ticker = 'NVDA'
    )
    ORDER BY gap_pct ASC
    LIMIT 8
),
opening_30m AS
(
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS d,
        argMin(toFloat64(open), window_start)                AS first_print,
        min(toFloat64(low))                                  AS low_30m
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'NVDA'
      AND window_start >= '2021-08-01'
      AND window_start <  '2026-08-01'
      AND toDate(toTimeZone(window_start, 'America/New_York')) IN (SELECT d FROM gap_days)
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 600
    GROUP BY d
)
SELECT
    toString(g.d)                                     AS session_date,
    g.gap_pct                                         AS open_vs_prior_close_pct,
    round(100 * (o.low_30m / o.first_print - 1), 2)   AS first_30m_low_vs_open_pct
FROM gap_days AS g
INNER JOIN opening_30m AS o ON o.d = g.d
ORDER BY g.d