Deepest closing declines from a record close, SPY

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    daily AS
    (
        SELECT
            date,
            toFloat64(argMax(close, _ingest_time)) AS close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
        GROUP BY date
    ),
    flagged AS
    (
        SELECT
            date,
            close,
            close >= max(close) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS is_record,
            min(date) OVER ()                                                                          AS series_start
        FROM daily
    ),
    runs AS
    (
        SELECT
            date,
            close,
            series_start,
            max(if(is_record = 1, date, toDate('1970-01-01'))) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak_date
        FROM flagged
    ),
    by_peak AS
    (
        SELECT
            peak_date,
            argMin(close, date) AS peak_close,
            min(close)          AS trough_close,
            argMin(date, close) AS trough_date
        FROM runs
        WHERE peak_date >= addYears(series_start, 4)
        GROUP BY peak_date
    ),
    with_next AS
    (
        SELECT
            peak_date,
            peak_close,
            trough_close,
            trough_date,
            leadInFrame(peak_date, 1) OVER (ORDER BY peak_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_peak
        FROM by_peak
    )
SELECT
    formatDateTime(peak_date, '%b %e, %Y')                                        AS peak_label,
    formatDateTime(trough_date, '%b %e, %Y')                                      AS trough_label,
    round(100 * (1 - trough_close / peak_close), 1)                               AS decline_pct,
    if(next_peak > peak_date, dateDiff('day', peak_date, next_peak), 0)           AS days_to_new_high,
    if(next_peak > peak_date, formatDateTime(next_peak, '%b %e, %Y'), 'not yet')  AS new_high_label
FROM with_next
WHERE decline_pct >= 10
ORDER BY decline_pct DESC
LIMIT 10