Flag outlier orders

Return order_id and total_amount for orders whose total_amount is more than twice the average order value. Order by total_amount DESC, then order_id ASC.

Compare total_amount to twice the overall average computed in a scalar subquery.

Sample input

ordersAcross all 12 orders the average total is 180, so the threshold is 2 × 180 = 360.

order_id	total_amount
1001	80
1004	220
1005	540
1010	640
1003	40

Expected output

Expected outputOrders above twice the average value, highest first — flagged for review.

order_id	total_amount
1010	640
1005	540

Constraints

Use a scalar subquery, 2 * (SELECT AVG(total_amount) FROM orders), as the threshold. Keep only orders above it. Flag, do not delete.

Expected skills

Statistical outlier detection with a scalar-subquery threshold.

SQL

AI evaluation

Run the SQL query to inspect preview rows.