markout

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,
        toStartOfMinute(toTimeZone(window_start, 'America/New_York')) AS et_minute,
        toFloat64(open)   AS open_px,
        toFloat64(close)  AS close_px,
        toFloat64(volume) AS vol
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'AAPL', 'KO', 'SJM', 'LANC')
      AND window_start >= '2026-07-01 00:00:00'
      AND window_start <  '2026-09-26 00:00:00'
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) >= 570
      AND (toHour(toTimeZone(window_start, 'America/New_York')) * 60
           + toMinute(toTimeZone(window_start, 'America/New_York'))) < 960
      AND volume > 0
      AND open > 0
),
ranked AS
(
    SELECT
        ticker,
        avg(vol) AS avg_minute_vol
    FROM bars
    GROUP BY ticker
    HAVING count() >= 500
),
picks AS
(
    SELECT
        argMax(ticker, avg_minute_vol) AS liquid_ticker,
        argMin(ticker, avg_minute_vol) AS thin_ticker
    FROM ranked
),
sided AS
(
    SELECT
        if(b.ticker = p.thin_ticker, 'thin', 'liquid') AS side,
        b.et_minute                                    AS et_minute,
        b.open_px                                      AS open_px,
        b.close_px                                     AS close_px,
        b.vol                                          AS vol
    FROM bars AS b
    CROSS JOIN picks AS p
    WHERE b.ticker = p.thin_ticker
       OR b.ticker = p.liquid_ticker
),
mean_vol AS
(
    SELECT side, avg(vol) AS avg_vol
    FROM sided
    GROUP BY side
),
events AS
(
    SELECT
        s.side                             AS side,
        s.et_minute                        AS et_minute,
        s.close_px                         AS fill_px,
        if(s.close_px >= s.open_px, 1, -1) AS direction
    FROM sided AS s
    INNER JOIN mean_vol AS m ON m.side = s.side
    WHERE s.vol >= 3 * m.avg_vol
),
horizons AS
(
    SELECT
        side,
        fill_px,
        direction,
        hz,
        et_minute + toIntervalMinute(hz) AS target_minute
    FROM
    (
        SELECT side, et_minute, fill_px, direction, arrayJoin([1, 2, 5, 15, 30, 60]) AS hz
        FROM events
    )
)
SELECT
    h.hz AS horizon_minutes,
    round(avgIf(h.direction * (f.close_px / h.fill_px - 1) * 10000, h.side = 'thin'), 1)   AS thin_markout_bps,
    round(avgIf(h.direction * (f.close_px / h.fill_px - 1) * 10000, h.side = 'liquid'), 1) AS liquid_markout_bps,
    count() AS observations
FROM horizons AS h
INNER JOIN sided AS f ON f.side = h.side AND f.et_minute = h.target_minute
GROUP BY h.hz
HAVING countIf(h.side = 'thin') > 0
   AND countIf(h.side = 'liquid') > 0
ORDER BY h.hz