MSFT 距高點跌多少？2015 年以來回檔數據

WITH daily AS
(
    SELECT
        date,
        toFloat64(max(close)) AS close
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'MSFT'
      AND date >= '2024-01-01'
    GROUP BY date
),
rolling AS
(
    SELECT
        date,
        close,
        max(close) OVER (ORDER BY date ROWS BETWEEN 251 PRECEDING AND CURRENT ROW) AS high_252d
    FROM daily
)
SELECT
    toString(date)                          AS session_date,
    formatDateTime(date, '%Y/%m/%d')        AS session_label,
    round(close, 2)                         AS close,
    round(high_252d, 2)                     AS high_252d_close,
    round((1 - close / high_252d) * 100, 2) AS below_252d_high_pct
FROM rolling
WHERE date >= today() - 130
ORDER BY date

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

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

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