Loading problem...
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
Table: Purchases
Computation logic:
Selection rule:
Task: Return all lines for the selected invoice with columns:
Supported submission environments:
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 |[
{"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.
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 |[
{"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.
Products:
| product_id | price |
|------------|-------|
| 100 | 30 |
| 200 | 20 |
Purchases:
| invoice_id | product_id | quantity |
|------------|------------|----------|
| 50 | 100 | 3 |
| 50 | 999 | 7 |
| 51 | 200 | 4 |[
{"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