Calendar days from a record close to the next one, 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 >= max(close) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS is_record,
            min(date) OVER ()                                                                          AS series_start,
            max(date) OVER ()                                                                          AS series_end
        FROM daily
    ),
    highs AS
    (
        SELECT
            date,
            series_end,
            leadInFrame(date, 1) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS next_high
        FROM flagged
        WHERE is_record = 1
          AND date >= addYears(series_start, 4)
    ),
    gaps AS
    (
        SELECT
            if(next_high > date, dateDiff('day', date, next_high), 99999) AS gap_days
        FROM highs
        WHERE date <= subtractDays(series_end, 365)
    ),
    bucketed AS
    (
        SELECT
            multiIf(gap_days <= 31,  'within 1 month',
                    gap_days <= 92,  '1 to 3 months',
                    gap_days <= 183, '3 to 6 months',
                    gap_days <= 365, '6 to 12 months',
                                     'more than 12 months') AS bucket,
            count()                                          AS record_closes,
            min(gap_days)                                    AS sort_key
        FROM gaps
        GROUP BY bucket
    )
SELECT
    bucket,
    record_closes,
    round(100 * record_closes / sum(record_closes) OVER (), 1)                                        AS share_pct,
    round(100 * sum(record_closes) OVER (ORDER BY sort_key ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
              / sum(record_closes) OVER (), 1)                                                        AS cumulative_share_pct
FROM bucketed
ORDER BY sort_key