Generate the Invoice - Problem

You are given two tables: Products and Purchases.

The Products table contains product information with unique product IDs and their unit prices.

The Purchases table contains invoice details showing the quantity of each product ordered in different invoices.

Write a SQL query to find the details of the invoice with the highest total price. If multiple invoices have the same highest price, return the one with the smallest invoice_id.

Return the result showing each product in that invoice with columns: product, quantity, price (unit price), and price * quantity (total for that product).

Table Schema

Products
Column Name Type Description
product_id PK int Unique product identifier
price int Unit price of the product
Primary Key: product_id
Purchases
Column Name Type Description
invoice_id PK int Invoice identifier
product_id PK int Product identifier (foreign key)
quantity int Quantity of product ordered
Primary Key: (invoice_id, product_id)

Input & Output

Example 1 — Basic Invoice Comparison
Input Tables:
Products
product_id price
1 10
2 25
3 15
4 2
Purchases
invoice_id product_id quantity
1 1 12
1 2 3
2 2 10
2 4 15
3 3 1
Output:
product quantity price total
2 10 25 250
4 15 2 30
💡 Note:

Invoice totals: Invoice 1 = (12×10 + 3×25) = 195, Invoice 2 = (10×25 + 15×2) = 280, Invoice 3 = (1×15) = 15. Invoice 2 has the highest total (280), so we return its product details.
Example 2 — Tie Breaker with Smallest ID
Input Tables:
Products
product_id price
1 20
2 50
Purchases
invoice_id product_id quantity
3 1 5
1 2 2
Output:
product quantity price total
2 2 50 100
💡 Note:

Invoice totals: Invoice 1 = (2×50) = 100, Invoice 3 = (5×20) = 100. Both have the same total, but invoice 1 has the smaller invoice_id, so we return invoice 1's details.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 1 ≤ price ≤ 1000
  • 1 ≤ invoice_id ≤ 1000
  • 1 ≤ quantity ≤ 1000

Visualization

Tap to expand
Generate the Invoice: Find Highest Value InvoiceInput: Products & Purchasesproduct_idprice110225invoice_idproduct_idqty11122210JOIN & SUMInvoice Totalsinvoice_idtotal_price11952280MAXInvoice 2 Detailsproductquantity210415Invoice 2 has the highest total price (280), so we return its product details
Understanding the Visualization
1
Join Tables
Combine Purchases and Products
2
Calculate Totals
Sum price × quantity per invoice
3
Find Maximum
Get invoice with highest total (tie-breaker: min ID)
4
Return Details
Show product details for winning invoice
Key Takeaway
🎯 Key Insight: Use window functions or subqueries to rank invoices by total price, then join back for product details

Related Problems

Asked in
Amazon 12 Microsoft 8 Apple 6
24.5K Views
Medium Frequency
~18 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen