Highest-Value Invoice Line Breakdown

Hard

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 1

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 2

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 3

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.

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

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 |

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 |

Products: | product_id | price | |------------|-------| | 100 | 30 | | 200 | 20 | Purchases: | invoice_id | product_id | quantity | |------------|------------|----------| | 50 | 100 | 3 | | 50 | 999 | 7 | | 51 | 200 | 4 |