move_distribution

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
bars AS (
    SELECT
        ticker,
        date,
        toFloat64(high)  AS hi,
        toFloat64(low)   AS lo,
        toFloat64(close) AS px
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('AAPL','MSFT','NVDA','AMZN','GOOGL','META','TSLA','AVGO','JPM','BAC','WFC','GS','KO','PEP','PG','JNJ','PFE','MRK','XOM','CVX','WMT','HD','MCD','NKE','CSCO','INTC','IBM','T','VZ','DIS')
      AND date >= '2010-01-04'
      AND date <= '2026-09-30'
),
split_dates AS (
    SELECT
        ticker,
        groupArray(execution_date) AS split_days
    FROM
    (
        SELECT ticker, execution_date
        FROM global_markets.stocks_splits
        WHERE execution_date >= '2009-10-01'
        GROUP BY ticker, execution_date
    )
    GROUP BY ticker
),
stepped AS (
    SELECT
        ticker,
        date,
        px,
        hi - lo AS rng,
        if(hi < lagInFrame(hi, 1) OVER w AND lo < lagInFrame(lo, 1) OVER w, 1, 0) AS lower_both
    FROM bars
    WINDOW w AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
),
feat AS (
    SELECT
        ticker,
        date,
        px,
        sum(lower_both) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)  AS wedge_streak,
        avg(rng)        OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW)  AS rng5,
        groupArray(rng) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN 64 PRECEDING AND 5 PRECEDING) AS prior_rng,
        lagInFrame(px, 5)   OVER wf AS px_back5,
        leadInFrame(px, 5)  OVER wf AS px_fwd5,
        leadInFrame(px, 10) OVER wf AS px_fwd10,
        leadInFrame(px, 20) OVER wf AS px_fwd20
    FROM stepped
    WINDOW wf AS (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
),
scored AS (
    SELECT
        f.px       AS px,
        f.px_fwd20 AS px_fwd20,
        if(f.wedge_streak = 4 AND f.rng5 < arraySort(f.prior_rng)[15], 1, 0) AS is_wedge
    FROM feat AS f
    LEFT JOIN split_dates AS s ON s.ticker = f.ticker
    WHERE length(f.prior_rng) = 60
      AND f.px_back5 > 0
      AND f.px_fwd5  > 0
      AND f.px_fwd10 > 0
      AND f.px_fwd20 > 0
      AND NOT arrayExists(d -> (d >= f.date - 10) AND (d <= f.date + 32), s.split_days)
)
SELECT
    move_bucket,
    round(100 * wedge_n / sum(wedge_n) OVER (), 1) AS wedge_share_pct,
    round(100 * base_n  / sum(base_n)  OVER (), 1) AS base_share_pct
FROM
(
    SELECT
        multiIf(fwd20 < -10, 'below -10%',
                fwd20 < -5,  '-10% to -5%',
                fwd20 < 0,   '-5% to 0%',
                fwd20 < 5,   '0% to +5%',
                fwd20 < 10,  '+5% to +10%',
                             'above +10%') AS move_bucket,
        multiIf(fwd20 < -10, 1, fwd20 < -5, 2, fwd20 < 0, 3, fwd20 < 5, 4, fwd20 < 10, 5, 6) AS bucket_order,
        countIf(is_wedge = 1) AS wedge_n,
        count()               AS base_n
    FROM
    (
        SELECT is_wedge, 100 * (px_fwd20 / px - 1) AS fwd20
        FROM scored
    )
    GROUP BY move_bucket, bucket_order
)
ORDER BY bucket_order ASC