Definitions

The definitions the agent and Ask both run on. Each has an owner, a version, SQL and tests. Edits make a new version.

Duplicate order

duplicate_order unit: orders agent detector

The same order arriving twice: same branch, same channel, same total, within window_seconds, on a channel where such pairs are unusually frequent. The later copy is the duplicate.

opsCOO · ops v2

A later order at the same branch on the same channel with the same total, placed within window_seconds of an earlier one, on a channel where such pairs are at least min_channel_rate of its orders in the period. Identical baskets a few seconds apart happen by chance at busy stores; a double-submit bug shows up as a rate.

latest change: A pair counts only when its channel's pair rate in the period reaches 0.4%; identical baskets seconds apart also happen by chance at busy stores.

pass count_betweenobserved 0, expected between 0.0 and 20.0
pass magicpin_double_submitobserved 10, expected between 5.0 and 30.0
pass uniqueobserved {'duplicates': 0}, expected order_id unique
pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object

SQL, rendered for now

WITH recent AS (
  SELECT o.id, o.branch_id, o.channel_id, o.placed_at, o.total, o.item_count, o.status
  FROM orders o
  WHERE o.placed_at >= TIMESTAMP 'Thu 23:00:00' - INTERVAL (60) SECOND
    AND o.placed_at < TIMESTAMP 'Fri 23:00:00' AND o.placed_at <= TIMESTAMP 'Fri 23:00:00'
),
pairs AS (
  SELECT d.id AS order_id, d.branch_id, d.channel_id, d.placed_at, d.total, d.item_count, d.status,
         arg_min(o.id, o.placed_at) AS original_order_id,
         min(o.placed_at) AS original_placed_at,
         any_value(o.status) AS original_status,
         count(*) AS earlier_copies
  FROM recent d
  JOIN recent o
    ON o.branch_id = d.branch_id AND o.channel_id = d.channel_id AND o.total = d.total
   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 (60) SECOND
  WHERE d.placed_at >= TIMESTAMP 'Thu 23:00:00'
  GROUP BY d.id, d.branch_id, d.channel_id, d.placed_at, d.total, d.item_count, d.status
),
channel_orders AS (
  SELECT channel_id, count(*) AS orders
  FROM recent
  WHERE placed_at >= TIMESTAMP 'Thu 23:00:00'
  GROUP BY channel_id
),
channel_rate AS (
  SELECT p.channel_id, count(*) AS pairs, count(*) / any_value(co.orders) AS rate
  FROM pairs p
  JOIN channel_orders co ON co.channel_id = p.channel_id
  GROUP BY p.channel_id
)
SELECT 'order' AS subject_type, p.order_id AS subject_id, p.order_id, p.branch_id, b.merchant_id,
       p.channel_id, p.placed_at AS started_at,
       CASE WHEN p.status = 'delivered' AND p.original_status = 'delivered' THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'original_order_id', p.original_order_id,
         'seconds_apart', date_diff('second', p.original_placed_at, p.placed_at),
         'total', p.total,
         'item_count', p.item_count,
         'channel', c.name,
         'status', p.status,
         'original_status', p.original_status,
         'earlier_copies', p.earlier_copies,
         'channel_pairs', cr.pairs,
         'channel_duplicate_rate', round(cr.rate, 4)
       ) AS evidence
FROM pairs p
JOIN branches b ON b.id = p.branch_id
JOIN channels c ON c.id = p.channel_id
JOIN channel_rate cr ON cr.channel_id = p.channel_id
WHERE cr.rate >= 0.004
ORDER BY started_at, p.order_id

Parameters

change one, see what changes tonight, then save it as a new version with a note

History

append-only; nothing is edited in place

definitionCOO ops v1 First version: same branch, channel and total within 60 seconds.
definitionCOO ops v2 A pair counts only when its channel's pair rate in the period reaches 0.4%; identical baskets seconds apart also happen by chance at busy stores.