מהו trade markout? הסבר על איכות ביצוע ומדידת עסקאות

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

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_size  AS fill_size,
            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,
                size             AS fill_size
            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(avgIf(f.side * (fut.mid - f.ref_mid) / f.ref_mid, f.fill_size < 1000) * 10000, 3)       AS small_fill_bps,
    round(avgIf(f.side * (fut.mid - f.ref_mid) / f.ref_mid, f.fill_size >= 1000) * 10000, 3)      AS block_fill_bps,
    countIf(f.fill_size >= 1000)                                                                 AS block_fill_count
FROM
(
    SELECT
        sec,
        fill_size,
        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
HAVING countIf(f.fill_size < 1000) > 0
   AND countIf(f.fill_size >= 1000) > 0
ORDER BY f.horizon_s

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