Generate the Invoice - Practice Coding Problems

Generate the Invoice - Problem

Database Hard

Generate the Invoice - Find the Most Expensive Invoice

You're working for a billing system that needs to identify the highest-value invoice from purchase records. Given two tables:

Products table contains product information:
• product_id: Unique identifier for each product
• price: Price per unit of the product

Purchases table contains invoice details:
• invoice_id: Invoice identifier
• product_id: Product being purchased
• quantity: Number of units purchased

Goal: Find the invoice with the highest total value (sum of price × quantity for all products in that invoice). If multiple invoices have the same highest value, return the one with the smallest invoice_id.

Return all purchase details for the winning invoice, including product information and calculated totals.

Input & Output

example_1.sql — Basic Invoice Selection

$ Input: Products: [(1,10), (2,15), (3,20)] Purchases: [(101,1,5), (101,2,2), (102,1,7), (102,3,1)]

› Output: invoice_id=101, product_id=1, price=10, quantity=5, total=50 invoice_id=101, product_id=2, price=15, quantity=2, total=30

💡 Note: Invoice 101 total: (10×5 + 15×2) = $80. Invoice 102 total: (10×7 + 20×1) = $90. Invoice 102 has higher total, so return all its details.

example_2.sql — Tie-Breaking by Invoice ID

$ Input: Products: [(1,25), (2,30)] Purchases: [(201,1,2), (202,2,2), (203,1,1)]

› Output: invoice_id=201, product_id=1, price=25, quantity=2, total=50

💡 Note: Invoice 201: $50, Invoice 202: $60, Invoice 203: $25. Invoice 202 has the highest total ($60), so return its details.

example_3.sql — Equal Totals Edge Case

$ Input: Products: [(1,10), (2,5)] Purchases: [(301,1,4), (302,2,8), (303,1,2)]

› Output: invoice_id=301, product_id=1, price=10, quantity=4, total=40

💡 Note: Invoice 301: $40, Invoice 302: $40, Invoice 303: $20. Both 301 and 302 have equal highest totals ($40), but 301 has smaller ID, so return invoice 301 details.

Constraints

1 ≤ number of products ≤ 10⁴
1 ≤ number of purchases ≤ 10⁵
1 ≤ product_id ≤ 10⁶
1 ≤ price ≤ 10³
1 ≤ quantity ≤ 10²
Each invoice_id will have at least one purchase
All product_ids in Purchases table exist in Products table

Visualization

Tap to expand

Understanding the Visualization

Collect Receipt Data

Gather all purchase records with product prices from both tables

Calculate Totals

For each invoice, sum up all line items (price × quantity)

Find the Winner

Identify the invoice with highest total, using invoice_id for tie-breaking

Return Details

Fetch and format all line items for the winning invoice

Key Takeaway

🎯 Key Insight: Use CTEs to break down the problem into logical steps - calculate totals, find the maximum, then retrieve details. This approach is both efficient and easy to understand.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses Common Table Expressions (CTEs) to efficiently calculate invoice totals and identify the highest-value invoice. Key steps: 1) Join tables and calculate totals per invoice 2) Find maximum total with tie-breaking by smallest invoice_id 3) Return detailed records for the winning invoice. Time complexity: O(n) with excellent query optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Two-Pass)	O(n log n)	O(n)	Use window functions to rank invoices by total value efficiently
Optimized CTE (Single Query)	O(n)	O(k)	Use Common Table Expressions for efficient single-pass calculation
Nested Subqueries (Brute Force)	O(n²)	O(n)	Calculate all invoice totals, then find the maximum with separate queries

Window Functions (Two-Pass) — Algorithm Steps

JOIN tables and calculate individual line totals (price × quantity)
Use SUM() window function to calculate total per invoice
Apply RANK() window function ordered by total DESC, invoice_id ASC
Filter for rank = 1 to get the winning invoice

Visualization

Tap to expand

Step-by-Step Walkthrough

Join & Calculate

Join tables and calculate line totals

Partition Sum

Use window function to sum by invoice_id

Rank Invoices

Rank all invoices by total (desc) and id (asc)

Filter Top

Select only rank = 1 records

Code -

solution.c — C

// SQL solution using Window Functions - Two Pass

#include <stdio.h>

const char* getWindowFunctionQuery() {
    return 
        "WITH InvoiceDetails AS (\n"
        "    SELECT \n"
        "        p.invoice_id,\n"
        "        pr.product_id,\n"
        "        pr.price,\n"
        "        p.quantity,\n"
        "        (pr.price * p.quantity) as line_total,\n"
        "        SUM(pr.price * p.quantity) OVER (PARTITION BY p.invoice_id) as invoice_total\n"
        "    FROM Purchases p\n"
        "    JOIN Products pr ON p.product_id = pr.product_id\n"
        "),\n"
        "RankedInvoices AS (\n"
        "    SELECT *,\n"
        "        RANK() OVER (ORDER BY invoice_total DESC, invoice_id ASC) as rank\n"
        "    FROM InvoiceDetails\n"
        ")\n"
        "SELECT \n"
        "    invoice_id,\n"
        "    product_id,\n"
        "    price,\n"
        "    quantity,\n"
        "    line_total as total\n"
        "FROM RankedInvoices\n"
        "WHERE rank = 1\n"
        "ORDER BY product_id;";
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

One pass for JOIN and calculations, plus sorting for ranking

⚡ Linearithmic

Space Complexity

O(n)

Storage for intermediate results and ranking

⚡ Linearithmic Space

48.5K Views

High Frequency

~25 min Avg. Time

1.8K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Functions (Two-Pass) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler