Orders With Maximum Quantity Above Average - Problem
You have a table OrdersDetails that contains information about products ordered in different orders. Each row represents one product in an order with its quantity.
Your task is to find imbalanced orders. An imbalanced order is one where:
- The maximum quantity of any single product in the order is strictly greater than the average quantity of that order
For each order:
- Average quantity = (total quantity of all products) ÷ (number of different products)
- Maximum quantity = highest quantity of any single product
Return the order_id of all imbalanced orders in any order.
Table Schema
OrdersDetails
| Column Name | Type | Description |
|---|---|---|
order_id
PK
|
int | Identifier for the order |
product_id
PK
|
int | Identifier for the product |
quantity
|
int | Quantity of the product ordered |
Primary Key: (order_id, product_id)
Note: Each order can have multiple products, and each (order_id, product_id) combination is unique
Input & Output
Example 1 — Mixed Imbalanced Orders
Input Table:
| order_id | product_id | quantity |
|---|---|---|
| 1 | 101 | 30 |
| 1 | 102 | 10 |
| 2 | 201 | 20 |
| 2 | 202 | 20 |
| 3 | 301 | 50 |
| 3 | 302 | 30 |
| 3 | 303 | 20 |
Output:
| order_id |
|---|
| 1 |
| 3 |
💡 Note:
Order 1: MAX(30) > AVG((30+10)/2 = 20) ✓ Imbalanced
Order 2: MAX(20) = AVG((20+20)/2 = 20) ✗ Balanced
Order 3: MAX(50) > AVG((50+30+20)/3 = 33.33) ✓ Imbalanced
Example 2 — Single Product Orders
Input Table:
| order_id | product_id | quantity |
|---|---|---|
| 100 | 1001 | 25 |
| 200 | 2001 | 15 |
Output:
| order_id |
|---|
💡 Note:
Orders with single products have MAX = AVG, so none are imbalanced. Order 100: MAX(25) = AVG(25). Order 200: MAX(15) = AVG(15).
Constraints
-
1 ≤ order_id ≤ 10^6 -
1 ≤ product_id ≤ 10^6 -
1 ≤ quantity ≤ 10^5 -
Each
(order_id, product_id)combination is unique
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code