same_direction

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 rets AS
(
    SELECT
        date,
        ticker,
        toFloat64(close) AS c,
        lagInFrame(toFloat64(close)) OVER
            (PARTITION BY ticker ORDER BY date ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS prev_c
    FROM global_markets.stocks_daily_aggs
    WHERE ticker IN ('QQQ', 'SPY')
      AND date >= '2015-12-01'
      AND date <  today()
),
paired AS
(
    SELECT
        date,
        anyIf(c / prev_c - 1, ticker = 'QQQ') AS qqq_ret,
        anyIf(c / prev_c - 1, ticker = 'SPY') AS spy_ret
    FROM rets
    WHERE prev_c > 0
    GROUP BY date
    HAVING countIf(ticker = 'QQQ') > 0 AND countIf(ticker = 'SPY') > 0
)
SELECT
    toString(toYear(date))                                                             AS year,
    count()                                                                            AS session_count,
    round(100 * countIf(sign(qqq_ret) = sign(spy_ret)) / count(), 1)                   AS same_direction_pct,
    round(100 * quantileDeterministic(0.5)(abs(qqq_ret - spy_ret), toUInt32(date)), 2) AS median_gap_pp
FROM paired
WHERE toYear(date) >= 2016
GROUP BY year
ORDER BY year