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 - Optimal Solution INPUT Invoices Table inv_id prod_id qty 1 101 2 1 102 3 2 101 5 Products Table prod_id price 101 50.00 102 30.00 JOIN tables on product_id Calculate: total = qty * price per invoice line item ALGORITHM STEPS 1 JOIN Tables Link invoices with products 2 Calculate Totals qty * unit_price per item 3 GROUP BY Invoice SUM totals per invoice_id 4 Find Maximum ORDER BY total DESC, id ASC Invoice Totals Calculation invoice_id total 1 190.00 2 250.00 Invoice 2 has MAX total Winner: ID 2 FINAL RESULT Invoice #2 Details Highest Total: $250.00 prod_id qty price total 101 5 50.00 250.00 Summary Invoice ID: 2 Grand Total: $250.00 OK - FOUND Output Format: {product_id, quantity, unit_price, total_price} Key Insight: Use JOIN to combine invoice items with product prices, then aggregate with SUM(qty * price). Handle ties by adding ORDER BY invoice_id ASC after sorting by total DESC. Use LIMIT 1 or subquery with MAX() to efficiently find the highest invoice and return all its line item details. TutorialsPoint - Generate the Invoice | Optimal Solution

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