Effective spread split into realized spread and adverse selection, by half hour

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 + 60   AS future_sec,
            t.et_time    AS et_time,
            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,
                formatDateTime(toStartOfInterval(toTimeZone(sip_timestamp, 'America/New_York'), toIntervalMinute(30)), '%H:%i') AS et_time
            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
    f.et_time                                                                  AS et_time,
    round(avg(2 * f.side * (f.fill_price - f.ref_mid) / f.ref_mid) * 10000, 3) AS effective_spread_bps,
    round(avg(2 * f.side * (f.fill_price - fut.mid) / f.ref_mid) * 10000, 3)   AS realized_spread_bps,
    round(avg(2 * f.side * (fut.mid - f.ref_mid) / f.ref_mid) * 10000, 3)      AS adverse_selection_bps,
    count()                                                                    AS fill_count
FROM signed_fills AS f
INNER JOIN mid_by_second AS fut ON fut.sec = f.future_sec
GROUP BY f.et_time
ORDER BY f.et_time