Order anomaly audit

Write a query that returns one row per detected order anomaly across duplicate orders, suspicious revenue, and invalid order-state transitions.

Break each anomaly type into a named CTE, then union the findings into a stable two-column audit result.

Sample input

ordersOrder-level revenue facts with duplicate rows and suspicious amounts.

order_id	customer_id	status	gross_revenue	refund_amount
1001	10	placed	120	0
1001	10	placed	120	0
1002	11	paid	-25	0
1003	12	refunded	80	90

order_eventsLifecycle events used to detect impossible state transitions.

Expected output

Expected output shapeExample anomaly findings. Your final query should return all matching findings from the sandbox.

Constraints

Treat duplicate order rows, negative gross revenue, refunds larger than gross revenue, and paid events after cancellation as audit findings.

Expected skills

CTE decomposition, row-level data quality checks, event ordering, and audit output that is easy to review.

SQL

AI evaluation

Run the SQL query to inspect preview rows.