Double Top สองยอด: สถิติจริงหลังหลุดเนคไลน์

WITH
px AS (
    SELECT ticker, date,
           toFloat64(argMax(high, _ingest_time))  AS h,
           toFloat64(argMax(low, _ingest_time))   AS l,
           toFloat64(argMax(close, _ingest_time)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','KO','XOM')
      AND date >= '2015-01-01' AND date < today()
    GROUP BY ticker, date),
splits AS (
    SELECT ticker, groupArray(execution_date) AS split_dates
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2014-01-01'
    GROUP BY ticker),
pivots AS (
    SELECT ticker, date, h, l, c,
           toUInt8(h = max(h) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)) AS is_peak
    FROM px),
framed AS (
    SELECT ticker, date, h, is_peak,
           groupArray(h)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS h_back,
           groupArray(l)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS l_back,
           groupArray(is_peak) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS p_back,
           groupArray(c)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS c_fwd,
           groupArray(date)    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS d_fwd
    FROM pivots),
paired AS (
    SELECT ticker, date AS peak2_date, h AS peak2_high, h_back, l_back, c_fwd, d_fwd,
           arrayFirst(g -> (p_back[61 - g] = 1)
                       AND (abs((h_back[61 - g] / h) - 1) <= 0.03)
                       AND (arrayMin(arraySlice(l_back, 61 - g, g + 1)) <= (0.95 * least(h_back[61 - g], h))),
                      range(15, 61)) AS gap
    FROM framed
    WHERE is_peak = 1 AND length(l_back) = 61 AND length(c_fwd) = 91),
necked AS (
    SELECT ticker, peak2_date, gap,
           greatest(h_back[61 - gap], peak2_high)          AS peak_high,
           arrayMin(arraySlice(l_back, 61 - gap, gap + 1)) AS neckline,
           c_fwd, d_fwd
    FROM (
        SELECT p.ticker AS ticker, p.peak2_date AS peak2_date, p.peak2_high AS peak2_high,
               p.h_back AS h_back, p.l_back AS l_back, p.c_fwd AS c_fwd, p.d_fwd AS d_fwd,
               p.gap AS gap, s.split_dates AS split_dates
        FROM paired AS p
        LEFT JOIN splits AS s ON p.ticker = s.ticker)
    WHERE gap > 0
      AND arrayCount(d -> (d >= (peak2_date - 150)) AND (d <= (peak2_date + 180)), split_dates) = 0),
confirmed AS (
    SELECT ticker,
           d_fwd[k_rel + 11]                                   AS confirm_date,
           round(100 * ((peak_high - neckline) / peak_high), 2) AS depth_pct
    FROM (SELECT *, arrayFirstIndex(x -> x < neckline, arraySlice(c_fwd, 12, 20)) AS k_rel FROM necked)
    WHERE k_rel > 0
    ORDER BY ticker, confirm_date
    LIMIT 1 BY ticker, confirm_date)
SELECT
    toYear(confirm_date)                    AS year,
    count()                                 AS signal_count,
    round(quantileExact(0.5)(depth_pct), 2) AS median_depth_pct
FROM confirmed
GROUP BY year
ORDER BY year

WITH
px AS (
    SELECT ticker, date,
           toFloat64(argMax(high, _ingest_time))  AS h,
           toFloat64(argMax(low, _ingest_time))   AS l,
           toFloat64(argMax(close, _ingest_time)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','KO','XOM')
      AND date >= '2015-01-01' AND date < today()
    GROUP BY ticker, date),
splits AS (
    SELECT ticker, groupArray(execution_date) AS split_dates
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2014-01-01'
    GROUP BY ticker),
pivots AS (
    SELECT ticker, date, h, l, c,
           toUInt8(h = max(h) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)) AS is_peak
    FROM px),
framed AS (
    SELECT ticker, date, h, is_peak,
           groupArray(h)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS h_back,
           groupArray(l)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS l_back,
           groupArray(is_peak) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS p_back,
           groupArray(c)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS c_fwd,
           groupArray(date)    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS d_fwd
    FROM pivots),
paired AS (
    SELECT ticker, date AS peak2_date, h AS peak2_high, h_back, l_back, c_fwd, d_fwd,
           arrayFirst(g -> (p_back[61 - g] = 1)
                       AND (abs((h_back[61 - g] / h) - 1) <= 0.03)
                       AND (arrayMin(arraySlice(l_back, 61 - g, g + 1)) <= (0.95 * least(h_back[61 - g], h))),
                      range(15, 61)) AS gap
    FROM framed
    WHERE is_peak = 1 AND length(l_back) = 61 AND length(c_fwd) = 91),
necked AS (
    SELECT ticker, peak2_date, gap,
           arrayMin(arraySlice(l_back, 61 - gap, gap + 1)) AS neckline,
           c_fwd, d_fwd
    FROM (
        SELECT p.ticker AS ticker, p.peak2_date AS peak2_date,
               p.l_back AS l_back, p.c_fwd AS c_fwd, p.d_fwd AS d_fwd,
               p.gap AS gap, s.split_dates AS split_dates
        FROM paired AS p
        LEFT JOIN splits AS s ON p.ticker = s.ticker)
    WHERE gap > 0
      AND arrayCount(d -> (d >= (peak2_date - 150)) AND (d <= (peak2_date + 180)), split_dates) = 0),
confirmed AS (
    SELECT ticker, d_fwd[k_rel + 11] AS confirm_date
    FROM (SELECT *, arrayFirstIndex(x -> x < neckline, arraySlice(c_fwd, 12, 20)) AS k_rel FROM necked)
    WHERE k_rel > 0
    ORDER BY ticker, confirm_date
    LIMIT 1 BY ticker, confirm_date),
universe AS (
    SELECT f.c_fwd AS c_fwd, s.hit AS is_signal
    FROM framed AS f
    LEFT JOIN (SELECT ticker, confirm_date, toUInt8(1) AS hit FROM confirmed GROUP BY ticker, confirm_date) AS s
           ON f.ticker = s.ticker AND f.date = s.confirm_date
    WHERE length(f.c_fwd) = 91)
SELECT
    concat(toString(hz), ' วันทำการ')                      AS horizon_label,
    round(quantileExactIf(0.5)(ret_pct, is_signal = 1), 2) AS after_break_pct,
    round(quantileExactIf(0.5)(ret_pct, is_signal = 0), 2) AS baseline_pct,
    countIf(is_signal = 1)                                 AS signal_count
FROM (
    SELECT hz, is_signal, 100 * ((c_fwd[hz + 1] / c_fwd[1]) - 1) AS ret_pct
    FROM (SELECT c_fwd, is_signal, arrayJoin([5, 10, 20, 60]) AS hz FROM universe))
GROUP BY hz
HAVING countIf(is_signal = 1) > 0
ORDER BY hz

WITH
px AS (
    SELECT ticker, date,
           toFloat64(argMax(high, _ingest_time))  AS h,
           toFloat64(argMax(low, _ingest_time))   AS l,
           toFloat64(argMax(close, _ingest_time)) AS c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','JPM','JNJ','KO','XOM')
      AND date >= '2015-01-01' AND date < today()
    GROUP BY ticker, date),
splits AS (
    SELECT ticker, groupArray(execution_date) AS split_dates
    FROM global_markets.stocks_splits
    WHERE execution_date >= '2014-01-01'
    GROUP BY ticker),
pivots AS (
    SELECT ticker, date, h, l, c,
           toUInt8(h = max(h) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING)) AS is_peak
    FROM px),
framed AS (
    SELECT ticker, date, h, is_peak,
           groupArray(h)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS h_back,
           groupArray(l)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS l_back,
           groupArray(is_peak) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 60 PRECEDING AND CURRENT ROW) AS p_back,
           groupArray(c)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS c_fwd,
           groupArray(l)       OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS l_fwd,
           groupArray(date)    OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN CURRENT ROW AND 90 FOLLOWING) AS d_fwd
    FROM pivots),
paired AS (
    SELECT ticker, date AS peak2_date, h AS peak2_high, h_back, l_back, c_fwd, l_fwd, d_fwd,
           arrayFirst(g -> (p_back[61 - g] = 1)
                       AND (abs((h_back[61 - g] / h) - 1) <= 0.03)
                       AND (arrayMin(arraySlice(l_back, 61 - g, g + 1)) <= (0.95 * least(h_back[61 - g], h))),
                      range(15, 61)) AS gap
    FROM framed
    WHERE is_peak = 1 AND length(l_back) = 61 AND length(c_fwd) = 91),
necked AS (
    SELECT ticker, peak2_date, gap,
           greatest(h_back[61 - gap], peak2_high)          AS peak_high,
           arrayMin(arraySlice(l_back, 61 - gap, gap + 1)) AS neckline,
           c_fwd, l_fwd, d_fwd
    FROM (
        SELECT p.ticker AS ticker, p.peak2_date AS peak2_date, p.peak2_high AS peak2_high,
               p.h_back AS h_back, p.l_back AS l_back, p.c_fwd AS c_fwd, p.l_fwd AS l_fwd,
               p.d_fwd AS d_fwd, p.gap AS gap, s.split_dates AS split_dates
        FROM paired AS p
        LEFT JOIN splits AS s ON p.ticker = s.ticker)
    WHERE gap > 0
      AND arrayCount(d -> (d >= (peak2_date - 150)) AND (d <= (peak2_date + 180)), split_dates) = 0),
confirmed AS (
    SELECT ticker,
           d_fwd[k_rel + 11] AS confirm_date,
           arrayFirstIndex(x -> x <= ((2 * neckline) - peak_high), arraySlice(l_fwd, k_rel + 12, 60)) AS target_idx,
           arrayFirstIndex(x -> x > neckline, arraySlice(c_fwd, k_rel + 12, 60))                      AS recover_idx
    FROM (SELECT *, arrayFirstIndex(x -> x < neckline, arraySlice(c_fwd, 12, 20)) AS k_rel FROM necked)
    WHERE k_rel > 0
    ORDER BY ticker, confirm_date
    LIMIT 1 BY ticker, confirm_date)
SELECT
    tup.1                                   AS outcome_label,
    tup.2                                   AS signal_count,
    round(100 * (tup.2 / total_signals), 1) AS share_pct
FROM (
    SELECT count() AS total_signals,
           countIf((target_idx > 0) AND ((recover_idx = 0) OR (target_idx <= recover_idx))) AS n_target,
           countIf((NOT ((target_idx > 0) AND ((recover_idx = 0) OR (target_idx <= recover_idx)))) AND (recover_idx > 0)) AS n_recover,
           countIf((target_idx = 0) AND (recover_idx = 0)) AS n_stall
    FROM confirmed)
ARRAY JOIN [('ถึงเป้า measured move ก่อน', n_target), ('ปิดกลับเหนือเนคไลน์ก่อน', n_recover), ('ไม่เกิดทั้งสองอย่าง', n_stall)] AS tup