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 saleproduct_id(int): Foreign key to Product tableuser_id(int): ID of the user making the purchasequantity(int): Number of products purchased
Product Table:
product_id(int): Unique identifier for each productprice(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
Understanding the Visualization
1
Input Tables
Sales records and Product prices
2
JOIN Operation
Connect sales with product prices
3
Group & Sum
Calculate total spending per user
Key Takeaway
🎯 Key Insight: Use INNER JOIN to connect related tables, then GROUP BY with aggregate functions to calculate totals per category
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code