Customer Placing the Largest Number of Orders

Customer Placing the Largest Number of Orders - Problem

Database Easy

Customer Placing the Largest Number of Orders

You're working as a data analyst for an e-commerce platform and need to identify your most valuable customer based on order frequency. Given a database table containing order information, your task is to find the customer who has placed the most orders.

Table: Orders

Column Name	Type
order_number	int
customer_number	int

• order_number is the primary key (unique identifier for each order)
• customer_number identifies which customer placed the order
• The test cases guarantee that exactly one customer will have more orders than any other

Goal: Write a SQL query to return the customer_number of the customer with the highest number of orders.

Input & Output

example_1.sql — Basic Case

$ Input: Orders table: | order_number | customer_number | |--------------|----------------| | 1 | 101 | | 2 | 102 | | 3 | 103 | | 4 | 101 | | 5 | 101 |

› Output: | customer_number | |----------------| | 101 |

💡 Note: Customer 101 has 3 orders (1, 4, 5), customer 102 has 1 order (2), and customer 103 has 1 order (3). Customer 101 has the most orders.

example_2.sql — Close Competition

$ Input: Orders table: | order_number | customer_number | |--------------|----------------| | 10 | 201 | | 11 | 202 | | 12 | 201 | | 13 | 202 | | 14 | 201 |

› Output: | customer_number | |----------------| | 201 |

💡 Note: Customer 201 has 3 orders (10, 12, 14) while customer 202 has 2 orders (11, 13). Customer 201 wins by one order.

example_3.sql — Single Customer

$ Input: Orders table: | order_number | customer_number | |--------------|----------------| | 1 | 999 | | 2 | 999 | | 3 | 999 |

› Output: | customer_number | |----------------| | 999 |

💡 Note: Only one customer (999) exists with 3 orders, so they are automatically the customer with the most orders.

Visualization

Tap to expand

Understanding the Visualization

Raw Data

Orders table with order_number and customer_number columns

Group By Customer

GROUP BY customer_number creates groups of orders for each customer

Count Orders

COUNT(*) calculates how many orders each customer has placed

Sort Descending

ORDER BY COUNT(*) DESC puts the customer with most orders first

Take Top Result

LIMIT 1 returns only the customer with the highest count

Key Takeaway

🎯 Key Insight: SQL's GROUP BY + ORDER BY + LIMIT pattern efficiently aggregates data and finds the maximum in one optimized database operation, much faster than manual counting approaches.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

GROUP BY is O(n), ORDER BY is O(k log k) where k is number of unique customers

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique customers for grouping results

✓ Linear Space

Constraints

1 ≤ order_number ≤ 10⁶
1 ≤ customer_number ≤ 10⁶
Each order_number is unique (primary key)
Exactly one customer will have the maximum number of orders

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28 Apple 22

This problem is best solved with a single SQL query using GROUP BY to count orders per customer, ORDER BY COUNT(*) DESC to sort by frequency, and LIMIT 1 to get the top customer. The optimal solution is: SELECT customer_number FROM Orders GROUP BY customer_number ORDER BY COUNT(*) DESC LIMIT 1;

Common Approaches

Approach	Time	Space	Notes
✓ Subquery Approach (Brute Force)	O(n log n)	O(k)	Use a subquery to count orders for each customer, then find the maximum
GROUP BY + ORDER BY (Optimal)	O(n log n)	O(k)	Group customers, count orders, and sort to find the top customer in one efficient query

Subquery Approach (Brute Force) — Algorithm Steps

Create a subquery that counts orders per customer using GROUP BY
Use the main query to find the customer with MAX(order_count)
Handle the case where multiple customers might have the same max count

Visualization

Tap to expand

Step-by-Step Walkthrough

Inner Subquery

GROUP BY customer_number to count orders per customer

Outer Query

Find MAX(order_count) from the grouped results

Final Selection

Return customer_number where count equals maximum

Code -

solution.c — C

// Simulating the subquery approach in C
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int orderNumber;
    int customerNumber;
} Order;

typedef struct {
    int customerNumber;
    int count;
} CustomerCount;

int findTopCustomerBruteForce(Order* orders, int orderCount) {
    CustomerCount customers[1000]; // Assuming max 1000 customers
    int uniqueCustomers = 0;
    
    // Count orders per customer (subquery equivalent)
    for (int i = 0; i < orderCount; i++) {
        int found = 0;
        for (int j = 0; j < uniqueCustomers; j++) {
            if (customers[j].customerNumber == orders[i].customerNumber) {
                customers[j].count++;
                found = 1;
                break;
            }
        }
        if (!found) {
            customers[uniqueCustomers].customerNumber = orders[i].customerNumber;
            customers[uniqueCustomers].count = 1;
            uniqueCustomers++;
        }
    }
    
    // Find customer with maximum orders (main query equivalent)
    int maxOrders = 0;
    int topCustomer = -1;
    for (int i = 0; i < uniqueCustomers; i++) {
        if (customers[i].count > maxOrders) {
            maxOrders = customers[i].count;
            topCustomer = customers[i].customerNumber;
        }
    }
    
    return topCustomer;
}

// SQL equivalent:
// SELECT customer_number FROM (
//     SELECT customer_number, COUNT(*) as order_count 
//     FROM Orders 
//     GROUP BY customer_number
// ) t WHERE order_count = (
//     SELECT MAX(order_count) FROM (
//         SELECT COUNT(*) as order_count 
//         FROM Orders 
//         GROUP BY customer_number
//     ) t2
// )

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

GROUP BY requires sorting customers, plus finding MAX across groups

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique customers for intermediate results

✓ Linear Space

Constraints

1 ≤ order_number ≤ 10⁶
1 ≤ customer_number ≤ 10⁶
Each order_number is unique (primary key)
Exactly one customer will have the maximum number of orders

42.3K Views

High Frequency

~8 min Avg. Time

1.9K 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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Subquery Approach (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler