2,170 answered market questions

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMax(toFloat64(close), window_start) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
      AND window_start >= toDateTime('2003-01-01 00:00:00') AND window_start < toDateTime('2026-01-01 00:00:00')
    GROUP BY et_date
),
changes AS (
    SELECT et_date, close_usd,
           lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM daily
),
ranked AS (
    SELECT et_date, close_usd,
           round((close_usd / prev_close - 1) * 100, 1) AS change_pct,
           row_number() OVER (ORDER BY close_usd / prev_close ASC, et_date ASC) AS day_rank
    FROM changes
    WHERE prev_close > 0
),
enriched AS (
    SELECT et_date, change_pct, day_rank,
           max(if(et_date = toDate('2011-08-08'), day_rank, 0)) OVER () AS downgrade_rank,
           sum(if(day_rank <= 10 AND toYear(et_date) NOT IN (2008, 2020), 1, 0)) OVER () AS top10_outside_crises,
           round((max(if(et_date = toDate('2011-08-05'), close_usd, 0)) OVER () / max(if(et_date = toDate('2011-07-22'), close_usd, 0)) OVER () - 1) * 100, 1) AS two_week_slide_pct
    FROM ranked
)
SELECT
    toString(et_date) AS session,
    change_pct,
    downgrade_rank,
    top10_outside_crises,
    two_week_slide_pct
FROM enriched
WHERE day_rank <= 10
ORDER BY day_rank