Product Sales Analysis III solution and explanation
Google
9 more videos on this problem
Product Sales Analysis III | Leetcode 1070 | Crack SQL Interviews in 50 Qs #mysql #leetcodeLearn With Chirag · 6:04 · 11K views
LeetCode 1070: Product Sales Analysis III [SQL]Frederik Müller · 5:12 · 6K views
AMAZON LeetCode Medium “Product Sales Analysis III" 1070 Interview SQL Question Explanation | EDSEveryday Data Science · 5:59 · 3K views
1070. Product Sales Analysis III - LeetCode SQL Solution [MySQL]Code with Carter · 4:51 · 712 views
1070. Product Sales Analysis IIISheCodes · 5:45 · 300 views
1070. Product Sales Analysis III | Leetcode | HindiDataEngineering360 · 4:18 · 272 views
1070 Product Sales Analysis III | 64/100 Days Of SQL Challenge | LeetCodeEduZy · 10:43 · 133 views
Unlocking SQL Magic: Solving LeetCode's 'Product Sales Analysis III' Problem - 1070vanAmsen · 3:11 · 91 views
25. Leetcode 1070. Product Sales Analysis III : MYSQLWeb Fusion with Bharadwaj · 15:43 · 88 views
Leetcode 1070: Product Sales Analysis III Solution | SQL | Leetcode Interview QuestionThe Knowledge Academy · 3:18 · 54 views
The problem
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.
SELECT s.product_id, s.year AS first_year, s.quantity, s.price FROM Sales s INNER JOIN (SELECT product_id, MIN(year) AS first_year FROM Sales GROUP BY product_id) t ON s.product_id = t.product_id AND s.year = t.first_year;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.
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.
SELECT s.product_id, s.year AS first_year, s.quantity, s.price FROM Sales s WHERE (s.product_id, s.year) IN (SELECT product_id, MIN(year) FROM Sales GROUP BY product_id);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.
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.
SELECT s.product_id, s.year as first_year, s.quantity, s.price FROM Sales s WHERE (s.product_id, s.year) IN (SELECT product_id, MIN(year) FROM Sales GROUP BY product_id);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.
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.
WITH FirstSalesYear AS (SELECT product_id, MIN(year) as first_year FROM Sales GROUP BY product_id) SELECT s.product_id, s.year as first_year, s.quantity, s.price FROM Sales s JOIN FirstSalesYear fsy ON s.product_id = fsy.product_id AND s.year = fsy.first_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.
Time Complexity: O(n), due to a single pass over the Sales data; Space Complexity: O(n), mainly due to intermediate CTE storage.
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. |
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 |
Frequently asked questions
Is Product Sales Analysis III easy or hard?
Product Sales Analysis III is rated Medium on LeetCode. The difficulty comes from understanding how to return full rows after computing an aggregated value like MIN(year) for each product.
Product Sales Analysis III Python/Java solution
This problem is designed for SQL rather than general-purpose languages. Instead of Python or Java logic, the solution uses SQL queries with GROUP BY, subqueries, or CTEs to compute the earliest year for each product and return the corresponding row.
How to solve Product Sales Analysis III in O(n)?
Compute the earliest sales year using SELECT product_id, MIN(year) GROUP BY product_id. Then join this result with the Sales table on product_id and year to return the corresponding quantity and price. The database processes this aggregation in linear time relative to the table size.
What is the best approach for Product Sales Analysis III?
The most common approach uses SQL aggregation with MIN(year) grouped by product_id, then joins the result back to the Sales table to retrieve quantity and price. This approach runs in O(n) time and clearly separates computing the earliest year from selecting the full row.
Is Product Sales Analysis III asked at Google/Amazon/Meta?
SQL aggregation and grouping problems similar to this appear frequently in database interviews at companies like Amazon, Google, and Meta. Interviewers often test your ability to combine GROUP BY, subqueries, and joins to retrieve aggregated records.
What data structure is used in Product Sales Analysis III?
The problem relies on relational database tables and SQL operations such as GROUP BY, aggregation functions like MIN(), joins, and subqueries. Internally, the database may use hash aggregation or sorting structures to compute grouped results efficiently.
What is the time complexity of Product Sales Analysis III?
Most SQL solutions run in O(n) time where n is the number of rows in the Sales table. Aggregation with GROUP BY scans the table once, and the join or filtering step adds minimal overhead depending on indexing.