User Spending Ranked by Basket Value

EASY10 pts

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

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 1010 + 125 = 125. Users 102 and 103 both spend 75, so user_id ascending breaks the tie.

Example

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

Input

Sales:
[]

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

Output

[]

Explanation

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

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Sales =

[{"sale_id":1,"product_id":1,"user_id":101,"quantity":10},{"sale_id":2,"product_id":2,"user_id":101,"quantity":1},{"sale_id":3,"product_id":3,"user_id":102,"quantity":3},{"sale_id":4,"product_id":3,"user_id":102,"quantity":2},{"sale_id":5,"product_id":2,"user_id":103,"quantity":3}]

Product =

[{"product_id":1,"price":10},{"product_id":2,"price":25},{"product_id":3,"price":15}]

User Spending Ranked by Basket Value

Hints

User Spending Ranked by Basket Value

Hints