Abstract / Summary

Find the total sales for each product by joining relevant tables and grouping by product id.

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 46% (1+ million 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 query extracts the earliest sales record for each product from the Sales table. It first determines, per product, the minimum year in which a sale occurred. Then it joins this result back to the original table to retrieve the full row (quantity and price) that corresponds to that earliest year. The output columns are the product identifier, the first year of sale, the quantity sold, and the sale price.

Key Steps

Determining the first year per product

The common table expression (CTE) named initial aggregates the Sales table. It groups rows by product_id and computes the smallest year value for each group.

WITH initial AS (
    SELECT product_id,
           MIN(year) AS year
    FROM Sales
    GROUP BY product_id
)

This step isolates the temporal anchor for every product, ensuring that later processing can focus on a single year per product.

Joining back to retrieve the complete record

The main SELECT statement references both the Sales table (aliased as s) and the CTE initial (aliased as i). The join condition matches rows where the product identifiers are equal and the year matches the minimum year identified earlier.

SELECT s.product_id,
       s.year AS first_year,
       s.quantity,
       s.price
FROM Sales s,
     initial i
WHERE s.product_id = i.product_id
  AND s.year = i.year;

Because the join uses equality on both product_id and year, the result set contains exactly the rows that represent the first recorded sale for each product.

Edge Cases Handled

When a product has multiple sales entries in the same earliest year, the query returns all those rows. This behavior is intentional: the join condition does not further restrict by additional columns such as quantity or price. Consequently, the output will include every record that shares the minimum year for a given product, preserving the full detail of each qualifying transaction.

If a product appears only once in the Sales table, that single row naturally satisfies the minimum-year condition and is returned. Conversely, products that never appear in the table are excluded automatically because the CTE contains only product identifiers present in Sales.

The use of MIN(year) guarantees that even if the year column contains null values, those nulls are ignored in the aggregation, and the smallest non-null year is selected. Rows where year is null will not match the join condition, so they are omitted from the final result.

Performance Optimizations

The query leverages a CTE to perform the aggregation once, rather than recomputing the minimum year for each row in a correlated subquery. This reduces the amount of work the database engine must perform, especially when the Sales table is large.

By joining on indexed columns-typically product_id and year-the database can use index lookups to locate matching rows efficiently. The grouping operation in the CTE benefits from an index on product_id (and optionally on year) to accelerate the GROUP BY and MIN calculation.

The explicit use of equality predicates in the WHERE clause enables the optimizer to apply hash or merge join strategies, which are generally faster than nested-loop joins for sizable datasets. The separation of aggregation and join phases also allows the optimizer to materialize the CTE once and reuse it, avoiding repeated scans of the Sales table.

Imagine a big toy box filled with many boxes of LEGO sets that were bought over several different years. Some of these sets were bought multiple times, so the box is a bit messy and spread out.

The first part of the code acts like a very organized helper who looks through all the toy records. This helper finds every single type of LEGO set and picks out the exact year it was first bought. It makes a little list that only shows the name of the toy and its very first birthday in the toy box.

The second part of the code then takes that list and goes back to the giant messy pile to match everything up. It looks at the toy records and keeps only the ones that match the special first-birthday list. By doing this, the code filters out all the extra times the same toy was bought later and keeps only the original information from the very first year.

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