Become Sovereign.
Your Market Infrastructure.

-- The "Sovereign" Backtest: Detecting Historical Arbitrage
-- Replaying Market State from March 7, 2022

WITH underlying_feed AS (
    SELECT ticker, (bid_price + ask_price) / 2 AS spot, sip_timestamp 
    FROM stocks_quotes
    WHERE sip_timestamp BETWEEN '2022-03-07 14:30:00' AND '2022-03-07 14:35:00'
)
SELECT
    extract(o.ticker, '^O:([A-Z]+)') AS root,
    toDecimal64(extract(o.ticker, '([0-9]{8})$'), 3) / 1000 AS strike,
    
    -- The Exact Stock Price at that Millisecond
    u.spot AS underlying_price_at_quote,
    (o.bid_price + o.ask_price) / 2 AS opt_mid_price,
    
    -- Calculate Intrinsic Value (Spot - Strike)
    (u.spot - strike) AS intrinsic_diff

FROM
    options_quotes AS o
ASOF LEFT JOIN underlying_feed AS u
    ON extract(o.ticker, '^O:([A-Z]+)') = u.ticker 
    AND o.sip_timestamp >= u.sip_timestamp

WHERE
    o.sip_timestamp BETWEEN '2022-03-07 14:30:00' AND '2022-03-07 14:35:00'
    AND root = 'TMUS'
    AND extract(o.ticker, '[0-9]{6}([CP])') = 'C'
ORDER BY
    o.sip_timestamp DESC
LIMIT 5

>> Result: TMUS | Spot: $123.00 | Strike: $75.00 | Price: $47.70
>> Status: Cache MISS (Cold Run) | Time: 0.473s | Rows: 13.8M
>> Arbitrage: Option trading $0.30 BELOW intrinsic value.