లెహ్‌మన్ కూల్చివేత: 2008 టేప్ పునరావృతం

WITH monthly AS (
    SELECT
        formatDateTime(month_start, '%Y-%m') AS month,
        round(argMax(day_close, et_date), 2) AS month_close
    FROM (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
            toStartOfMonth(toDate(toTimeZone(window_start, 'America/New_York'))) AS month_start,
            argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS day_close
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'LEH'
          AND window_start >= toDateTime('2007-01-01 00:00:00') AND window_start < toDateTime('2008-09-18 00:00:00')
        GROUP BY et_date, month_start
    )
    GROUP BY month_start
)
SELECT
    month,
    month_close,
    round((month_close / max(month_close) OVER () - 1) * 100, 1) AS vs_best_month_close_pct
FROM monthly
ORDER BY month

WITH
    (
        SELECT argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2008-09-12 00:00:00') AND window_start < toDateTime('2008-09-15 04:00:00')
    ) AS prior_rth_close
SELECT
    round(prior_rth_close, 2) AS prior_close,
    round(toFloat64(argMinIf(open, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)), 2) AS rth_open,
    round((toFloat64(argMinIf(open, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / prior_rth_close - 1) * 100, 1) AS gap_pct,
    round(minIf(toFloat64(low), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS rth_low,
    formatDateTime(toTimeZone(argMinIf(window_start, (toFloat64(low), toInt64(toUnixTimestamp(window_start))), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 'America/New_York'), '%H:%i') AS low_et,
    round(maxIf(toFloat64(high), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS rth_high,
    round(toFloat64(argMaxIf(close, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)), 2) AS rth_close,
    round((toFloat64(argMaxIf(close, window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959)) / prior_rth_close - 1) * 100, 1) AS day_change_pct,
    round((minIf(toFloat64(low), (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) / prior_rth_close - 1) * 100, 1) AS low_vs_prior_pct,
    round(toFloat64(sum(volume)) / 1e6, 1) AS day_shares_m,
    countIf((toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS rth_minute_bars
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY'
  AND window_start >= toDateTime('2008-09-15 04:00:00') AND window_start < toDateTime('2008-09-15 23:59:00')

SELECT
    formatDateTime(toStartOfInterval(toTimeZone(window_start, 'America/New_York'), INTERVAL 30 MINUTE), '%H:%i') AS et_time,
    round(toFloat64(argMax(close, window_start)), 2) AS bucket_close,
    round(min(toFloat64(low)), 2) AS bucket_low,
    round(toFloat64(sum(volume)) / 1e6, 1) AS shares_m
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker = 'SPY'
  AND window_start >= toDateTime('2008-09-15 04:00:00') AND window_start < toDateTime('2008-09-15 23:59:00')
  AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959
GROUP BY et_time
ORDER BY et_time

SELECT
    toString(et_date) AS session,
    close_usd,
    round(if(prev_close = 0, NULL, (close_usd / prev_close - 1) * 100), 1) AS change_pct,
    shares_m,
    tbill_3m_pct,
    y10_pct
FROM (
    SELECT et_date, close_usd, shares_m,
           lagInFrame(close_usd) OVER (ORDER BY et_date ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_close
    FROM (
        SELECT
            toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
            round(argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959), 2) AS close_usd,
            round(toFloat64(sum(volume)) / 1e6, 1) AS shares_m
        FROM global_markets.delayed_stocks_minute_aggs
        WHERE ticker = 'SPY'
          AND window_start >= toDateTime('2008-09-12 00:00:00') AND window_start < toDateTime('2008-09-19 23:59:00')
        GROUP BY et_date
    )
) s
LEFT JOIN (
    SELECT date, round(toFloat64(yield_3_month), 2) AS tbill_3m_pct, round(toFloat64(yield_10_year), 2) AS y10_pct
    FROM global_markets.treasury_yields
    WHERE date BETWEEN '2008-09-15' AND '2008-09-19'
) t ON s.et_date = t.date
WHERE et_date >= toDate('2008-09-15') AND et_date <= toDate('2008-09-19')
ORDER BY et_date

WITH daily AS (
    SELECT
        ticker,
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('AIG', 'MS', 'GS', 'WM', 'C', 'SPY')
      AND window_start >= toDateTime('2008-09-12 00:00:00') AND window_start < toDateTime('2008-09-20 00:00:00')
    GROUP BY ticker, et_date
)
SELECT
    ticker,
    round(maxIf(close_usd, et_date = toDate('2008-09-12')), 2) AS fri_close,
    round(maxIf(close_usd, et_date = toDate('2008-09-15')), 2) AS mon_close,
    round((maxIf(close_usd, et_date = toDate('2008-09-15')) / maxIf(close_usd, et_date = toDate('2008-09-12')) - 1) * 100, 1) AS mon_pct,
    round(minIf(close_usd, et_date >= toDate('2008-09-15')), 2) AS week_low_close,
    round((minIf(close_usd, et_date >= toDate('2008-09-15')) / maxIf(close_usd, et_date = toDate('2008-09-12')) - 1) * 100, 1) AS low_close_vs_fri_pct,
    round(maxIf(close_usd, et_date = toDate('2008-09-19')), 2) AS fri19_close,
    round((maxIf(close_usd, et_date = toDate('2008-09-19')) / maxIf(close_usd, et_date = toDate('2008-09-12')) - 1) * 100, 1) AS week_pct
FROM daily
GROUP BY ticker
ORDER BY low_close_vs_fri_pct ASC

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2003-09-10 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
        row_number() OVER (ORDER BY (close_usd / prev_close - 1) ASC, et_date ASC) AS rank,
        toString(et_date) AS session,
        round((close_usd / prev_close - 1) * 100, 1) AS change_pct
    FROM changes
    WHERE prev_close > 0 AND isFinite(close_usd / prev_close)
),
(SELECT any(rank) FROM ranked WHERE session = '2008-09-29') AS tarp_rank_s,
(SELECT countIf(rank <= 10 AND session BETWEEN '2008-09-16' AND '2008-12-31') FROM ranked) AS post_lehman_s
SELECT
    session,
    rank,
    change_pct,
    tarp_rank_s AS tarp_vote_rank,
    post_lehman_s AS post_lehman_2008_top10
FROM ranked
WHERE rank <= 10 OR session = '2008-09-15'
ORDER BY rank

WITH daily AS (
    SELECT
        toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
        argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS close_usd
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2007-01-01 00:00:00') AND window_start < toDateTime('2014-01-01 00:00:00')
    GROUP BY et_date
),
(SELECT argMax(et_date, (close_usd, et_date)) FROM daily WHERE et_date < toDate('2008-09-15')) AS peak_d,
(SELECT argMin(et_date, (close_usd, et_date)) FROM daily WHERE et_date < toDate('2010-01-01')) AS trough_d,
(SELECT max(close_usd) FROM daily WHERE et_date = toDate('2008-09-12')) AS lehman_eve_close,
(SELECT min(et_date) FROM daily WHERE et_date > toDate('2008-09-15') AND close_usd >= lehman_eve_close) AS reclaim_d
SELECT
    toString(peak_d) AS peak_date,
    round(maxIf(close_usd, et_date = peak_d), 2) AS peak_close,
    toString(trough_d) AS trough_date,
    round(maxIf(close_usd, et_date = trough_d), 2) AS trough_close,
    round((maxIf(close_usd, et_date = trough_d) / maxIf(close_usd, et_date = peak_d) - 1) * 100, 1) AS peak_to_trough_pct,
    countIf(et_date > peak_d AND et_date <= trough_d) AS sessions_peak_to_trough,
    round(maxIf(close_usd, et_date = toDate('2008-09-15')), 2) AS lehman_monday_close,
    round((maxIf(close_usd, et_date = toDate('2008-09-15')) / maxIf(close_usd, et_date = peak_d) - 1) * 100, 1) AS peak_to_lehman_pct,
    round((maxIf(close_usd, et_date = trough_d) / maxIf(close_usd, et_date = toDate('2008-09-15')) - 1) * 100, 1) AS trough_vs_lehman_pct,
    toString(reclaim_d) AS lehman_eve_reclaim_date,
    countIf(et_date > toDate('2008-09-15') AND et_date < reclaim_d) AS sessions_until_reclaim
FROM daily