Generate the Invoice

Mechanism

Roll each purchase line up to a per-line subtotal (quantity * price), sum those subtotals per invoice_id, sort the invoices by total descending with invoice_id ascending as the deterministic tie-breaker, keep the single top row with LIMIT 1, then re-scan the line-level rows and emit only the lines whose invoice_id matches that winner. The trick is that you compute the winner on aggregated rows but you must return un-aggregated line detail, so the query splits into two passes over the same join: one to pick the invoice, one to project its lines.

The problem

Two tables. Products(product_id, price) gives the unit price; Purchases(invoice_id, product_id, quantity) lists how many units of each product an invoice ordered. Return the line detail (product_id, quantity, price, where price is the line subtotal, not the unit price) of the single invoice with the highest total. If two invoices tie on total, return the one with the smaller invoice_id. Result order is unconstrained.

Concrete inputs we will trace end to end:

This is built so invoice 2 (400 + 600 = 1000) and invoice 4 (10 × 100 = 1000) tie at 1000 — the tie-break must pick invoice 2.

The query

WITH details_by_invoices AS (
    SELECT
        pu.invoice_id,
        pu.product_id,
        pu.quantity,
        pu.quantity * pr.price AS price   -- line subtotal
    FROM Purchases AS pu
    INNER JOIN Products AS pr
        ON pu.product_id = pr.product_id
),
invoice_with_max_total AS (
    SELECT invoice_id
    FROM details_by_invoices
    GROUP BY invoice_id
    ORDER BY SUM(price) DESC,   -- highest total first
             invoice_id ASC     -- tie-break: smallest id
    LIMIT 1
)
SELECT product_id, quantity, price
FROM details_by_invoices
WHERE invoice_id IN (SELECT invoice_id FROM invoice_with_max_total);

Two CTEs: the first materializes every purchase line with its subtotal; the second collapses that to one row per invoice, orders, and slices off the single winner. The outer SELECT then reuses the first CTE — not the second — to recover the full line detail.

Worked trace

Stage 1 — details_by_invoices (join each purchase to its unit price, multiply by quantity):

Stage 2 — group and total (SUM(price) per invoice):

Stage 3 — order and slice. ORDER BY SUM(price) DESC floats invoices 2 and 4 (both 1000) to the top, tied. invoice_id ASC breaks the tie by putting 2 before 4. LIMIT 1 keeps invoice 2. Without the second sort key the tie would resolve arbitrarily and could return invoice 4 on some runs.

Stage 4 — project the winner's lines. Re-scan Stage 1, keep rows where invoice_id = 2:

Note the output price column is the line subtotal (400, 600), not the unit price (100, 200). The problem's column name price is deliberately overloaded.

Why the naive version is wrong

The tempting shortcut is to compute the max total once and filter invoices equal to it:

-- BUGGY when totals tie
SELECT product_id, quantity, price
FROM details_by_invoices
WHERE invoice_id IN (
    SELECT invoice_id FROM details_by_invoices
    GROUP BY invoice_id
    HAVING SUM(price) = (
        SELECT MAX(t) FROM (
            SELECT SUM(price) AS t FROM details_by_invoices GROUP BY invoice_id
        ) x
    )
);

On the trace data this returns both invoice 2 and invoice 4's lines, because both totals equal the max of 1000 — four rows instead of two, with no way to honor "smallest invoice_id". The ORDER BY ... DESC, invoice_id ASC LIMIT 1 idiom is what makes the choice singular and deterministic: it folds the "highest total" rule and the "smallest id on a tie" rule into one sort, then takes exactly one row.

The dialect-friendly choice: LIMIT 1 vs window RANK

The same winner can be selected with a window function, which some engineers reach for first:

-- window alternative
SELECT invoice_id FROM (
    SELECT invoice_id,
           RANK() OVER (ORDER BY SUM(price) DESC) AS rk
    FROM details_by_invoices
    GROUP BY invoice_id
) r
WHERE rk = 1
ORDER BY invoice_id
LIMIT 1;

This is correct but heavier, and the choice of ranking function matters: RANK() assigns rank 1 to both tied invoices, so you still need the outer ORDER BY invoice_id LIMIT 1 to single out invoice 2 — the window did not actually break the tie. ROW_NUMBER() OVER (ORDER BY SUM(price) DESC, invoice_id ASC) would break it inside the window, but at that point you have just re-implemented the simpler ORDER BY ... LIMIT 1 with extra machinery. The LIMIT 1 form is also the most portable: it runs unchanged on MySQL, PostgreSQL, and SQLite. SQL Server has no LIMIT — there you write SELECT TOP 1 ... ORDER BY SUM(price) DESC, invoice_id ASC, or OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY. Window functions also have a portability cliff: they are unavailable in MySQL before 8.0.

Pitfalls

• Forgetting the tie-break key. ORDER BY SUM(price) DESC LIMIT 1 alone is non-deterministic across tied totals. The engine may return invoice 4 today and invoice 2 tomorrow after a vacuum, reindex, or plan change. Always add , invoice_id ASC.
• Returning unit price instead of the line subtotal. If you select pr.price in the final output you emit 100 and 200, not 400 and 600. The computed quantity * price alias is what the grader expects.
• Integer overflow / silent truncation. Here price and quantity are INT, so quantity * price stays integral and SUM is exact. The moment unit price becomes DECIMAL (real money has cents) the subtotal is decimal and equality-based tie detection on totals can break: SUM of floating NUMERIC may differ by a least-significant digit, so two "equal" invoices might not compare equal. The ORDER BY ... LIMIT 1 form is robust to this because it never tests totals for equality — it only orders them. A wide INT sum can also overflow on large catalogs; cast to BIGINT/DECIMAL before summing if quantities are large.
• Aggregating away the detail too early. You cannot return per-line rows from the grouped CTE — it has one row per invoice. The pattern requires keeping the un-aggregated line CTE around and filtering it by the chosen invoice_id, which is exactly the two-pass structure.
• Assuming every product in Purchases exists in Products. An INNER JOIN silently drops purchase lines whose product_id has no matching product, understating that invoice's total. With clean FK-constrained data this is fine; on dirty data it is a real bug — a LEFT JOIN would surface the orphan (as a NULL price you must then handle).

Takeaways

• "Top-1 with deterministic tie-break" = ORDER BY metric DESC, tiebreak ASC LIMIT 1. One sort folds the primary rule and the tie rule together; LIMIT 1 makes the answer singular.
• When you need a winner's detail, keep two passes over the same join — one aggregated pass to pick the winner, one line-level pass to project it — instead of trying to do both in one grouped query.
• Prefer ordering over equality on aggregates. LIMIT 1 never compares totals for equality, so it survives the integer-to-decimal transition that breaks HAVING SUM = MAX(...) approaches.
• The LIMIT 1 idiom is the portable default; reach for window RANK/ROW_NUMBER only when you genuinely need the top-N per group, and remember RANK does not break ties on its own.

Based on the LeetCode Database problem "Generate the Invoice." Cross-checked against the PostgreSQL documentation (LIMIT and window functions), the MySQL 8.0 reference manual (window functions, available from 8.0), and the SQL Server documentation (TOP / OFFSET-FETCH). Re-authored and deepened for this guide: added the mechanism statement, the four-stage worked trace on tied invoices 2 and 4, a hand-authored data-flow diagram, the buggy HAVING = MAX counter-example, the LIMIT-1-vs-window-RANK dialect comparison, and the integer-vs-decimal tie-break nuance.