Skip to main content
Talentd

Product Sales Analysis IV - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Amazon
Practice this problem

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

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
GROUP BY with ORDER BYO(n)O(k)When only the single highest revenue product is required
GROUP BY + Window Function RankO(n)O(k)Best general solution when ties or top-K results must be handled

Video Solution

AMAZON LeetCode Medium 2324 “Product Sales Analysis IV" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,304 views views

Frequently Asked Questions

Is Product Sales Analysis IV easy or hard?
Product Sales Analysis IV is rated Medium. The aggregation step is straightforward, but correctly using ranking functions to handle ties and return the highest revenue product requires familiarity with SQL analytical functions.
Product Sales Analysis IV Python/Java solution
This problem is a database question, so the primary solution is written in SQL (MySQL on FleetCode). Python or Java would typically execute the SQL query against the database rather than implement the aggregation logic manually.
How to solve Product Sales Analysis IV in O(n)?
Scan the Sales table once and compute SUM(quantity * price) grouped by product_id. Then apply a ranking function like RANK() over the aggregated totals ordered by revenue descending. Filtering rank = 1 returns the product with the maximum total sales.
What is the best approach for Product Sales Analysis IV?
The best approach aggregates revenue per product using SUM(quantity * price) and then ranks products using a window function such as RANK() or DENSE_RANK(). Filtering for rank = 1 returns the product with the highest total revenue. This solution runs in O(n) time over the Sales table.
Is Product Sales Analysis IV asked at Google/Amazon/Meta?
SQL aggregation and ranking problems like this frequently appear in database interviews at companies such as Amazon, Meta, and analytics-focused roles at Google. Candidates are often expected to combine GROUP BY with window functions.
What data structure is used in Product Sales Analysis IV?
The solution relies on SQL aggregation rather than traditional data structures. Internally, the database engine uses hash or sort-based grouping to compute SUM values and window function ranking to order the results.
What is the time complexity of Product Sales Analysis IV?
The query requires scanning the Sales table and aggregating rows by product_id. This results in O(n) time complexity where n is the number of sales records. Space complexity is O(k) for storing aggregated totals for each distinct product.

Ready to solve this problem?

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

Practice on FleetCode
Previous

Product Sales Analysis I

Next

Product Sales Analysis II