User Product Spend Leaderboard with Tie Support

Medium

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 sale_id: unique identifier for a purchase row product_id: purchased product identifier user_id: purchasing user identifier quantity: units purchased in that row Table: Product product_id: unique product identifier price: unit price of the product Spend logic: A row contributes quantity * price. A user's spend on a product is the sum of row spend over all rows sharing that (user_id, product_id). Task: For each user_id, return every product_id whose total spend is maximal for that user. Tie behavior: If two or more products are tied for the same user's highest spend, include all tied product_id values. Output requirements: Return exactly two columns: user_id, product_id Output order is not important Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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    |

Output

[
  {"user_id":101,"product_id":3},
  {"user_id":102,"product_id":1},
  {"user_id":102,"product_id":2},
  {"user_id":102,"product_id":3}
]

Explanation

User 101 spends the most on product 3. User 102 ties at the same maximum spend on products 1, 2, and 3.

Example 2

Input

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    |

Output

[
    {"user_id":200,"product_id":10},
  {"user_id":300,"product_id":30}
]

Explanation

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.

Example 3

Input

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    |

Output

[
  {"user_id":1,"product_id":100}
]

Explanation

The sale referencing product 999 has no matching product price and is ignored by the join.

Constraints

0 <= number of rows in Sales <= 5000
0 <= number of rows in Product <= 2000
1 <= sale_id <= 2000000000
1 <= product_id <= 2000000000
1 <= user_id <= 2000000000
1 <= quantity <= 200
1 <= price <= 100000
sale_id values are unique
product_id values are unique in Product
Rows in Sales may reference products absent from Product
Return columns exactly as: user_id, product_id
Include all products tied at each user's maximum spend
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: user_id, product_id

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 |

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 |

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 |