whipsaw

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