Product Sales Analysis III - Solution & Explanation
Problem Statement
Table: Sales
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| year | int |
| quantity | int |
| price | int |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.
Table: Product
+--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id is the primary key (column with unique values) of this table. Each row of this table indicates the product name of each product.
Write a solution to select the product id, year, quantity, and price for the first year of every product sold.
Return the resulting table in any order.
The result format is in the following example.
Example 1:
Input: Sales table: +---------+------------+------+----------+-------+ | sale_id | product_id | year | quantity | price | +---------+------------+------+----------+-------+ | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | +---------+------------+------+----------+-------+ Product table: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | +------------+--------------+ Output: +------------+------------+----------+-------+ | product_id | first_year | quantity | price | +------------+------------+----------+-------+ | 100 | 2008 | 10 | 5000 | | 200 | 2011 | 15 | 9000 | +------------+------------+----------+-------+
Approach Overview
Problem Overview: You are given a Sales table that records product sales by year. The goal is to return the first year each product was sold along with the corresponding quantity and price. Only the row representing the earliest year per product_id should appear in the result.
Approach 1: SQL Aggregation and Filtering (O(n) time, O(k) space)
Group records by product_id and compute the earliest year using MIN(year). This aggregation identifies the first sales year for each product. Then filter the original table by matching product_id and the computed minimum year to retrieve the correct quantity and price. This approach relies on standard SQL aggregation and works efficiently when the dataset is indexed by product or year. It is a straightforward application of SQL aggregation with a filtering step.
Approach 2: Nested Query and Join (O(n) time, O(k) space)
Create a derived table that calculates MIN(year) for each product_id. Then join this result back with the Sales table using both product_id and year. The join ensures that only rows matching the first sales year are returned. This pattern is common in database queries where aggregated values must be mapped back to full records.
Approach 3: SQL Subquery (O(n) time, O(1) extra space)
Use a correlated subquery inside the WHERE clause. For each row in Sales, compute the minimum year for that product using SELECT MIN(year) FROM Sales WHERE product_id = .... Keep only rows where the row's year matches that minimum value. This approach is compact and expressive but may run slightly slower on very large datasets because the subquery is evaluated per row. Databases with strong query optimization typically convert it internally into an efficient execution plan.
Approach 4: SQL Common Table Expression (CTE) (O(n) time, O(k) space)
Define a WITH clause that calculates the earliest year per product. The CTE stores product_id and first_year using GROUP BY and MIN(year). Then join the CTE with the original table to fetch the associated quantity and price. CTEs improve readability for complex queries and are widely used in analytical SQL workflows involving aggregation.
Recommended for interviews: The aggregation + join approach or the CTE solution is usually preferred. Both clearly separate computing the earliest year from retrieving the full row. Writing a brute-force correlated subquery shows understanding of SQL filtering logic, but the grouped aggregation pattern demonstrates stronger query design and scalability.
Approach 1: SQL Aggregation and Filtering
This approach involves using an aggregate function to determine the first year a product was sold, and then filtering the sales data to include only those records with the corresponding year. This approach leverages SQL's ability to aggregate and join tables, making it efficient for relational data.
The SQL query first calculates the earliest year of sale for each product ID using a subquery. The result of this subquery is then joined with the Sales table to filter out records that match these years, effectively selecting only the first year's records for each product.
Code
SQL
Complexity
Time Complexity: O(N log N), due to the aggregation and join operations.
Space Complexity: O(N), due to storing intermediate results in the join.
Approach 2: Nested Query and Join
This approach uses a nested query to find the minimum year for each product, which is then used to filter the main sales data. This results in extracting the desired columns for each product's first sale year.
This solution finds the smallest year for each product ID using a subquery and applies an IN clause to filter records in the Sales table. The result is that only records meeting the criteria of being the first sale year for their respective product are included.
Code
SQL
Complexity
Time Complexity: O(N log N), primarily due to the aggregation and filtering operations.
Space Complexity: O(N), as temporary storage is used to hold the results of the subquery.
Approach 3: Approach using SQL Subquery
To solve this problem, we need to extract details for the first year when each product was sold. We can do this by selecting the minimum year for each product from the 'Sales' table and joining it back to the original data to filter the first year details. Using a subquery will help us identify the first sale year for each product.
This SQL solution involves a subquery that finds the minimum year for each product_id by grouping them and calculates the minimum year as the first_year. The main query retrieves sale details matching this first year condition.
Code
SQL
Complexity
Time Complexity: O(n + m), where n is the number of rows in the main table and m in the subquery; Space Complexity: O(n), due to storing results of the distinct years per product.
Approach 4: Approach using SQL Common Table Expression (CTE)
Another way to solve this problem is by utilizing Common Table Expressions (CTEs). By employing CTEs, we can first compute the minimum year for each product_id and then join this result with the original Sales table to get the desired data for each product's initial sale year.
This SQL solution uses a CTE to first calculate the minimum year for each product_id. Then it joins this result back with the Sales table to filter out the relevant rows corresponding to that first year.
Code
SQL
Complexity
Time Complexity: O(n), due to a single pass over the Sales data; Space Complexity: O(n), mainly due to intermediate CTE storage.
Approach 5: Default Approach
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| SQL Aggregation and Filtering | Time Complexity: O(N log N), due to the aggregation and join operations. |
| Nested Query and Join | Time Complexity: O(N log N), primarily due to the aggregation and filtering operations. |
| Approach using SQL Subquery | Time Complexity: O(n + m), where n is the number of rows in the main table and m in the subquery; Space Complexity: O(n), due to storing results of the distinct years per product. |
| Approach using SQL Common Table Expression (CTE) | Time Complexity: O(n), due to a single pass over the Sales data; Space Complexity: O(n), mainly due to intermediate CTE storage. |
| Default Approach | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Aggregation and Filtering | O(n) | O(k) | General case when computing earliest values per group |
| Nested Query and Join | O(n) | O(k) | When aggregated results must be joined back to full rows |
| SQL Subquery | O(n) | O(1) | Short queries or when using correlated filtering logic |
| SQL Common Table Expression (CTE) | O(n) | O(k) | Readable query structure for analytics and complex SQL pipelines |
Video Solution
Product Sales Analysis III | Leetcode 1070 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 11,752 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Product Sales Analysis III easy or hard?
Product Sales Analysis III Python/Java solution
How to solve Product Sales Analysis III in O(n)?
What is the best approach for Product Sales Analysis III?
Is Product Sales Analysis III asked at Google/Amazon/Meta?
What data structure is used in Product Sales Analysis III?
What is the time complexity of Product Sales Analysis III?
Ready to solve this problem?
Practice Product Sales Analysis III with our built-in code editor and test cases.
Practice on FleetCode