Product Sales Analysis V

Problem

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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32


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.

Solution

Method 1 – Join, Multiply, and Group By

Intuition

We need to compute the total spending for each user by multiplying quantity and price for each sale, then summing by user. This is a join and group-by-aggregate problem.

Approach

Join Sales and Product on product_id.
For each sale, compute quantity * price.
Group by user_id and sum the spending.
Order by spending descending, then user_id ascending.

Code

1
2
3
4
5


SELECT s.user_id, SUM(s.quantity * p.price) AS spending
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
GROUP BY s.user_id
ORDER BY spending DESC, s.user_id ASC;

1
2
3
4
5


SELECT s.user_id, SUM(s.quantity * p.price) AS spending
FROM Sales s
JOIN Product p ON s.product_id = p.product_id
GROUP BY s.user_id
ORDER BY spending DESC, s.user_id ASC;

1
2
3
4
5
6
7
8


# Assume Sales and Product are pandas DataFrames
import pandas as pd
def product_sales_analysis_v(Sales: pd.DataFrame, Product: pd.DataFrame) -> pd.DataFrame:
    merged = Sales.merge(Product, on='product_id')
    merged['spending'] = merged['quantity'] * merged['price']
    result = merged.groupby('user_id')['spending'].sum().reset_index()
    result = result.sort_values(['spending', 'user_id'], ascending=[False, True]).reset_index(drop=True)
    return result

Complexity

⏰ Time complexity: O(N) where N is the number of rows in Sales.
🧺 Space complexity: O(U) where U is the number of users.

Problem#

Examples#

Solution#

Method 1 – Join, Multiply, and Group By#

Intuition#

Approach#

Code#

Complexity#