INTC markout curve, June 10 2026, measured from two reference bases

WITH
    mid_by_second AS
    (
        SELECT
            dateDiff('second', toDateTime('2026-06-10 13:30:00', 'UTC'), sip_timestamp) AS sec,
            argMax((toFloat64(bid_price) + toFloat64(ask_price)) / 2, sip_timestamp)    AS mid
        FROM global_markets.cache_stocks_quotes
        WHERE ticker = 'INTC'
          AND sip_timestamp >= toDateTime('2026-06-10 13:30:00', 'UTC')
          AND sip_timestamp <  toDateTime('2026-06-10 20:00:00', 'UTC')
          AND bid_price > 0
          AND ask_price > bid_price
        GROUP BY sec
    ),
    signed_fills AS
    (
        SELECT
            t.sec        AS sec,
            t.fill_price AS fill_price,
            q.mid        AS ref_mid,
            if(t.fill_price > q.mid, 1, -1) AS side
        FROM
        (
            SELECT
                dateDiff('second', toDateTime('2026-06-10 13:30:00', 'UTC'), sip_timestamp) AS sec,
                sec - 1          AS ref_sec,
                toFloat64(price) AS fill_price
            FROM global_markets.stocks_trades
            WHERE ticker = 'INTC'
              AND sip_timestamp >= toDateTime('2026-06-10 13:30:01', 'UTC')
              AND sip_timestamp <  toDateTime('2026-06-10 19:55:00', 'UTC')
              AND price > 0
              AND size > 0
        ) AS t
        INNER JOIN mid_by_second AS q ON q.sec = t.ref_sec
        WHERE t.fill_price != q.mid
    )
SELECT
    multiIf(f.horizon_s < 60,
            concat(toString(f.horizon_s), ' sec'),
            concat(toString(intDiv(f.horizon_s, 60)), ' min'))              AS horizon,
    round(avg(f.side * (fut.mid - f.ref_mid) / f.ref_mid) * 10000, 3)       AS mid_basis_bps,
    round(avg(f.side * (fut.mid - f.fill_price) / f.fill_price) * 10000, 3) AS fill_basis_bps,
    count()                                                                 AS fill_count
FROM
(
    SELECT
        sec,
        fill_price,
        ref_mid,
        side,
        horizon_s,
        sec + horizon_s AS future_sec
    FROM signed_fills
    ARRAY JOIN [1, 5, 15, 60, 300] AS horizon_s
) AS f
INNER JOIN mid_by_second AS fut ON fut.sec = f.future_sec
GROUP BY f.horizon_s
ORDER BY f.horizon_s