Product Sales Analysis II solution and explanation
4 more videos on this problem
LeetCode 1069 Amazon Interview SQL Question with Detailed Explanation | Practice SQLEveryday Data Science · 3:17 · 3K views
LeetCode 1069: Product Sales Analysis II [SQL]Frederik Müller · 2:17 · 2K views
SQL LeetCode 1069 Product Sales Analysis IIAjay Thakur · 5:54 · 28 views
Product Sales Analysis I II SQL 50 || Leetcode || #sql #leetcode #sql50serieslost set bit · 5:48 · 23 views
猩猩的乐园 Leetcode 1069 Product Sales Analysis II猩猩的乐园 · 1:29 · 20 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 that reports the total quantity sold for every product id.
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 | total_quantity | +--------------+----------------+ | 100 | 22 | | 200 | 15 | +--------------+----------------+
Approach overview
Problem Overview: You are given a Sales table where each row represents a sale of a product in a particular year with its quantity and price. The task is to compute the total quantity sold for each product_id. The output should list every product along with the sum of quantities across all its sales records.
Approach 1: SQL Aggregation with GROUP BY (O(n) time, O(k) space)
The most direct way to solve this problem is using SQL aggregation. Scan the Sales table and group rows by product_id. For each group, apply the SUM() aggregate function on the quantity column to compute the total number of units sold. Databases typically implement GROUP BY using a hash aggregation or sorting strategy internally. The query processes each row once, giving O(n) time complexity where n is the number of sales records. The extra memory required is proportional to the number of distinct products (k), resulting in O(k) space.
This approach relies on fundamental SQL operations and is the expected solution for most database interviews. The key idea is recognizing that the problem is purely an aggregation task rather than a join or filtering problem. Using GROUP BY product_id automatically groups all related rows so the database can compute the total quantity for each product efficiently.
Approach 2: Aggregation with Join (O(n) time, O(k) space)
In some variations of the product sales analysis problems, you may need additional attributes such as the product name from the Product table. In that case, you join Sales with Product on product_id and then perform the same aggregation. After the join, apply GROUP BY product_id and calculate SUM(quantity). The database optimizer usually pushes aggregation efficiently after the join, keeping the overall complexity close to O(n).
This variation is useful when the output requires descriptive product data instead of just IDs. The core logic still centers on SQL aggregation and grouping operations.
Both approaches rely on concepts from database querying, SQL, and aggregation. Mastering GROUP BY patterns is critical because many real-world analytics queries follow the same structure: scan records, group them by a key, and compute metrics like SUM, COUNT, or AVG.
Recommended for interviews: The simple GROUP BY aggregation is the expected solution. Interviewers want to see that you immediately recognize this as a grouping problem and apply SUM(quantity) efficiently. More complex joins are unnecessary unless the problem explicitly requires data from another table.
Solution
# Write your MySQL query statement below
SELECT product_id, SUM(quantity) AS total_quantity
FROM Sales
GROUP BY 1;Complexity analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY Aggregation | O(n) | O(k) | Standard case when only product_id and total quantity are required |
| Aggregation with JOIN | O(n) | O(k) | When additional product information (like product_name) is needed from another table |
Frequently asked questions
Is Product Sales Analysis II easy or hard?
Product Sales Analysis II is classified as an Easy problem. It focuses on basic SQL aggregation using GROUP BY and SUM, which are core concepts in database querying and analytics.
Product Sales Analysis II Python/Java solution
This problem is designed for SQL rather than general-purpose languages like Python or Java. The solution is typically written as a MySQL query using GROUP BY and SUM to aggregate the quantity column for each product_id.
How to solve Product Sales Analysis II in O(n)?
Use a GROUP BY query on the Sales table. Aggregate rows by product_id and apply SUM(quantity) to compute the total sold units. SQL engines typically perform this using hash aggregation, allowing the query to process the dataset in linear time relative to the number of rows.
What is the best approach for Product Sales Analysis II?
The best approach is using SQL aggregation with GROUP BY. Group rows in the Sales table by product_id and compute SUM(quantity) for each group. This scans the table once and efficiently calculates the total quantity sold per product.
Is Product Sales Analysis II asked at Google/Amazon/Meta?
Database aggregation questions similar to Product Sales Analysis II appear in interviews at companies like Amazon and Meta. They test understanding of SQL fundamentals such as GROUP BY, aggregate functions, and efficient table scanning.
What data structure is used in Product Sales Analysis II?
Internally, most SQL engines use a hash table or sorting mechanism to implement GROUP BY aggregation. The hash table maps each product_id to its running total quantity while scanning the table.
What is the time complexity of Product Sales Analysis II?
The query runs in O(n) time where n is the number of rows in the Sales table. The database scans each record once and groups them by product_id. Space complexity is O(k) where k is the number of distinct products stored during aggregation.