2,170 answered market questions

WITH daily AS (
    SELECT ticker,
           toDate(toTimeZone(window_start, 'America/New_York')) AS session,
           argMin(close, window_start) AS first_price,
           argMax(close, window_start) AS last_price
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AAPL', 'MSFT', 'NVDA', 'WMT', 'COST', 'KO', 'JNJ')
      AND toDate(toTimeZone(window_start, 'America/New_York')) >= toDate('2024-07-01')
      AND toDate(toTimeZone(window_start, 'America/New_York')) <= toDate('2026-06-30')
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
    GROUP BY ticker, session
),
overnight AS (
    SELECT ticker,
           session,
           first_price,
           lagInFrame(last_price) OVER (PARTITION BY ticker ORDER BY session
                                        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prior_close
    FROM daily
),
moves AS (
    SELECT ticker,
           session,
           abs(toFloat64(first_price) / toFloat64(prior_close) - 1) * 100 AS abs_gap_pct
    FROM overnight
    WHERE toFloat64(prior_close) > 0
      AND abs(toFloat64(first_price) / toFloat64(prior_close) - 1) < 0.3
),
biggest AS (
    SELECT ticker,
           session,
           abs_gap_pct,
           row_number() OVER (PARTITION BY ticker ORDER BY abs_gap_pct DESC) AS rk
    FROM moves
),
spacing AS (
    SELECT ticker,
           session,
           abs_gap_pct,
           dateDiff('day',
                    lagInFrame(session) OVER (PARTITION BY ticker ORDER BY session
                                              ROWS BETWEEN 1 PRECEDING AND CURRENT ROW),
                    session) AS days_between
    FROM biggest
    WHERE rk <= 8
)
SELECT ticker,
       round(quantileDeterministic(0.5)(toFloat64(days_between), cityHash64(session)), 0) AS median_days_between,
       min(days_between) AS closest_pair_days,
       max(days_between) AS widest_pair_days,
       round(quantileDeterministic(0.5)(abs_gap_pct, cityHash64(session)), 2) AS median_move_pct
FROM spacing
WHERE days_between BETWEEN 1 AND 400
GROUP BY ticker
ORDER BY median_days_between DESC