drawdown_buckets

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
),
tagged AS
(
    SELECT
        multiIf(close >= peak_close, 1,
                close / peak_close >= 0.95, 2,
                close / peak_close >= 0.90, 3,
                close / peak_close >= 0.80, 4,
                5) AS ord,
        multiIf(close >= peak_close, '當日收在新高',
                close / peak_close >= 0.95, '距高點 0 至 5%',
                close / peak_close >= 0.90, '距高點 5 至 10%',
                close / peak_close >= 0.80, '距高點 10 至 20%',
                '距高點 20% 以上') AS bucket
    FROM marked
),
counts AS
(
    SELECT
        ord,
        any(bucket) AS bucket_label,
        count()     AS sessions
    FROM tagged
    GROUP BY ord
),
totals AS
(
    SELECT sum(sessions) AS total FROM counts
)
SELECT
    c.bucket_label                         AS drawdown_bucket,
    toUInt32(c.sessions)                   AS sessions,
    round(c.sessions * 100.0 / t.total, 1) AS share_pct,
    round(sum(c.sessions) OVER (ORDER BY c.ord DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
          * 100.0 / t.total, 1)            AS at_least_this_deep_pct
FROM counts AS c
CROSS JOIN totals AS t
ORDER BY c.ord