Highest-Value Invoice Line Breakdown

HARD30 pts

A billing intelligence platform stores unit prices in one table and invoice lines in another. Finance operations needs a query that pinpoints the single most expensive invoice and then emits all of its line-level details.

Table: Products

product_id: unique product identifier
price: unit price for that product

Table: Purchases

invoice_id: invoice identifier
product_id: product purchased on this invoice line
quantity: units for this line
(invoice_id, product_id) is unique within Purchases

Computation logic:

Each line contributes line_amount = quantity * Products.price.
Invoice total is the sum of line_amount across all lines sharing invoice_id.

Selection rule:

Choose the invoice with the highest invoice total.
If multiple invoices share that maximum total, choose the one with the smallest invoice_id.

Task: Return all lines for the selected invoice with columns:

product_id
quantity
price (where this is line_amount, not unit price)

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

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

Purchases:
| invoice_id | product_id | quantity |
|------------|------------|----------|
| 1          | 1          | 2        |
| 3          | 2          | 1        |
| 2          | 2          | 3        |
| 2          | 1          | 4        |
| 4          | 1          | 10       |

Output

[
  {"product_id":1,"quantity":4,"price":400},
  {"product_id":2,"quantity":3,"price":600}
]

Explanation

Invoice totals are: #1=200, #2=1000, #3=200, #4=1000. Invoices 2 and 4 tie at the maximum, so invoice 2 is selected because it has the smaller invoice_id.

Example

Input

Products:
| product_id | price |
|------------|-------|
| 10         | 25    |
| 20         | 40    |
| 30         | 10    |

Purchases:
| invoice_id | product_id | quantity |
|------------|------------|----------|
| 7          | 10         | 4        |
| 7          | 30         | 9        |
| 8          | 20         | 2        |
| 8          | 30         | 1        |
| 9          | 10         | 1        |
| 9          | 20         | 1        |

Output

[
  {"product_id":10,"quantity":4,"price":100},
  {"product_id":30,"quantity":9,"price":90}
]

Explanation

Invoice 7 totals 190, invoice 8 totals 90, and invoice 9 totals 65. Only invoice 7 is maximal, so its lines are returned.

Example

Input

Products:
| product_id | price |
|------------|-------|
| 100        | 30    |
| 200        | 20    |

Purchases:
| invoice_id | product_id | quantity |
|------------|------------|----------|
| 50         | 100        | 3        |
| 50         | 999        | 7        |
| 51         | 200        | 4        |

Output

[
  {"product_id":100,"quantity":3,"price":90}
]

Explanation

Line 50-999 has no matching product price and is naturally excluded by an inner join. Invoice 50 contributes 90 and invoice 51 contributes 80, so invoice 50 wins.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Products <= 2000
0 <= number of rows in Purchases <= 10000
1 <= product_id <= 1000000000
1 <= invoice_id <= 1000000000
1 <= quantity <= 100000
1 <= price <= 100000
Products.product_id values are unique
(invoice_id, product_id) pairs are unique in Purchases
Return columns exactly as: product_id, quantity, price
Output column price must be quantity * unit_price for each selected row
Choose smallest invoice_id when max invoice totals tie
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exact output schema

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Products =

[{"product_id":1,"price":100},{"product_id":2,"price":200}]

Purchases =

[{"invoice_id":1,"product_id":1,"quantity":2},{"invoice_id":3,"product_id":2,"quantity":1},{"invoice_id":2,"product_id":2,"quantity":3},{"invoice_id":2,"product_id":1,"quantity":4},{"invoice_id":4,"product_id":1,"quantity":10}]

Highest-Value Invoice Line Breakdown

Hints

Highest-Value Invoice Line Breakdown

Hints