after_break_vs_baseline

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
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