הורדת דירוג ארה"ב 2011: קלטת יום שני השחור

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('2011-08-05 00:00:00') AND window_start < toDateTime('2011-08-08 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('2011-08-08 04:00:00') AND window_start < toDateTime('2011-08-08 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('2011-08-08 04:00:00') AND window_start < toDateTime('2011-08-08 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
    ticker,
    round(fri_close, 2) AS fri_close_usd,
    round(mon_close, 2) AS mon_close_usd,
    round((mon_close / fri_close - 1) * 100, 1) AS change_pct
FROM (
    SELECT
        ticker,
        argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2011-08-05') AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS fri_close,
        argMaxIf(toFloat64(close), window_start, toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2011-08-08') AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959) AS mon_close
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('VXX', 'BAC', 'C', 'GS', 'XLF', 'SPY', 'KO', 'XLU', 'GLD')
      AND window_start >= toDateTime('2011-08-05 00:00:00') AND window_start < toDateTime('2011-08-09 04:00:00')
    GROUP BY ticker
)
ORDER BY mon_close / fri_close ASC, ticker ASC

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

SELECT
    toString(date) AS date,
    round(yield_3_month, 2) AS yield_3_month,
    round(yield_2_year, 2) AS yield_2_year,
    round(yield_10_year, 2) AS yield_10_year,
    round(yield_30_year, 2) AS yield_30_year
FROM global_markets.treasury_yields
WHERE date BETWEEN '2011-08-01' AND '2011-08-15'
ORDER BY date

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
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('2011-08-05 00:00:00') AND window_start < toDateTime('2011-08-12 23:59:00')
        GROUP BY et_date
    )
)
WHERE et_date >= toDate('2011-08-08') AND et_date <= toDate('2011-08-12')
ORDER BY et_date

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('2011-08-05 00:00:00') AND window_start < toDateTime('2012-07-01 00:00:00')
    GROUP BY et_date
),
(SELECT close_usd FROM daily WHERE et_date = toDate('2011-08-05')) AS pre_close,
(SELECT close_usd FROM daily WHERE et_date = toDate('2011-08-08')) AS mon_close,
(SELECT min(et_date) FROM daily WHERE et_date > toDate('2011-08-05') AND close_usd >= pre_close) AS reclaim_d
SELECT
    round(pre_close, 2) AS predowngrade_close,
    toString(reclaim_d) AS first_reclaim_date,
    countIf(et_date > toDate('2011-08-05') AND et_date < reclaim_d) AS sessions_before_first_reclaim,
    toString(argMinIf(et_date, (close_usd, et_date), et_date > toDate('2011-08-05'))) AS bottom_date,
    round(minIf(close_usd, et_date > toDate('2011-08-05')), 2) AS bottom_close,
    round((minIf(close_usd, et_date > toDate('2011-08-05')) / pre_close - 1) * 100, 1) AS bottom_vs_predowngrade_pct,
    round((minIf(close_usd, et_date > toDate('2011-08-05')) / mon_close - 1) * 100, 1) AS bottom_vs_downgrade_monday_pct,
    toString(maxIf(et_date, close_usd < pre_close)) AS last_close_below,
    countIf(et_date > toDate('2011-08-05') AND close_usd < pre_close) AS total_sessions_below
FROM daily