Tick-Level Trade Execution Matching via Native ASOF JOIN

-- Match each executed trade with the latest market quote effective at that exact microsecond

SELECT t.trade_id,
       t.symbol,
       t.trade_time,
       t.executed_price,
       t.shares,
       q.quote_time AS prevailing_quote_time,
       q.bid_price,
       q.ask_price, -- Effective slippage calculation (difference between ask and executed price)
 round(t.executed_price - q.ask_price, 4) AS slippage
FROM trades t asof
JOIN quotes q ON t.symbol = q.symbol
AND t.trade_time >= q.quote_time
ORDER BY t.trade_time;

CREATE TABLE quotes (quote_time TIMESTAMP, symbol VARCHAR, bid_price DOUBLE,
                                                                     ask_price DOUBLE);


CREATE TABLE trades (trade_time TIMESTAMP, symbol VARCHAR, trade_id INT, executed_price DOUBLE,
                                                                                        shares INT);


INSERT INTO quotes
VALUES ('2026-03-01 09:30:00.100', 'NVDA', 850.10, 850.25),
       ('2026-03-01 09:30:00.250', 'NVDA', 850.15, 850.30),
       ('2026-03-01 09:30:00.400', 'NVDA', 850.20, 850.35);


INSERT INTO trades
VALUES ('2026-03-01 09:30:00.150', 'NVDA', 101, 850.25, 100),
       ('2026-03-01 09:30:00.300', 'NVDA', 102, 850.30, 200),
       ('2026-03-01 09:30:00.450', 'NVDA', 103, 850.32, 150);