2,170 answered market questions

WITH spy AS
(
    SELECT
        date,
        toFloat64(close) AS close_px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND date >= '2016-01-01'
      AND date <  '2026-07-01'
)
SELECT
    toString(toYear(tue.date)) AS year,
    count()                    AS session_pair_count,
    round(quantileDeterministic(0.5)(abs(fri.close_px / tue.close_px - 1) * 100,
          toUInt64(toUnixTimestamp(tue.date))), 2) AS median_gap_move_pct,
    round(quantileDeterministic(0.9)(abs(fri.close_px / tue.close_px - 1) * 100,
          toUInt64(toUnixTimestamp(tue.date))), 2) AS p90_gap_move_pct
FROM spy AS tue
INNER JOIN spy AS fri ON fri.date = tue.date + 3
WHERE toDayOfWeek(tue.date) = 2
GROUP BY year
ORDER BY year