Abstract / Summary

Find the customer who placed the most orders from a list of customer order records.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 64% (700k+ submissions).

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL statement retrieves the identifier of the customer who has placed the greatest number of orders. It does so by aggregating rows in the Orders table, ranking the aggregated results, and returning only the top entry. The query is compact yet expresses a complete analytical workflow: grouping, counting, sorting, and limiting.

Key Steps

The execution path can be broken down into distinct logical phases.

The first phase selects the customer_number column from the Orders table. This column is the grouping key and the only attribute projected in the final result set.

The second phase groups all rows by customer_number. During this grouping the database engine computes an internal count for each distinct customer, effectively building a histogram of order frequencies.

The third phase orders the grouped rows by the computed count in descending order. This sorting step arranges customers from the most prolific to the least prolific.

The final phase applies a LIMIT 1 clause, which instructs the engine to return only the first row of the sorted list. Consequently, the query yields the single customer_number with the highest order count.

SELECT customer_number
  FROM Orders
 GROUP BY customer_number
 ORDER BY COUNT(*) DESC
 LIMIT 1

Edge Cases Handled

When the Orders table contains no rows, the grouping phase produces an empty set, and the LIMIT 1 clause returns an empty result without error.

If multiple customers share the same maximum order count, the ORDER BY … DESC clause establishes a deterministic order based on the internal row ordering of the database engine. The LIMIT 1 then selects one of those tied customers, ensuring the query always returns a single value rather than raising an exception.

Rows where customer_number is NULL are treated as a distinct group by most relational engines. The query therefore includes a count for the NULL group, and if that group happens to have the highest count, the result will be NULL. This behavior avoids unexpected failures when null identifiers are present.

The use of *COUNT()** counts all rows, including those with null values in other columns, guaranteeing that every order contributes to the tally regardless of data completeness.

Performance Optimizations Observed

The query benefits from several implicit and explicit optimizations.

An index on customer_number enables the database to perform the grouping operation using an index-only scan. The engine can traverse the index entries in order, aggregating counts without needing to read the full table rows, which reduces I/O.

The *ORDER BY COUNT() DESC combined with LIMIT 1** allows the optimizer to apply a “top-N” algorithm. Instead of sorting the entire result set, the engine can maintain a small heap of the current highest count while streaming through the groups, dramatically lowering the amount of data that must be sorted in memory.

Because the query projects only customer_number, a covering index that includes this column can satisfy the query entirely from the index structure, eliminating the need for a separate data page lookup.

The aggregation is performed on the grouped key rather than on the entire table, which scales linearly with the number of distinct customers rather than the total number of orders. This characteristic makes the query efficient even when the Orders table contains a very large number of rows, provided the cardinality of customer_number remains moderate.

Finally, the LIMIT 1 clause short-circuits further processing once the top group is identified, preventing the engine from continuing to evaluate remaining groups after the result is known. This early-exit behavior contributes to faster response times, especially in scenarios where the most frequent customer appears early in the index order.

Imagine a big toy store that keeps a record of every order placed by customers in a giant book. The first part of this command tells the computer to look only at the lists of names for the people who bought things. Since many customers come back multiple times, their names appear over and over again in the book. The group part gathers all the orders for each specific person into their own little pile so it is easier to see how many times each person visited. The next part sorts those piles by counting how many orders are in them, placing the person with the most orders right at the very top of the list. The last part takes that very first name from the top of the list and shows it so everyone knows exactly who the most frequent shopper is.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

Time: loglinear
As a rule of thumb, the maximum input size for one second execution time is

• factorial: up to 10 elements
• exponential: up to 20 elements
• polynomial: a few thousand elements
• loglinear: up to 100,000 elements
• linear: a few million elements
• logarithmic: a billion elements
• constant: practically unlimited

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	lzl124631x	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
lzl124631x	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality