3,256 answered market questions

SELECT
    ticker,
    round(countIf(overnight_move_pct <= -2) * 100.0 / count(), 2) AS gap_down_2pct_rate_pct,
    round(abs(min(overnight_move_pct)), 2)                        AS worst_gap_down_pct,
    countIf(overnight_move_pct <= -2)                             AS gap_down_day_count,
    count()                                                       AS session_count
FROM
(
    SELECT
        ticker,
        round((toFloat64(open) / toFloat64(prev_close) - 1) * 100, 3) AS overnight_move_pct
    FROM
    (
        SELECT
            ticker,
            date,
            open,
            any(close) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_close
        FROM
        (
            SELECT
                ticker,
                date,
                any(open)  AS open,
                any(close) AS close
            FROM global_markets.stocks_daily_aggs
            WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'SPY', 'KO', 'JNJ')
              AND date >= '2016-10-01'
              AND date <= '2026-09-30'
              AND (ticker, date) NOT IN (SELECT ticker, execution_date FROM global_markets.stocks_splits)
            GROUP BY ticker, date
        )
    )
    WHERE prev_close > 0
)
GROUP BY ticker
ORDER BY gap_down_2pct_rate_pct DESC