Product Sales Analysis V - Solution & Explanation
Problem Statement
Table: Sales
+-------------+-------+
| Column Name | Type |
+-------------+-------+
| sale_id | int |
| product_id | int |
| user_id | int |
| quantity | int |
+-------------+-------+
sale_id contains unique values.
product_id is a foreign key (column with unique values) to Product table.
Each row of this table shows the ID of the product and the quantity purchased by a user.
Table: Product
+-------------+------+ | Column Name | Type | +-------------+------+ | product_id | int | | price | int | +-------------+------+ product_id contains unique values. Each row of this table indicates the price of each product.
Write a solution to report the spending of each user.
Return the resulting table ordered by spending in descending order. In case of a tie, order them by user_id in ascending order.
The result format is in the following example.
Example 1:
Input: Sales table: +---------+------------+---------+----------+ | sale_id | product_id | user_id | quantity | +---------+------------+---------+----------+ | 1 | 1 | 101 | 10 | | 2 | 2 | 101 | 1 | | 3 | 3 | 102 | 3 | | 4 | 3 | 102 | 2 | | 5 | 2 | 103 | 3 | +---------+------------+---------+----------+ Product table: +------------+-------+ | product_id | price | +------------+-------+ | 1 | 10 | | 2 | 25 | | 3 | 15 | +------------+-------+ Output: +---------+----------+ | user_id | spending | +---------+----------+ | 101 | 125 | | 102 | 75 | | 103 | 75 | +---------+----------+ Explanation: User 101 spent 10 * 10 + 1 * 25 = 125. User 102 spent 3 * 15 + 2 * 15 = 75. User 103 spent 3 * 25 = 75. Users 102 and 103 spent the same amount and we break the tie by their ID while user 101 is on the top.
Approach Overview
Problem Overview: You are given product information and sales records stored in relational tables. The task is to analyze the sales data and return aggregated results for each product by combining the relevant tables and computing metrics such as total quantity or sales value.
Approach 1: SQL JOIN with Aggregation (O(n) time, O(1) extra space)
The core idea is to combine the Sales table with the Product table using an inner join on product_id. This gives access to both the transactional sales data and the descriptive product fields in a single result set. After joining, iterate over the rows logically using SQL aggregation and compute metrics such as SUM() of quantities or revenue. The GROUP BY clause groups all sales rows belonging to the same product so the database can calculate totals per product.
If the problem requires filtering based on aggregated results, use a HAVING clause instead of WHERE. For example, you might filter products whose total quantity crosses a threshold or return only products that satisfy a specific sales condition. Sorting the final result with ORDER BY ensures deterministic output when required.
This approach works efficiently because relational databases are optimized for grouping and aggregation operations. The database engine scans the sales table once and performs grouping internally, giving an overall time complexity of O(n) where n is the number of sales records. Only constant additional memory is required for query execution, so the space complexity is O(1) outside the database engine’s internal buffers.
Conceptually, the problem relies on standard database techniques: joining normalized tables and aggregating transactional data. If you want to strengthen these skills, practice related topics like database queries, SQL aggregation, and grouping concepts similar to hash aggregation.
Recommended for interviews: The JOIN + GROUP BY aggregation query is the expected solution. It shows you understand how relational schemas separate entities (products) from transactions (sales) and how to recombine them for analytics. Even though the problem is categorized as easy, writing a clean aggregation query with correct grouping and filtering demonstrates strong SQL fundamentals.
Solution
Code
MySQL
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| JOIN with GROUP BY Aggregation | O(n) | O(1) | Standard solution when combining product metadata with sales records and computing totals. |
| Subquery with Aggregation | O(n) | O(1) | Useful when precomputing aggregated sales per product before joining with the product table. |
Video Solution
LeetCode 2329 "Product Sales Analysis V" Amazon Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,401 views views
Frequently Asked Questions
Is Product Sales Analysis V easy or hard?
Product Sales Analysis V Python/Java solution
How to solve Product Sales Analysis V in O(n)?
What is the best approach for Product Sales Analysis V?
Is Product Sales Analysis V asked at Google/Amazon/Meta?
What data structure is used in Product Sales Analysis V?
What is the time complexity of Product Sales Analysis V?
Ready to solve this problem?
Practice Product Sales Analysis V with our built-in code editor and test cases.
Practice on FleetCode