Order Peak Over Global Average

Medium

An analytics team at an online marketplace wants to detect orders with unusually concentrated demand in a single line item. Table: orders_details order_id: order identifier product_id: product identifier within the order quantity: ordered units for that product line Data guarantee: (order_id, product_id) is unique. Definitions: Order peak quantity = maximum quantity among that order's product lines. Order average quantity = arithmetic mean of quantities in that order. Global average benchmark = maximum order average quantity among all orders. Task: Return each order_id whose order peak quantity is strictly greater than the global average benchmark. Output requirements: Return exactly one column named order_id. Sort by order_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

orders_details:
| order_id | product_id | quantity |
|----------|------------|----------|
| 1        | 101        | 12       |
| 1        | 102        | 10       |
| 1        | 103        | 15       |
| 2        | 201        | 8        |
| 2        | 202        | 4        |
| 2        | 203        | 6        |
| 2        | 204        | 4        |
| 3        | 301        | 5        |
| 3        | 302        | 18       |
| 3        | 303        | 20       |
| 4        | 401        | 2        |
| 4        | 402        | 8        |
| 5        | 501        | 9        |
| 5        | 502        | 9        |

Output

[
  {"order_id":1},
  {"order_id":3}
]

Explanation

The highest order-level average is from order 3: (5 + 18 + 20) / 3 = 14.333.... Orders 1 and 3 have peaks 15 and 20, both strictly above 14.333....

Example 2

Input

orders_details:
| order_id | product_id | quantity |
|----------|------------|----------|
| 10       | 1          | 7        |
| 10       | 2          | 7        |
| 11       | 1          | 5        |
| 11       | 2          | 5        |
| 12       | 8          | 9        |

Output

[]

Explanation

Order averages are {7, 5, 9}; the global benchmark is 9. Peaks are {7, 5, 9}, so none is strictly greater than 9.

Example 3

Input

orders_details:
| order_id | product_id | quantity |
|----------|------------|----------|
| 42       | 10         | 2        |
| 42       | 11         | 100      |
| 42       | 12         | 3        |

Output

[
  {"order_id":42}
]

Explanation

With one order, the benchmark equals that order's own average: (2 + 100 + 3) / 3 = 35. The peak 100 is strictly greater than 35.

Constraints

0 <= number of rows in orders_details <= 2200
1 <= order_id <= 1000000000
1 <= product_id <= 1000000000
1 <= quantity <= 1000000000
(order_id, product_id) is unique
An order may contain one or many product lines
Use strict inequality: peak quantity must be greater than, not equal to, the benchmark
Return exactly one column: order_id
Sort output by order_id ascending
For SQL submissions, follow SQLite 3.27.2 behavior
For Pandas submissions, return a DataFrame with column: order_id

orders_details: | order_id | product_id | quantity | |----------|------------|----------| | 1 | 101 | 12 | | 1 | 102 | 10 | | 1 | 103 | 15 | | 2 | 201 | 8 | | 2 | 202 | 4 | | 2 | 203 | 6 | | 2 | 204 | 4 | | 3 | 301 | 5 | | 3 | 302 | 18 | | 3 | 303 | 20 | | 4 | 401 | 2 | | 4 | 402 | 8 | | 5 | 501 | 9 | | 5 | 502 | 9 |

orders_details: | order_id | product_id | quantity | |----------|------------|----------| | 10 | 1 | 7 | | 10 | 2 | 7 | | 11 | 1 | 5 | | 11 | 2 | 5 | | 12 | 8 | 9 |

orders_details: | order_id | product_id | quantity | |----------|------------|----------| | 42 | 10 | 2 | | 42 | 11 | 100 | | 42 | 12 | 3 |