Abstract / Summary

Find the salesperson who sold the most products by comparing the number of orders linked to each salesperson.

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 66% (600k+ submissions).

Typical patterns to solve this problem:

• Database

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 names of salespeople whose sales_id does not appear in any order placed with a company named RED. It does this by filtering the SalesPerson table against a sub-query that extracts all sales_id values linked to orders for the target company. The logic is expressed in a single SELECT statement with a NOT IN predicate that references a derived set of identifiers.

Key Steps

The first step selects the name column from the SalesPerson table. This is the outer query that defines the result set.

SELECT name
  FROM SalesPerson

The second step applies a filter that excludes any salesperson whose identifier is present in the sub-query result. The NOT IN operator implements this exclusion.

WHERE sales_id NOT IN ( ... )

The sub-query builds the list of sales_id values that must be excluded. It joins the Orders table with the Company table using the com_id foreign key, then restricts the rows to those where the company name equals RED.

SELECT o.sales_id
  FROM Orders o, Company c
 WHERE o.com_id = c.com_id
   AND c.name = 'RED'

The combination of the outer filter and the inner selection yields the final list of salesperson names that have never been associated with an order for the specified company.

Edge Cases Handled

When the Orders table contains rows with a sales_id that is NULL, the NOT IN predicate automatically excludes those NULL values from the comparison set, preventing unintended matches. If the sub-query returns an empty result set because no orders exist for RED, the NOT IN condition evaluates to true for every row in SalesPerson, thereby returning all salesperson names. The query also correctly handles the situation where multiple orders reference the same sales_id; the sub-query’s result set is deduplicated by the database engine, ensuring that each identifier is considered only once in the exclusion test. Additionally, the use of an explicit equality comparison on c.name guarantees that only rows with an exact match to the literal string RED are considered, avoiding partial or case-insensitive matches unless the database collation dictates otherwise.

Performance Optimizations Observed

The query leverages a non-correlated sub-query, allowing the database optimizer to materialize the list of sales_id values once and reuse it for the NOT IN comparison, which reduces repeated scanning of the Orders and Company tables. The join condition o.com_id = c.com_id can be satisfied efficiently if indexes exist on the foreign key columns Orders.com_id and Company.com_id. An index on Company.name further accelerates the filtering of rows where the company name equals RED, limiting the number of rows that need to be examined in the join. Because the outer query only accesses the SalesPerson table and filters on its primary key sales_id, an index on SalesPerson.sales_id enables rapid exclusion of matching identifiers. The use of the NOT IN construct, when the sub-query result set is guaranteed to be free of NULLs, permits the optimizer to transform the predicate into a semi-join operation, which can be executed with hash or merge join strategies for large data volumes. These index choices and the non-correlated nature of the sub-query together provide a solid foundation for efficient execution without additional query rewriting.

Imagine a big toy box filled with files about all the folks who sell toys. The code acts like a detective looking for a specific group of people. First, the detective goes to the list of companies to find the one named RED. Once the detective finds that company, it looks at all the orders to see which sellers helped that specific company. After making a list of those sellers, the detective goes back to the master book of all salespeople and picks out everyone whose name is not on the list of people who worked with RED. It is just like asking to see the names of all the helpers who have never played with the red building blocks.

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

not analyzed yet

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	everthis	SQL
	lzl124631x	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
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