Can a Death Cross Be Bullish? The SPY Record

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
)
SELECT
    concat(formatDateTime(date, '%b'), ' ', toString(toDayOfMonth(date)), ', ', toString(toYear(date))) AS event,
    round((1 - px / high_1y) * 100, 1)                        AS off_1y_high_pct,
    if(px_1m > 0,  round((px_1m / px - 1) * 100, 1), NULL)    AS fwd_1m_pct,
    if(px_3m > 0,  round((px_3m / px - 1) * 100, 1), NULL)    AS fwd_3m_pct,
    if(px_6m > 0,  round((px_6m / px - 1) * 100, 1), NULL)    AS fwd_6m_pct,
    if(px_12m > 0, round((px_12m / px - 1) * 100, 1), NULL)   AS fwd_12m_pct
FROM flags
WHERE is_death
ORDER BY date

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
),
unrolled AS
(
    SELECT
        is_death,
        tupleElement(h, 1) AS horizon_order,
        tupleElement(h, 2) AS horizon,
        tupleElement(h, 3) AS fwd_px,
        px
    FROM flags
    ARRAY JOIN [(1, '1 month', px_1m), (2, '3 months', px_3m), (3, '6 months', px_6m), (4, '12 months', px_12m)] AS h
    WHERE rn >= 201
      AND tupleElement(h, 3) > 0
)
SELECT
    horizon,
    countIf(is_death)                                                            AS case_count,
    countIf(is_death AND fwd_px > px)                                            AS higher_count,
    toInt32(round(100 * countIf(is_death AND fwd_px > px) / countIf(is_death)))  AS higher_pct,
    toInt32(round(100 * countIf(fwd_px > px) / count()))                         AS all_sessions_higher_pct,
    round(avgIf((fwd_px / px - 1) * 100, is_death), 1)                           AS avg_return_pct,
    round(quantileExactIf((fwd_px / px - 1) * 100, is_death), 1)                 AS median_return_pct,
    round(minIf((fwd_px / px - 1) * 100, is_death), 1)                           AS worst_pct,
    round(maxIf((fwd_px / px - 1) * 100, is_death), 1)                           AS best_pct
FROM unrolled
GROUP BY horizon_order, horizon
HAVING countIf(is_death) > 0
ORDER BY horizon_order

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,
        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
    FROM bars
)
SELECT
    toString(date)    AS session_date,
    round(px, 2)      AS close,
    round(avg_50, 2)  AS sma_50,
    round(avg_200, 2) AS sma_200
FROM smas
WHERE date BETWEEN toDate('2020-02-03') AND toDate('2020-07-31')
ORDER BY date

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

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
    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
),
events AS
(
    SELECT date, px, rn, is_death
    FROM flags
    WHERE is_death OR is_golden
),
paired AS
(
    SELECT
        date,
        px,
        rn,
        is_death,
        leadInFrame(date, 1) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next_date,
        leadInFrame(px, 1)   OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next_px,
        leadInFrame(rn, 1)   OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next_rn
    FROM events
)
SELECT
    concat(formatDateTime(date, '%b'), ' ', toString(toDayOfMonth(date)), ', ', toString(toYear(date))) AS death_cross,
    if(next_rn > 0,
       concat(formatDateTime(next_date, '%b'), ' ', toString(toDayOfMonth(next_date)), ', ', toString(toYear(next_date))),
       'none yet')                                                    AS golden_cross,
    if(next_rn > 0, toInt64(next_rn) - toInt64(rn), NULL)             AS session_count,
    if(next_rn > 0, round((next_px / px - 1) * 100, 1), NULL)         AS change_pct
FROM paired
WHERE is_death
ORDER BY date