Duplicate order duplicate_order
The same order placed twice by a double submit: same customer, same channel, same basket, same delivery address, seconds apart. The later copy is the duplicate.
Parameters: window_seconds = 120
operations · v1 · head of operations
A later order with the same customer, channel, basket (every line and quantity) and delivery address as an earlier one placed within window_seconds, not cancelled, held or refunded as of now. A customer who really buys the same thing again two minutes later looks exactly like this; it happens, rarely.
WITH recent AS (
SELECT id, customer_id, marketplace_id, basket_key, ship_line1, total, placed_at, paid_at, picked_at,
shipped_at, cancelled_at
FROM orders
WHERE placed_at <= {now} AND placed_at >= {period_start} - INTERVAL ({window_seconds}) SECOND
AND placed_at < {period_end}
),
pairs AS (
SELECT d.id AS order_id, arg_min(o.id, o.placed_at) AS original_order_id, min(o.placed_at) AS original_at,
any_value(d.placed_at) AS placed_at
FROM recent d
JOIN recent o
ON o.customer_id = d.customer_id AND o.marketplace_id = d.marketplace_id AND o.basket_key = d.basket_key
AND o.ship_line1 = d.ship_line1 AND o.id <> d.id
AND (o.placed_at < d.placed_at OR (o.placed_at = d.placed_at AND o.id < d.id))
AND d.placed_at - o.placed_at <= INTERVAL ({window_seconds}) SECOND
WHERE d.placed_at >= {period_start}
GROUP BY d.id
),
handled AS (
SELECT DISTINCT order_id FROM order_events WHERE type IN ('held', 'refunded') AND "at" <= {now}
)
SELECT 'order' AS subject_type, o.id AS subject_id, o.id AS order_id, o.customer_id AS account_id,
o.marketplace_id, 'order:' || o.id AS scope, o.placed_at AS started_at,
CASE WHEN o.shipped_at <= {now} THEN 'high' ELSE 'medium' END AS severity,
json_object(
'order_no', o.order_no, 'original_order_id', p.original_order_id,
'seconds_apart', date_diff('second', p.original_at, o.placed_at), 'total', o.total,
'items', o.item_count, 'marketplace', m.name,
'stage', CASE WHEN o.shipped_at <= {now} THEN 'shipped' WHEN o.picked_at <= {now} THEN 'picked'
WHEN o.paid_at <= {now} THEN 'paid' ELSE 'placed' END,
'paid', o.paid_at <= {now}
) AS evidence
FROM pairs p
JOIN orders o ON o.id = p.order_id
JOIN marketplaces m ON m.id = o.marketplace_id
LEFT JOIN handled h ON h.order_id = o.id
WHERE h.order_id IS NULL AND (o.cancelled_at IS NULL OR o.cancelled_at > {now})
ORDER BY started_at, o.id