Product Sales Analysis IV - Problem

You have access to two tables: Sales and Product.

The Sales table contains information about each sale, including the product sold, user who made the purchase, and quantity bought. The Product table contains the price of each product.

Write a SQL query that finds, for each user, the product(s) on which they spent the most money. If a user spent the same maximum amount on multiple products, return all such products for that user.

Return the result in any order.

Table Schema

Sales
Column Name Type Description
sale_id PK int Unique sale identifier
product_id int Foreign key referencing Product table
user_id int User who made the purchase
quantity int Quantity of product purchased
Primary Key: sale_id
Product
Column Name Type Description
product_id PK int Unique product identifier
price int Price per unit of the product
Primary Key: product_id

Input & Output

Example 1 — Basic Product Spending Analysis
Input Tables:
Sales
sale_id product_id user_id quantity
1 1 1 2
2 3 3 7
3 1 1 1
4 2 2 2
5 2 1 3
Product
product_id price
1 10
2 25
3 15
Output:
user_id product_id
1 2
2 2
3 3
💡 Note:

User 1 spent: Product 1 = (2+1)*10 = 30, Product 2 = 3*25 = 75. Maximum is Product 2 (75).

User 2 spent: Product 2 = 2*25 = 50. Only product, so it's maximum.

User 3 spent: Product 3 = 7*15 = 105. Only product, so it's maximum.
Example 2 — Tied Maximum Spending
Input Tables:
Sales
sale_id product_id user_id quantity
1 1 1 3
2 2 1 2
3 3 2 1
Product
product_id price
1 20
2 30
3 40
Output:
user_id product_id
1 1
1 2
2 3
💡 Note:

User 1 spent: Product 1 = 3*20 = 60, Product 2 = 2*30 = 60. Both products tie for maximum (60), so both are returned.

User 2 spent: Product 3 = 1*40 = 40. Only product, so it's maximum.

Constraints

  • 1 ≤ sale_id, product_id, user_id ≤ 500
  • 1 ≤ quantity ≤ 100
  • 1 ≤ price ≤ 1000

Visualization

Tap to expand
Product Sales Analysis IV: Find Maximum Spending ProductsSales Tableuser_idproduct_idquantity112123Product Tableproduct_idprice110225JOIN &RANK()Result: Max Spend Productsuser_idproduct_id1222User 1: Product 2 (3×25 = 75) > Product 1 (2×10 = 20)User 2: Product 2 only option
Understanding the Visualization
1
Join Tables
Combine Sales and Product data
2
Calculate Spend
Sum quantity * price per user-product
3
Rank Products
Use RANK() to find maximum spend per user
Key Takeaway
🎯 Key Insight: Use RANK() window function to handle ties when finding maximum values per group

Related Problems

Asked in
Amazon 12 Microsoft 8 Google 6
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen