covid_2020

WITH
bars AS
(
    SELECT
        date,
        toFloat64(argMax(close, _ingest_time)) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
    GROUP BY date
),
smas AS
(
    SELECT
        date,
        px,
        row_number() OVER (ORDER BY date) AS rn,
        avg(px) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) AS avg_50,
        avg(px) OVER (ORDER BY date ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS avg_200,
        max(px) OVER (ORDER BY date ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) AS high_1y,
        leadInFrame(px, 21) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS px_1m,
        leadInFrame(px, 63) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS px_3m,
        leadInFrame(px, 126) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS px_6m,
        leadInFrame(px, 252) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS px_12m
    FROM bars
),
states AS
(
    SELECT
        *,
        (avg_50 < avg_200) AS below,
        lagInFrame((avg_50 < avg_200), 1) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_below
    FROM smas
),
flags AS
(
    SELECT
        *,
        (rn >= 201 AND below = 1 AND prev_below = 0) AS is_death,
        (rn >= 201 AND below = 0 AND prev_below = 1) AS is_golden
    FROM states
),
cross_row AS
(
    SELECT date AS cross_date, px AS cross_px, rn AS cross_rn, px_12m AS cross_px_12m
    FROM flags
    WHERE is_death
      AND date BETWEEN toDate('2020-02-01') AND toDate('2020-05-31')
    ORDER BY date
    LIMIT 1
),
golden_row AS
(
    SELECT date AS golden_date, px AS golden_px, rn AS golden_rn
    FROM flags
    WHERE is_golden
      AND date BETWEEN toDate('2020-04-01') AND toDate('2020-12-31')
    ORDER BY date
    LIMIT 1
),
peak_row AS
(
    SELECT date AS peak_date, px AS peak_px, rn AS peak_rn
    FROM flags
    WHERE date BETWEEN toDate('2020-01-01') AND toDate('2020-03-31')
    ORDER BY px DESC
    LIMIT 1
),
low_row AS
(
    SELECT date AS low_date, px AS low_px, rn AS low_rn
    FROM flags
    WHERE date BETWEEN toDate('2020-02-01') AND toDate('2020-06-30')
    ORDER BY px ASC
    LIMIT 1
)
SELECT
    concat(formatDateTime(peak_date, '%b'), ' ', toString(toDayOfMonth(peak_date)), ', ', toString(toYear(peak_date)))       AS peak_day,
    round(peak_px, 2)                                  AS peak_close,
    concat(formatDateTime(low_date, '%b'), ' ', toString(toDayOfMonth(low_date)), ', ', toString(toYear(low_date)))           AS low_day,
    round(low_px, 2)                                   AS low_close,
    concat(formatDateTime(cross_date, '%b'), ' ', toString(toDayOfMonth(cross_date)), ', ', toString(toYear(cross_date)))     AS cross_day,
    round(cross_px, 2)                                 AS cross_close,
    concat(formatDateTime(golden_date, '%b'), ' ', toString(toDayOfMonth(golden_date)), ', ', toString(toYear(golden_date))) AS golden_day,
    round(golden_px, 2)                                AS golden_close,
    round((1 - low_px / peak_px) * 100, 1)             AS peak_to_low_drop_pct,
    toInt64(low_rn) - toInt64(peak_rn)                 AS sessions_peak_to_low,
    toInt64(cross_rn) - toInt64(low_rn)                AS sessions_low_to_cross,
    round((cross_px / low_px - 1) * 100, 1)            AS low_to_cross_gain_pct,
    round((1 - cross_px / peak_px) * 100, 1)           AS cross_off_peak_pct,
    toInt64(golden_rn) - toInt64(cross_rn)             AS sessions_cross_to_golden,
    round((golden_px / cross_px - 1) * 100, 1)         AS cross_to_golden_gain_pct,
    round((cross_px_12m / cross_px - 1) * 100, 1)      AS fwd_12m_pct
FROM cross_row, peak_row, low_row, golden_row