Product Sales Analysis IV
ASKED IN INTERVIEWWalmart
Problem Statement
Walmart wants to understand customer loyalty toward specific products. Write an SQL query that finds, for each user, the product_id that they spent the most money on.
Total spending for a product is calculated as the sum of (unit_price * quantity) across all sales of that product for that user.
Rules:
- If there is a tie for the most money spent by a user on multiple products, return all such products.
- Output columns: user_id, product_id.
- Results must be sorted by user_id in ascending order, then product_id in ascending order.
Tests your understanding of
Window Functions, Ranking and Aggregation
Input Tables
sales
| sale_id(INTEGER) | product_id(INTEGER) | user_id(INTEGER) | quantity(INTEGER) |
|---|---|---|---|
| 1 | 10 | 1 | 2 |
| 2 | 20 | 1 | 1 |
| 3 | 10 | 2 | 5 |
| 4 | 30 | 2 | 1 |
product
| product_id(INTEGER) | price(INTEGER) |
|---|---|
| 10 | 100 |
| 20 | 500 |
| 30 | 1000 |
store_locations
| store_id(INTEGER) | city(VARCHAR) |
|---|---|
| 501 | Bentonville |
| 502 | Dallas |
Expected Output
| user_id(INTEGER) | product_id(INTEGER) |
|---|---|
| 1 | 20 |
| 2 | 30 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingAggregation
20-25 min
58%