death_crosses

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