Table events(order_id, ts, type) has type in ('new','ack','fill','cancel'). Find every order acknowledged and then cancelled within 50 milliseconds of the acknowledgement with no fill in between. Give the SQL and the index that makes it run.

Table events(order_id, ts, type) has type in ('new','ack','fill','cancel'). Find every order acknowledged and then cancelled within 50 milliseconds of the acknowledgement with no fill in between. Give the SQL and the index that makes it run.

Approach: Pair the two rows with a self join on the order, put the time bound inside the join predicate, then express the absence of the third event as a correlated subquery over the pair.

Self join the ack to the cancel and exclude the fill with NOT EXISTS: SELECT a.order_id, a.ts, c.ts FROM events a JOIN events c ON c.order_id = a.order_id AND c.type = 'cancel' AND c.ts > a.ts AND c.ts <= a.ts + interval '50 milliseconds' WHERE a.type = 'ack' AND NOT EXISTS (SELECT 1 FROM events f WHERE f.order_id = a.order_id AND f.type = 'fill' AND f.ts > a.ts AND f.ts < c.ts). The range predicate belongs in the join clause so the planner can drive the inner scan with it. The absence condition has to be a correlated NOT EXISTS rather than a left join with a null check, because the window for the fill depends on c.ts and cannot be evaluated before the pair is formed. The index is (order_id, ts) with type carried in it, so all three lookups become a bounded range scan inside one order's rows and the plan is a nested loop with a small inner. One correctness trap: an order can be acknowledged more than once after a replace, so restrict a to the earliest ack per order with a window function first, otherwise one cancel pairs with several acks and the count is inflated.

Follow-up: How does the query change if the same logic must run continuously on a stream rather than a table?

Key concepts: self join, not exists, composite index, range predicate.