2,173 answered market questions

WITH daily AS
(
    SELECT
        date,
        toFloat64(open)  AS open_px,
        toFloat64(close) AS close_px,
        lagInFrame(toFloat64(close)) OVER (ORDER BY date
            ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SONY'
      AND date >= '2016-01-01'
      AND date <  '2026-08-01'
)
SELECT
    toString(toYear(date)) AS year,
    round(quantileDeterministic(0.5)(abs(open_px / prior_close_px - 1) * 100, toUInt32(date)), 3) AS overnight_gap_pct,
    round(quantileDeterministic(0.5)(abs(close_px / open_px - 1) * 100, toUInt32(date)), 3)       AS open_to_close_pct
FROM daily
WHERE prior_close_px > 0
  AND open_px > 0
GROUP BY year
ORDER BY year