User Spending Ranked by Basket Value

Easy

A commerce analytics team tracks purchase events and product pricing in two tables. Sales columns: sale_id: unique purchase event identifier product_id: purchased product identifier user_id: buyer identifier quantity: units bought in that event Product columns: product_id: unique product identifier price: per-unit price for the product Task: Compute total spending per user. Each sales row contributes quantity * price to that user. Sum all contributions for the same user. Output requirements: Return exactly two columns in this order: user_id, spending Sort by spending in descending order. If two users have equal spending, place the smaller user_id first. 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       | 2          | 101     | 1        |
| 3       | 3          | 102     | 3        |
| 4       | 3          | 102     | 2        |
| 5       | 2          | 103     | 3        |

Product:
| product_id | price |
|------------|-------|
| 1          | 10    |
| 2          | 25    |
| 3          | 15    |

Output

[
  {"user_id":101,"spending":125},
  {"user_id":102,"spending":75},
  {"user_id":103,"spending":75}
]

Explanation

User 101 spends 10*10 + 1*25 = 125. Users 102 and 103 both spend 75, so user_id ascending breaks the tie.

Example 2

Input

Sales:
| sale_id | product_id | user_id | quantity |
|---------|------------|---------|----------|
| 11      | 10         | 7       | 2        |
| 12      | 20         | 7       | 1        |
| 13      | 10         | 8       | 3        |
| 14      | 30         | 8       | 2        |

Product:
| product_id | price |
|------------|-------|
| 10         | 40    |
| 20         | 10    |
| 30         | 5     |
| 40         | 99    |

Output

[
  {"user_id":8,"spending":130},
  {"user_id":7,"spending":90}
]

Explanation

Only product_ids present in Sales contribute. Product 40 is unused and does not affect totals.

Example 3

Input

Sales:
[]

Product:
| product_id | price |
|------------|-------|
| 1          | 100   |
| 2          | 200   |

Output

[]

Explanation

With no purchase rows, no user has spending to report.

Constraints

0 <= number of rows in Sales <= 10000
1 <= number of rows in Product <= 3000
1 <= sale_id <= 2000000000
1 <= product_id <= 2000000000
1 <= user_id <= 2000000000
1 <= quantity <= 100000
1 <= price <= 1000000
Sales.sale_id values are unique
Product.product_id values are unique
Every Sales.product_id exists in Product
Return exactly two columns: user_id, spending
Sort by spending DESC, then user_id ASC
For Pandas submissions, return a DataFrame with columns: user_id, spending

Sales: | sale_id | product_id | user_id | quantity | |---------|------------|---------|----------| | 1 | 1 | 101 | 10 | | 2 | 2 | 101 | 1 | | 3 | 3 | 102 | 3 | | 4 | 3 | 102 | 2 | | 5 | 2 | 103 | 3 | Product: | product_id | price | |------------|-------| | 1 | 10 | | 2 | 25 | | 3 | 15 |

Sales: | sale_id | product_id | user_id | quantity | |---------|------------|---------|----------| | 11 | 10 | 7 | 2 | | 12 | 20 | 7 | 1 | | 13 | 10 | 8 | 3 | | 14 | 30 | 8 | 2 | Product: | product_id | price | |------------|-------| | 10 | 40 | | 20 | 10 | | 30 | 5 | | 40 | 99 |