path_vs_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.

SELECT
    toStartOfMonth(a.date)                                                                                AS month,
    round((toFloat64(argMaxIf(a.close, a.date, a.ticker = 'NKE')) / any(p.nke_peak_close) - 1) * 100, 1)  AS nke_vs_peak_pct,
    round((toFloat64(argMaxIf(a.close, a.date, a.ticker = 'SPY')) / any(p.spy_base_close) - 1) * 100, 1)  AS spy_same_window_pct
FROM global_markets.stocks_daily_aggs AS a
CROSS JOIN
(
    SELECT
        any(n.peak_date)     AS peak_date,
        any(n.peak_close)    AS nke_peak_close,
        any(s.spy_close)     AS spy_base_close
    FROM
    (
        SELECT
            argMax(date, toFloat64(close))   AS peak_date,
            max(toFloat64(close))            AS peak_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'NKE'
          AND date >= '2016-01-01'
    ) AS n
    INNER JOIN
    (
        SELECT
            date,
            toFloat64(any(close))  AS spy_close
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'SPY'
          AND date >= '2016-01-01'
        GROUP BY date
    ) AS s ON s.date = n.peak_date
) AS p
WHERE a.ticker IN ('NKE', 'SPY')
  AND a.date >= p.peak_date
GROUP BY month
ORDER BY month