drawdown_lines

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                  AS d,
            toFloat64(max(close)) AS px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'MSFT'
          AND date >= '2003-03-01'
        GROUP BY d
    ),
    pathed AS
    (
        SELECT
            d,
            round((px / max(px) OVER (ORDER BY d ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - 1) * 100, 2) AS dd_pct
        FROM daily
    ),
    stepped AS
    (
        SELECT
            d,
            dd_pct,
            lagInFrame(dd_pct) OVER (ORDER BY d ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_dd_pct
        FROM pathed
    ),
    grid AS
    (
        SELECT arrayJoin([10., 20., 25., 33., 40., 50.]) AS threshold
    )
SELECT
    concat('-', toString(toUInt8(g.threshold)), '%')                       AS threshold_label,
    countIf(s.dd_pct <= -g.threshold AND s.prev_dd_pct > -g.threshold)     AS episode_count,
    countIf(s.dd_pct <= -g.threshold)                                      AS below_count,
    toString(min(s.d))                                                     AS sample_from,
    toString(max(s.d))                                                     AS sample_to
FROM stepped AS s
CROSS JOIN grid AS g
GROUP BY g.threshold
ORDER BY g.threshold