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 (reference column) 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 that reports for each user the product id on which the user spent the most money. In case the same user spent the most money on two or more products, report all of them.
Return the resulting table in any 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 | 3 | 101 | 7 |
| 3 | 1 | 102 | 9 |
| 4 | 2 | 102 | 6 |
| 5 | 3 | 102 | 10 |
| 6 | 1 | 102 | 6 |
+---------+------------+---------+----------+
Product table:
+------------+-------+
| product_id | price |
+------------+-------+
| 1 | 10 |
| 2 | 25 |
| 3 | 15 |
+------------+-------+
Output:
+---------+------------+
| user_id | product_id |
+---------+------------+
| 101 | 3 |
| 102 | 1 |
| 102 | 2 |
| 102 | 3 |
+---------+------------+
Explanation:
User 101:
- Spent 10 * 10 = 100 on product 1.
- Spent 7 * 15 = 105 on product 3.
User 101 spent the most money on product 3.
User 102:
- Spent (9 + 6) * 10 = 150 on product 1.
- Spent 6 * 25 = 150 on product 2.
- Spent 10 * 15 = 150 on product 3.
User 102 spent the most money on products 1, 2, and 3.
Problem Overview: You are given product sales records containing product_id, quantity, and price. The goal is to compute the total sales value for each product and return the product (or products) with the highest overall revenue.
Approach 1: GROUP BY with Aggregation (O(n) time, O(k) space)
The straightforward way is to compute revenue per product using SQL aggregation. Iterate through the Sales table and calculate SUM(quantity * price) grouped by product_id. This produces one aggregated row per product. Once you have totals, order the results in descending order of revenue and return the top entry. The database engine performs a full scan of the table (O(n)) and stores aggregated results for each distinct product (O(k) space where k is the number of products). This approach works well when only the single highest revenue product is needed.
Approach 2: Window Function Ranking (O(n) time, O(k) space)
A more flexible solution uses a window function to rank products by total revenue. First compute revenue per product using GROUP BY. Then apply RANK() or DENSE_RANK() over the aggregated totals ordered descending. Filtering rows where rank equals 1 returns all products tied for the maximum revenue. This pattern is common in database interview problems because it separates aggregation from ranking logic. Window functions are efficient since the engine processes the aggregated dataset once while assigning ranks.
Window ranking becomes especially useful if the requirement changes to return the top K products or to keep ties. Instead of rewriting the query, you simply filter by rank. Understanding how aggregation interacts with ranking functions is a common requirement in SQL interview questions involving SQL analytics.
Recommended for interviews: Use the window function ranking approach. A basic GROUP BY solution shows you understand aggregation, but using RANK() or DENSE_RANK() demonstrates stronger SQL skills and handles edge cases like revenue ties cleanly. Interviewers frequently expect candidates to combine aggregation with analytical functions when solving database problems.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY with ORDER BY | O(n) | O(k) | When only the single highest revenue product is required |
| GROUP BY + Window Function Rank | O(n) | O(k) | Best general solution when ties or top-K results must be handled |
AMAZON LeetCode Medium 2324 “Product Sales Analysis IV" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,266 views views
Practice Product Sales Analysis IV with our built-in code editor and test cases.
Practice on FleetCode