drawdown_episodes

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,
        argMax(date, close) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak_date
    FROM daily
),
next_session AS
(
    SELECT
        date,
        any(date) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_date
    FROM daily
),
episodes AS
(
    SELECT
        peak_date,
        any(peak_close)     AS peak_level,
        min(close)          AS trough_level,
        argMin(date, close) AS trough_date,
        max(date)           AS last_below_date,
        count()             AS sessions_below
    FROM marked
    WHERE close < peak_close
    GROUP BY peak_date
    HAVING min(close) / any(peak_close) <= 0.90
)
SELECT
    formatDateTime(e.peak_date, '%Y/%m/%d')             AS peak_on,
    formatDateTime(e.trough_date, '%Y/%m/%d')           AS trough_on,
    round((1 - e.trough_level / e.peak_level) * 100, 1) AS depth_pct,
    toUInt32(e.sessions_below)                          AS sessions_below_peak,
    if(n.next_date > e.peak_date,
       toUInt32(dateDiff('day', e.peak_date, n.next_date)),
       toUInt32(0))                                     AS days_to_recover
FROM episodes AS e
LEFT JOIN next_session AS n ON n.date = e.last_below_date
ORDER BY depth_pct DESC