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

Loading problem...

Preparing your editor. You can read the problem while it loads.

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