2020 మార్చి 24: మార్కెట్ మలుపు తిరిగిన రోజు

WITH daily AS (
    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
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker = 'SPY'
      AND window_start >= toDateTime('2020-02-19 00:00:00') AND window_start < toDateTime('2020-03-24 04:00:00')
    GROUP BY et_date
    HAVING et_date >= toDate('2020-02-19') AND et_date <= toDate('2020-03-23') AND close_usd > 0
)
SELECT
    toString(d.et_date) AS session,
    d.close_usd,
    round((d.close_usd / (SELECT max(close_usd) FROM daily) - 1) * 100, 1) AS drawdown_pct,
    round(t.yield_10_year * 100) AS y10_bps
FROM daily AS d
LEFT JOIN global_markets.treasury_yields AS t ON t.date = d.et_date
ORDER BY d.et_date

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('2020-03-23 00:00:00') AND window_start < toDateTime('2020-03-24 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('2020-03-24 04:00:00') AND window_start < toDateTime('2020-03-24 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('2020-03-24 04:00:00') AND window_start < toDateTime('2020-03-24 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(argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959 AND toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2020-03-23')), 2) AS mar23_close,
    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 AND toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2020-03-24')), 2) AS mar24_close,
    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 AND toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2020-03-24')) / argMaxIf(toFloat64(close), window_start, (toHour(toTimeZone(window_start, 'America/New_York')) * 60 + toMinute(toTimeZone(window_start, 'America/New_York'))) BETWEEN 570 AND 959 AND toDate(toTimeZone(window_start, 'America/New_York')) = toDate('2020-03-23')) - 1) * 100, 1) AS change_pct
FROM global_markets.delayed_stocks_minute_aggs
WHERE ticker IN ('SPY', 'DIA', 'QQQ', 'IWM', 'XLE', 'XLI', 'XLF', 'XLK', 'XLY', 'XLV', 'XLU', 'XLP')
  AND window_start >= toDateTime('2020-03-23 00:00:00') AND window_start < toDateTime('2020-03-25 00:00:00')
GROUP BY ticker
ORDER BY change_pct DESC

SELECT
    countIf(isFinite(day_pct)) AS sessions_measured,
    toString(min(et_date)) AS first_session,
    round(mar24_d, 1) AS mar24_gain_pct,
    countIf(isFinite(day_pct) AND day_pct > mar24_d) AS bigger_days,
    countIf(isFinite(day_pct) AND day_pct > mar24_d) + 1 AS mar24_rank,
    arrayStringConcat(arraySort(groupArrayIf(toString(et_date), isFinite(day_pct) AND day_pct > mar24_d)), ', ') AS bigger_day_dates,
    round(mar26_b, 1) AS burst3_gain_pct,
    countIf(isFinite(burst3_pct) AND burst3_pct > mar26_b) AS bigger_bursts,
    round(mar24_r30, 2) AS vol_vs_30d,
    round(mar24_r250, 2) AS vol_vs_250d,
    round(mar24_shares / 1e6, 0) AS mar24_shares_m
FROM (
    SELECT
        et_date, day_pct, burst3_pct,
        max(if(et_date = toDate('2020-03-24'), day_pct, -999)) OVER () AS mar24_d,
        max(if(et_date = toDate('2020-03-26'), burst3_pct, -999)) OVER () AS mar26_b,
        max(if(et_date = toDate('2020-03-24'), shares / vol_avg_30, -999)) OVER () AS mar24_r30,
        max(if(et_date = toDate('2020-03-24'), shares / vol_avg_250, -999)) OVER () AS mar24_r250,
        max(if(et_date = toDate('2020-03-24'), shares, -999)) OVER () AS mar24_shares
    FROM (
        SELECT
            et_date,
            (close_usd / lagInFrame(close_usd, 1) OVER (ORDER BY et_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) - 1) * 100 AS day_pct,
            (close_usd / lagInFrame(close_usd, 3) OVER (ORDER BY et_date ASC ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) - 1) * 100 AS burst3_pct,
            shares,
            avg(shares) OVER (ORDER BY et_date ASC ROWS BETWEEN 30 PRECEDING AND 1 PRECEDING) AS vol_avg_30,
            avg(shares) OVER (ORDER BY et_date ASC ROWS BETWEEN 250 PRECEDING AND 1 PRECEDING) AS vol_avg_250
        FROM (
            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,
                toFloat64(sum(volume)) AS shares
            FROM global_markets.delayed_stocks_minute_aggs
            WHERE ticker = 'SPY' AND window_start < toDateTime('2026-01-01 00:00:00')
            GROUP BY et_date
            HAVING close_usd > 0
        )
    )
)
GROUP BY mar24_d, mar26_b, mar24_r30, mar24_r250, mar24_shares

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('2020-03-20 00:00:00') AND window_start < toDateTime('2020-03-26 23:59:00')
        GROUP BY et_date
    )
)
WHERE et_date >= toDate('2020-03-23') AND et_date <= toDate('2020-03-26')
ORDER BY et_date

SELECT
    round(ath_close, 2) AS feb19_ath_close,
    round(bottom_close, 2) AS mar23_bottom_close,
    toString(rec_date) AS recovery_date,
    countIf(et_date > toDate('2020-03-23') AND et_date <= rec_date) AS sessions_to_new_high,
    dateDiff('day', toDate('2020-03-23'), rec_date) AS days_low_to_new_high,
    dateDiff('day', toDate('2020-02-19'), rec_date) AS round_trip_days,
    round(minIf(close_usd, et_date > toDate('2020-03-23') AND et_date <= rec_date), 2) AS lowest_close_after,
    toString(argMinIf(et_date, (close_usd, et_date), et_date > toDate('2020-03-23') AND et_date <= rec_date)) AS lowest_close_date,
    round((minIf(close_usd, et_date > toDate('2020-03-23') AND et_date <= rec_date) / bottom_close - 1) * 100, 1) AS closest_retest_pct,
    toString(peak07_date) AS peak_2007_date,
    toString(rec07_date) AS recovery_2013_date,
    dateDiff('day', peak07_date, rec07_date) AS round_trip_2008_days
FROM (
    SELECT
        et_date, close_usd, ath_close, bottom_close, peak07_date,
        min(if(et_date > toDate('2020-03-23') AND close_usd >= ath_close, et_date, toDate('2099-01-01'))) OVER () AS rec_date,
        min(if(et_date >= toDate('2008-01-01') AND close_usd >= peak07_close, et_date, toDate('2099-01-01'))) OVER () AS rec07_date
    FROM (
        SELECT
            et_date, close_usd,
            max(if(et_date = toDate('2020-02-19'), close_usd, -999)) OVER () AS ath_close,
            max(if(et_date = toDate('2020-03-23'), close_usd, -999)) OVER () AS bottom_close,
            max(if(et_date >= toDate('2007-01-01') AND et_date < toDate('2008-01-01'), (close_usd, et_date), (-999., toDate('1970-01-01')))) OVER () .1 AS peak07_close,
            max(if(et_date >= toDate('2007-01-01') AND et_date < toDate('2008-01-01'), (close_usd, et_date), (-999., toDate('1970-01-01')))) OVER () .2 AS peak07_date
        FROM (
            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('2021-01-01 00:00:00')
            GROUP BY et_date
            HAVING close_usd > 0
        )
    )
)
GROUP BY ath_close, bottom_close, rec_date, peak07_date, rec07_date