마켓 메이커가 역선택으로 손실을 보는 이유

WITH
  qs AS (
    SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
           avg((toFloat64(bid_price) + toFloat64(ask_price)) / 2) AS mid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-05-14 14:00:00'
      AND sip_timestamp <  '2026-05-14 17:06:00'
      AND bid_price > 0 AND ask_price > bid_price
    GROUP BY ts
  ),
  fills AS (
    SELECT t.ts AS ts, t.px AS px, t.shares AS shares, q.mid AS mid_at_fill,
           if(t.px > q.mid, 1, -1) AS taker_side
    FROM (
      SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
             toFloat64(price) AS px, toUInt64(size) AS shares
      FROM global_markets.stocks_trades
      WHERE ticker = 'AAPL'
        AND sip_timestamp >= '2026-05-14 14:00:00'
        AND sip_timestamp <  '2026-05-14 17:00:00'
        AND size > 0
    ) AS t
    INNER JOIN qs AS q ON q.ts = t.ts
    WHERE t.px != q.mid AND abs(t.px / q.mid - 1) < 0.02
  )
SELECT
  concat('+', toString(e.secs), 's')                          AS horizon,
  round(1000 * avg(e.taker_side * (e.px - e.mid_at_fill)), 2) AS edge_at_fill_mils,
  round(1000 * avg(e.taker_side * (e.px - f.mid)), 2)         AS edge_after_mils
FROM (
  SELECT toUInt32(ts + secs) AS future_ts, secs, px, mid_at_fill, taker_side
  FROM (SELECT *, arrayJoin([1, 5, 15, 30, 60, 300]) AS secs FROM fills)
) AS e
INNER JOIN qs AS f ON f.ts = e.future_ts
GROUP BY e.secs
ORDER BY e.secs

WITH
  qs AS (
    SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
           avg((toFloat64(bid_price) + toFloat64(ask_price)) / 2) AS mid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-05-14 14:00:00'
      AND sip_timestamp <  '2026-05-14 17:06:00'
      AND bid_price > 0 AND ask_price > bid_price
    GROUP BY ts
  ),
  fills AS (
    SELECT t.ts AS ts, t.px AS px, t.shares AS shares, q.mid AS mid_at_fill,
           if(t.px > q.mid, 1, -1) AS taker_side
    FROM (
      SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
             toFloat64(price) AS px, toUInt64(size) AS shares
      FROM global_markets.stocks_trades
      WHERE ticker = 'AAPL'
        AND sip_timestamp >= '2026-05-14 14:00:00'
        AND sip_timestamp <  '2026-05-14 17:00:00'
        AND size > 0
    ) AS t
    INNER JOIN qs AS q ON q.ts = t.ts
    WHERE t.px != q.mid AND abs(t.px / q.mid - 1) < 0.02
  )
SELECT
  multiIf(s.shares < 100, 'under 100 shares',
          s.shares < 500, '100 to 499',
          s.shares < 1000, '500 to 999',
          '1,000 or more')                                    AS size_bucket,
  count()                                                     AS fills_count,
  round(1000 * avg(s.taker_side * (s.px - s.mid_at_fill)), 2) AS edge_at_fill_mils,
  round(1000 * avg(s.taker_side * (s.px - f.mid)), 2)         AS edge_after_60s_mils
FROM (SELECT *, toUInt32(ts + 60) AS future_ts FROM fills) AS s
INNER JOIN qs AS f ON f.ts = s.future_ts
GROUP BY size_bucket
ORDER BY min(s.shares)

WITH
  bars AS (
    SELECT ticker, window_start,
           toDate(toTimeZone(window_start, 'America/New_York')) AS et_date,
           toFloat64(close) AS px
    FROM global_markets.delayed_stocks_minute_aggs
    WHERE ticker IN ('SPY', 'NVDA')
      AND window_start >= '2026-05-01 00:00:00'
      AND window_start <  '2026-06-01 00:00:00'
      AND transactions >= 50
      AND close > 0
  ),
  starts AS (
    SELECT ticker, et_date, px, mins,
           window_start + toIntervalMinute(mins) AS future_start
    FROM (SELECT *, arrayJoin([1, 2, 5, 15, 30, 60]) AS mins FROM bars)
  )
SELECT
  concat(toString(s.mins), ' min')                                AS hold_horizon,
  round(avgIf(abs(f.px / s.px - 1) * 10000, s.ticker = 'SPY'), 1)  AS spy_drift_bps,
  round(avgIf(abs(f.px / s.px - 1) * 10000, s.ticker = 'NVDA'), 1) AS nvda_drift_bps
FROM starts AS s
INNER JOIN bars AS f
  ON f.ticker = s.ticker AND f.et_date = s.et_date AND f.window_start = s.future_start
GROUP BY s.mins
HAVING countIf(s.ticker = 'SPY') > 0 AND countIf(s.ticker = 'NVDA') > 0
ORDER BY s.mins

WITH
  qs AS (
    SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
           avg((toFloat64(bid_price) + toFloat64(ask_price)) / 2) AS mid
    FROM global_markets.cache_stocks_quotes
    WHERE ticker = 'AAPL'
      AND sip_timestamp >= '2026-05-14 14:00:00'
      AND sip_timestamp <  '2026-05-14 17:06:00'
      AND bid_price > 0 AND ask_price > bid_price
    GROUP BY ts
  ),
  fills AS (
    SELECT t.ts AS ts, t.px AS px, t.shares AS shares, q.mid AS mid_at_fill,
           if(t.px > q.mid, 1, -1) AS taker_side
    FROM (
      SELECT toUnixTimestamp(toDateTime(sip_timestamp)) AS ts,
             toFloat64(price) AS px, toUInt64(size) AS shares
      FROM global_markets.stocks_trades
      WHERE ticker = 'AAPL'
        AND sip_timestamp >= '2026-05-14 14:00:00'
        AND sip_timestamp <  '2026-05-14 17:00:00'
        AND size > 0
    ) AS t
    INNER JOIN qs AS q ON q.ts = t.ts
    WHERE t.px != q.mid AND abs(t.px / q.mid - 1) < 0.02
  )
SELECT
  multiIf(s.cents <= 0.5, '0.5 cents or less',
          s.cents <= 1.0, '0.5 to 1 cent',
          s.cents <= 2.0, '1 to 2 cents',
          'over 2 cents')                                  AS distance_from_mid,
  round(100 * sum(s.shares) / sum(sum(s.shares)) OVER (), 2) AS share_of_volume_pct,
  round(1000 * avg(s.taker_side * (s.px - f.mid)), 2)        AS edge_after_60s_mils
FROM (
  SELECT *, toUInt32(ts + 60) AS future_ts, abs(px - mid_at_fill) * 100 AS cents
  FROM fills
) AS s
INNER JOIN qs AS f ON f.ts = s.future_ts
GROUP BY distance_from_mid
ORDER BY min(s.cents)