Generate the Invoice - Solution & Explanation

Problem Overview: You need to generate invoice totals from transactional data stored across multiple tables. Each purchase references a product and quantity, and the final invoice requires computing the total cost by multiplying product price with quantity and aggregating results per invoice.

Approach 1: JOIN + Aggregation (O(n) time, O(1) extra space)

The core idea is to reconstruct each invoice line by joining the purchases table with the products table using product_id. Once the price is available, compute the line cost using price * quantity. Then aggregate the values using SUM() grouped by the invoice identifier. SQL engines handle grouping efficiently, so this approach scans the joined dataset once, making the time complexity O(n) where n is the number of purchase records. Space overhead is constant because the database performs aggregation internally without allocating extra data structures.

This approach relies on relational operations: INNER JOIN to connect product pricing with purchases and GROUP BY to consolidate rows belonging to the same invoice. The correctness comes from computing totals after the join so every purchase line contributes exactly one price calculation. If multiple products exist in the same invoice, their contributions accumulate through the aggregation step.

Approach 2: CTE with Precomputed Line Totals (O(n) time, O(n) intermediate space)

A more structured query uses a Common Table Expression (CTE) to first compute line totals for each purchase. The CTE calculates price * quantity after joining product and purchase data. The outer query then aggregates those intermediate rows per invoice using SUM(). Time complexity remains O(n) because every purchase row is processed once, but intermediate results may be materialized depending on the optimizer, giving an effective O(n) temporary space footprint.

This structure improves readability and debugging when queries grow more complex. If additional filters or invoice-level calculations are needed later (such as ranking or selecting the highest invoice total), the intermediate result from the CTE becomes a convenient base dataset.

Conceptually, this problem revolves around relational data processing using joins and aggregations, common patterns in database and SQL interview questions. Understanding when to compute derived columns (like line totals) before aggregation is a key design detail.

Recommended for interviews: The direct JOIN + GROUP BY aggregation is what interviewers expect. It demonstrates you understand relational joins, derived expressions, and grouped aggregation. Writing the query with a CTE shows stronger SQL structuring skills and becomes useful when extending the query with additional logic.