underwater_curve

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(max(close)) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'MSFT'
      AND date >= '2015-01-01'
    GROUP BY date
),
marked AS
(
    SELECT
        date,
        close,
        max(close) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak_close
    FROM daily
)
SELECT
    formatDateTime(toStartOfMonth(date), '%Y-%m')                       AS month,
    round(argMax(close, date), 2)                                       AS month_close,
    round(argMax(peak_close, date), 2)                                  AS running_peak_close,
    round((1 - argMax(close, date) / argMax(peak_close, date)) * 100, 1) AS below_peak_pct
FROM marked
GROUP BY month
ORDER BY month