Record closes per year, SPY (price basis, first four years of the file excluded)

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
    )
SELECT
    toYear(date)                                                 AS year,
    count()                                                      AS closes_counted,
    countIf(is_record = 1)                                       AS record_closes,
    round(100 * countIf(is_record = 1) / count(), 1)             AS record_share_pct,
    any(formatDateTime(series_start, '%b %e, %Y'))               AS series_start_label,
    any(formatDateTime(addYears(series_start, 4), '%b %e, %Y'))  AS counting_from_label,
    formatDateTime(max(date), '%b %e, %Y')                       AS last_session_label
FROM flagged
WHERE date >= addYears(series_start, 4)
GROUP BY year
ORDER BY year