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 INPUT sale_id user_id prod_id qty 1 101 P1 3 2 101 P2 2 3 102 P1 5 4 102 P3 5 Product Table product_id price P1 $100 P2 $150 P3 $100 Sales + Products data with user purchases ALGORITHM STEPS 1 JOIN Tables Sales JOIN Product on product_id 2 Calculate Spending spent = qty * price per user, product 3 Find Max per User RANK() OVER (PARTITION BY user) 4 Filter Rank = 1 Keep products with highest spending WITH ranked AS ( SELECT user_id, product_id, RANK() OVER(...) WHERE rank = 1 FINAL RESULT user_id product_id 101 P1 102 P1 102 P3 User 101: P1: 3 x $100 = $300 P2: 2 x $150 = $300 Max: P1 (tie broken) User 102: P1: 5 x $100 = $500 P3: 5 x $100 = $500 Max: P1, P3 (both!) Key Insight: Use RANK() instead of ROW_NUMBER() to handle ties correctly. When multiple products have the same maximum spending for a user, RANK() assigns them all rank=1, while ROW_NUMBER() would arbitrarily pick one. This ensures ALL top-spending products are returned per user. TutorialsPoint - Product Sales Analysis IV | Optimal Solution

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