101
0/304
Loading content...
A commerce intelligence team stores purchase line items in one table and product prices in another. Product managers need to know, for each customer, which products absorbed the highest total spend.
Table: Sales
Table: Product
Spend logic:
Task: For each user_id, return every product_id whose total spend is maximal for that user.
Tie behavior:
Output requirements:
Supported submission environments:
Sales:
| sale_id | product_id | user_id | quantity |
|---------|------------|---------|----------|
| 1 | 1 | 101 | 10 |
| 2 | 3 | 101 | 7 |
| 3 | 1 | 102 | 9 |
| 4 | 2 | 102 | 6 |
| 5 | 3 | 102 | 10 |
| 6 | 1 | 102 | 6 |
Product:
| product_id | price |
|------------|-------|
| 1 | 10 |
| 2 | 25 |
| 3 | 15 |[
{"user_id":101,"product_id":3},
{"user_id":102,"product_id":1},
{"user_id":102,"product_id":2},
{"user_id":102,"product_id":3}
]User 101 spends the most on product 3. User 102 ties at the same maximum spend on products 1, 2, and 3.
Sales:
| sale_id | product_id | user_id | quantity |
|---------|------------|---------|----------|
| 10 | 10 | 200 | 4 |
| 11 | 20 | 200 | 2 |
| 12 | 20 | 200 | 3 |
| 13 | 30 | 300 | 1 |
Product:
| product_id | price |
|------------|-------|
| 10 | 8 |
| 20 | 6 |
| 30 | 12 |[
{"user_id":200,"product_id":10},
{"user_id":300,"product_id":30}
]For user 200, product 10 totals 4*8 = 32 while product 20 totals (2+3)*6 = 30, so product 10 wins. User 300 has only product 30 purchases.
Sales:
| sale_id | product_id | user_id | quantity |
|---------|------------|---------|----------|
| 21 | 100 | 1 | 2 |
| 22 | 999 | 1 | 10 |
| 23 | 200 | 1 | 5 |
Product:
| product_id | price |
|------------|-------|
| 100 | 40 |
| 200 | 15 |[
{"user_id":1,"product_id":100}
]The sale referencing product 999 has no matching product price and is ignored by the join.
Constraints