Skip to main content
Talentd

Product Sales Analysis II - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

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 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

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
GROUP BY AggregationO(n)O(k)Standard case when only product_id and total quantity are required
Aggregation with JOINO(n)O(k)When additional product information (like product_name) is needed from another table

Video Solution

LeetCode 1069 Amazon Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 3,144 views views

Watch 4 more video solutions →

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.

Ready to solve this problem?

Practice Product Sales Analysis II with our built-in code editor and test cases.

Practice on FleetCode
Previous

Product Sales Analysis I

Next

Product Sales Analysis III