The full spread of next-session returns for top-20 daily gainers

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,
        max(toFloat64(open))   AS o,
        max(toFloat64(close))  AS c,
        max(toFloat64(volume)) AS vol
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 1095
      AND date <= today() - 2
      AND ifNull(otc, 0) = 0
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, date
),
seq AS
(
    SELECT
        ticker,
        date,
        c,
        vol,
        lagInFrame(c)     OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_c,
        leadInFrame(o)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_o,
        leadInFrame(c)    OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_c,
        leadInFrame(date) OVER (PARTITION BY ticker ORDER BY date ASC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next_date
    FROM bars
),
movers AS
(
    SELECT
        date,
        ticker,
        100 * (next_c / next_o - 1) AS next_oc_pct,
        100 * (next_c / c - 1)      AS next_cc_pct,
        row_number() OVER (PARTITION BY date ORDER BY c / prev_c DESC, ticker ASC) AS rnk
    FROM seq
    WHERE prev_c >= 5
      AND next_o > 0
      AND next_c > 0
      AND c * vol >= 5000000
      AND dateDiff('day', date, next_date) <= 6
),
picks AS
(
    SELECT
        date,
        ticker,
        next_cc_pct,
        next_oc_pct,
        arrayJoin(['p05', 'p25', 'p50', 'p75', 'p95']) AS percentile
    FROM movers
    WHERE rnk <= 20
)
SELECT
    percentile,
    round(multiIf(percentile = 'p05', quantileDeterministic(0.05)(next_cc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p25', quantileDeterministic(0.25)(next_cc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p50', quantileDeterministic(0.5)(next_cc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p75', quantileDeterministic(0.75)(next_cc_pct, cityHash64(ticker, toString(date))),
                                      quantileDeterministic(0.95)(next_cc_pct, cityHash64(ticker, toString(date)))), 2) AS close_to_close_pct,
    round(multiIf(percentile = 'p05', quantileDeterministic(0.05)(next_oc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p25', quantileDeterministic(0.25)(next_oc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p50', quantileDeterministic(0.5)(next_oc_pct, cityHash64(ticker, toString(date))),
                  percentile = 'p75', quantileDeterministic(0.75)(next_oc_pct, cityHash64(ticker, toString(date))),
                                      quantileDeterministic(0.95)(next_oc_pct, cityHash64(ticker, toString(date)))), 2) AS open_to_close_pct
FROM picks
GROUP BY percentile
ORDER BY percentile ASC