Order Peak Over Global Average

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

orders_details =

[{"order_id":1,"product_id":101,"quantity":12},{"order_id":1,"product_id":102,"quantity":10},{"order_id":1,"product_id":103,"quantity":15},{"order_id":2,"product_id":201,"quantity":8},{"order_id":2,"product_id":202,"quantity":4},{"order_id":2,"product_id":203,"quantity":6},{"order_id":2,"product_id":204,"quantity":4},{"order_id":3,"product_id":301,"quantity":5},{"order_id":3,"product_id":302,"quantity":18},{"order_id":3,"product_id":303,"quantity":20},{"order_id":4,"product_id":401,"quantity":2},{"order_id":4,"product_id":402,"quantity":8},{"order_id":5,"product_id":501,"quantity":9},{"order_id":5,"product_id":502,"quantity":9}]

Order Peak Over Global Average

Hints

Order Peak Over Global Average

Hints