Product Sales Analysis V - Problem

Given two tables Sales and Product, write a SQL query to report the spending of each user.

Sales Table:

  • sale_id (int): Unique identifier for each sale
  • product_id (int): Foreign key to Product table
  • user_id (int): ID of the user making the purchase
  • quantity (int): Number of products purchased

Product Table:

  • product_id (int): Unique identifier for each product
  • price (int): Price of the product

Return the result ordered by spending in descending order. In case of a tie, order by user_id in ascending order.

Table Schema

Sales
Column Name Type Description
sale_id PK int Unique identifier for each sale
product_id int Foreign key to Product table
user_id int ID of the user making the purchase
quantity int Number of products purchased
Primary Key: sale_id
Product
Column Name Type Description
product_id PK int Unique identifier for each product
price int Price per unit of the product
Primary Key: product_id

Input & Output

Example 1 — Basic User Spending Calculation
Input Tables:
Sales
sale_id product_id user_id quantity
1 1 1 2
2 2 2 3
3 2 1 1
Product
product_id price
1 10
2 25
Output:
user_id spending
2 75
1 45
💡 Note:

User 1 spent: (2 × $10) + (1 × $25) = $45. User 2 spent: (3 × $25) = $75. Result is ordered by spending DESC: User 2 ($75) comes before User 1 ($45).
Example 2 — Tie Breaking by User ID
Input Tables:
Sales
sale_id product_id user_id quantity
1 1 3 5
2 1 1 5
Product
product_id price
1 10
Output:
user_id spending
1 50
3 50
💡 Note:

Both users spent the same amount: $50 (5 × $10). Since spending is tied, we order by user_id ASC: User 1 comes before User 3.
Example 3 — Single User Multiple Purchases
Input Tables:
Sales
sale_id product_id user_id quantity
1 1 1 1
2 2 1 2
3 1 1 1
Product
product_id price
1 15
2 20
Output:
user_id spending
1 70
💡 Note:

User 1 made multiple purchases: (1 × $15) + (2 × $20) + (1 × $15) = $70. All purchases are summed for the total spending.

Constraints

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

Visualization

Tap to expand
Product Sales Analysis V - SQL Solution INPUT TABLES Sales Table sale_id prod_id user_id qty 1 1 101 2 2 2 101 1 3 1 102 3 4 3 102 1 Product Table product_id price 1 100 2 200 3 150 Join on: product_id Sales.product_id = Product.product_id ALGORITHM STEPS 1 JOIN Tables Connect Sales with Product on matching product_id 2 Calculate Spending spending = quantity * price for each sale record 3 GROUP BY user_id SUM all spending per user using aggregate function 4 ORDER BY Result spending DESC, user_id ASC for proper ordering SQL QUERY SELECT user_id, SUM(quantity*price) AS spending FROM Sales s JOIN Product p ON s.product_id = p.product_id GROUP BY user_id ORDER BY spending DESC, user_id FINAL RESULT Output Table user_id spending 101 400 102 450 Spending Breakdown User 101: (2 x 100) + (1 x 200) = 400 User 102: (3 x 100) + (1 x 150) = 450 Ordered Result user_id spending 102 450 101 400 (DESC by spending) Key Insight: The solution uses JOIN to combine Sales and Product tables, then calculates total spending per user using SUM(quantity * price). GROUP BY aggregates per user, while ORDER BY ensures highest spenders appear first (DESC). Ties are resolved by user_id in ascending order (ASC). TutorialsPoint - Product Sales Analysis V | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Google 6
23.4K Views
Medium Frequency
~8 min Avg. Time
845 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