Overnight gap versus the session that follows, complete years

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

SELECT
    year,
    round(avg(overnight_pct), 3) AS overnight_move_pct,
    round(avg(intraday_pct), 3)  AS intraday_move_pct
FROM
(
    SELECT
        toYear(date)                             AS year,
        abs(open_px / prior_close - 1) * 100     AS overnight_pct,
        abs(close_px / open_px - 1) * 100        AS intraday_pct
    FROM
    (
        SELECT
            date,
            max(toFloat64(open))  AS open_px,
            max(toFloat64(close)) AS close_px,
            lagInFrame(max(toFloat64(close)))
                OVER (ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2021-01-01'
          AND date <  toStartOfYear(today())
        GROUP BY date
    )
    WHERE prior_close > 0 AND open_px > 0
)
GROUP BY year
ORDER BY year