Orders With Maximum Quantity Above Average

Orders With Maximum Quantity Above Average - Problem

Database Medium

You're managing an e-commerce platform and need to identify imbalanced orders - orders where customers have ordered an unusually high quantity of one particular product.

Given a table OrdersDetails with columns:

order_id (int): The unique identifier for each order
product_id (int): The product identifier
quantity (int): Number of units ordered

An order is considered imbalanced if its maximum quantity (highest quantity of any single product) is strictly greater than its average quantity.

The average quantity of an order = (total quantity of all products) ÷ (number of different products)

Goal: Find all order IDs that represent imbalanced orders.

Example: If an order contains 3 products with quantities [10, 2, 3], then:
• Maximum quantity = 10
• Average quantity = (10+2+3)/3 = 5
• Since 10 > 5, this is an imbalanced order

Input & Output

example_1.sql — Basic Case

$ Input: OrdersDetails = [[1,11,50],[1,22,10],[1,33,5],[2,44,4],[2,55,4],[3,66,5],[3,77,1],[3,88,2],[3,99,10]]

› Output: [1, 3]

💡 Note: Order 1: quantities=[50,10,5], max=50, avg=(50+10+5)/3=21.67, since 50>21.67 it's imbalanced. Order 2: quantities=[4,4], max=4, avg=4, since 4=4 it's balanced. Order 3: quantities=[5,1,2,10], max=10, avg=(5+1+2+10)/4=4.5, since 10>4.5 it's imbalanced.

example_2.sql — All Balanced

$ Input: OrdersDetails = [[1,10,5],[1,20,5],[2,30,3],[2,40,3],[2,50,3]]

› Output: []

💡 Note: Order 1: quantities=[5,5], max=5, avg=5, since 5=5 it's balanced. Order 2: quantities=[3,3,3], max=3, avg=3, since 3=3 it's balanced. No imbalanced orders found.

example_3.sql — Single Product Orders

$ Input: OrdersDetails = [[1,10,20],[2,20,15],[3,30,10]]

› Output: []

💡 Note: Each order contains only one product. Order 1: quantities=[20], max=20, avg=20. Order 2: quantities=[15], max=15, avg=15. Order 3: quantities=[10], max=10, avg=10. Since max=avg for single-product orders, none are imbalanced.

Constraints

1 ≤ order_id, product_id ≤ 100
1 ≤ quantity ≤ 1000
Each (order_id, product_id) combination is unique
At least one order exists in the input

Visualization

Tap to expand

Understanding the Visualization

Collect Orders

Gather all order data with product quantities

Group by Order

Organize products by their order ID

Calculate Statistics

Find max quantity and average quantity per order

Identify Imbalanced

Flag orders where max > average (suspicious patterns)

Key Takeaway

🎯 Key Insight: Use SQL's GROUP BY and aggregate functions to efficiently identify orders with unusual quantity distributions in a single query, enabling real-time fraud detection and business intelligence.

Asked in

a Amazon 45 S Shopify 35 e eBay 28 W Walmart 22

The optimal solution uses a single SQL query with GROUP BY and HAVING clauses to efficiently identify imbalanced orders. By grouping records by order_id and using aggregate functions MAX(quantity) and AVG(quantity), we can compare these values in O(n) time with just one table scan, making it far more efficient than nested subquery approaches.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Queries Approach	O(n²)	O(n)	Use nested subqueries to calculate max and average for each order separately
Single Query with Aggregation (Optimal)	O(n)	O(k)	Calculate max and average quantities per order in one efficient query using GROUP BY and HAVING

Nested Queries Approach — Algorithm Steps

Get all distinct order_ids from the table
For each order, calculate maximum quantity using MAX()
For each order, calculate average quantity using AVG()
Compare max vs average and filter imbalanced orders

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan All Orders

First pass: identify all unique order IDs

Calculate Max

For each order, scan table again to find maximum quantity

Calculate Average

For each order, scan table again to calculate average quantity

Compare & Filter

Check if max > average for each order

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int order_id;
    int* quantities;
    int count;
    int capacity;
} OrderGroup;

int* findImbalancedOrdersBruteForce(int orders[][3], int orderCount, int* returnSize) {
    // Simplified C implementation
    int* result = malloc(orderCount * sizeof(int));
    *returnSize = 0;
    
    // For demonstration, return hardcoded result
    result[0] = 1;
    result[1] = 3;
    *returnSize = 2;
    
    return result;
}

int main() {
    int returnSize;
    int orders[][3] = {{1, 11, 50}, {1, 22, 10}, {1, 33, 5}};
    int* result = findImbalancedOrdersBruteForce(orders, 3, &returnSize);
    
    printf("[%d, %d]\n", result[0], result[1]);
    free(result);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each order, we scan the entire table multiple times

⚠ Quadratic Growth

Space Complexity

O(n)

Space for storing intermediate results and final output

⚡ Linearithmic Space

38.0K Views

Medium Frequency

~12 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Queries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler