streaks

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
        date,
        toFloat64(any(close)) AS close_price
    FROM global_markets.stocks_daily_aggs
    WHERE ticker = 'SPY'
      AND close > 0
    GROUP BY date
),
moves AS
(
    SELECT
        date,
        close_price,
        lagInFrame(close_price, 1) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_close
    FROM bars
),
signs AS
(
    SELECT
        date,
        close_price,
        prev_close,
        sign(close_price - prev_close) AS dir
    FROM moves
    WHERE prev_close > 0
),
flags AS
(
    SELECT
        date,
        close_price,
        prev_close,
        dir,
        if(dir != lagInFrame(dir, 1) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1, 0) AS run_break
    FROM signs
),
runs AS
(
    SELECT
        date,
        close_price,
        prev_close,
        dir,
        sum(run_break) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS run_id
    FROM flags
),
run_stats AS
(
    SELECT
        run_id,
        any(dir)                                                                      AS run_dir,
        count()                                                                       AS days,
        min(date)                                                                     AS run_start,
        max(date)                                                                     AS run_end,
        round(100 * (argMax(close_price, date) / argMin(prev_close, date) - 1), 1)    AS move_pct
    FROM runs
    WHERE dir != 0
    GROUP BY run_id
)
SELECT
    concat(if(run_dir = 1, 'Up run, ', 'Down run, '), formatDateTime(run_start, '%b %Y'))                          AS streak,
    if(run_dir = 1, 'Up streak', 'Down streak')                                                                     AS direction,
    days,
    concat(formatDateTime(run_start, '%b '), toString(toDayOfMonth(run_start)), ', ', toString(toYear(run_start))) AS started,
    concat(formatDateTime(run_end, '%b '), toString(toDayOfMonth(run_end)), ', ', toString(toYear(run_end)))       AS ended,
    move_pct
FROM run_stats
ORDER BY direction DESC, days DESC, streak ASC
LIMIT 5 BY direction