Abstract / Summary

Given a period, list all products bought by customers with their order dates.

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 72% (400k+ 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 a summary of product sales for a specific two-week period in February 2020. It selects each product name together with the total number of units sold, filters the rows to the defined date range, aggregates the data by product, and finally limits the result set to products whose cumulative sales meet or exceed a threshold of one hundred units.

Key Steps

The query can be broken down into distinct logical phases, each represented by a fragment of the original statement.

SELECT p.product_name, SUM(o.unit) AS unit

In this initial phase the query defines the output columns. The product name is taken directly from the Products table, while the total units are calculated with the aggregate function SUM applied to the unit column of the Orders table. The alias unit is assigned to the aggregated value for readability.

FROM Products p, Orders o

Here the two source tables are introduced. The comma syntax creates an implicit inner join, meaning that only rows where a matching product identifier exists in both tables will be considered for further processing.

WHERE p.product_id = o.product_id
  AND order_date BETWEEN '2020-02-01' AND '2020-02-29'

The WHERE clause performs two critical filters. The equality predicate links each order to its corresponding product, establishing the join condition. The BETWEEN predicate restricts the rows to those whose order_date falls on or after February 1 2020 and on or before February 29 2020, thereby defining the reporting window.

GROUP BY p.product_name

After the rows have been filtered, the GROUP BY clause aggregates them by product name. All rows sharing the same product_name are collapsed into a single result row, and the SUM function computes the total units for each group.

HAVING SUM(o.unit) >= 100

The final phase applies a post-aggregation filter. The HAVING clause evaluates the summed units for each product group and retains only those groups whose total meets or exceeds the threshold of one hundred units. This ensures that the output contains only products with significant sales volume during the period.

Edge Cases Handled

The query’s construction implicitly addresses several boundary conditions. The inclusive nature of the BETWEEN operator guarantees that orders placed exactly on the first or last day of February 2020 are counted, preventing accidental omission of edge-date transactions. The join condition p.product_id = o.product_id ensures that orders lacking a corresponding product entry are excluded, which protects against orphaned order records that could otherwise distort the aggregation. The HAVING clause uses a greater-than-or-equal comparison, so a product with precisely one hundred units sold is retained, handling the case where the total equals the threshold.

Performance Optimizations

Although the query employs the older comma-style join syntax, modern relational engines rewrite it internally to an explicit inner join, allowing the optimizer to choose the most efficient join strategy. The presence of an equality predicate on product_id enables the use of an index on that column in both the Products and Orders tables, reducing the cost of locating matching rows. The date range filter on order_date can be satisfied by an index on the Orders table’s order_date column, allowing the engine to quickly prune rows outside the February window before the join occurs. Grouping by product_name benefits from any existing index on that column, which can accelerate the grouping operation by providing pre-sorted input. Finally, the HAVING clause is evaluated after aggregation, allowing the engine to discard low-volume groups early in the processing pipeline, thereby limiting the amount of data that must be materialized for the final result set.

Imagine a big toy store that keeps two special notebooks. The first notebook has a list of every toy with a secret number to identify it. The second notebook keeps track of every time someone buys a toy, writing down which toy was picked and how many were taken.

To find the most popular toys, this code acts like a helper who looks through both notebooks at the same time. First, the helper finds the matching secret numbers so it knows exactly which toy was sold. Since the store only wants to know about toys bought in February, the helper skips any sales that happened in other months.

Next, the helper gathers all the sales for each toy and adds up the numbers to see the grand total for the whole month. Finally, the helper looks at the pile of totals and sets aside the toys that sold ten or more times so the toy store knows which ones were the big winners.

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