Table: Product
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | name | varchar | +-------------+---------+ product_id is the column with unique values for this table. This table contains the ID and the name of the product. The name consists of only lowercase English letters. No two products have the same name.
Table: Invoice
+-------------+------+ | Column Name | Type | +-------------+------+ | invoice_id | int | | product_id | int | | rest | int | | paid | int | | canceled | int | | refunded | int | +-------------+------+ invoice_id is the column with unique values for this table and the id of this invoice. product_id is the id of the product for this invoice. rest is the amount left to pay for this invoice. paid is the amount paid for this invoice. canceled is the amount canceled for this invoice. refunded is the amount refunded for this invoice.
Write a solution that will, for all products, return each product name with the total amount due, paid, canceled, and refunded across all invoices.
Return the result table ordered by product_name.
The result format is in the following example.
Example 1:
Input: Product table: +------------+-------+ | product_id | name | +------------+-------+ | 0 | ham | | 1 | bacon | +------------+-------+ Invoice table: +------------+------------+------+------+----------+----------+ | invoice_id | product_id | rest | paid | canceled | refunded | +------------+------------+------+------+----------+----------+ | 23 | 0 | 2 | 0 | 5 | 0 | | 12 | 0 | 0 | 4 | 0 | 3 | | 1 | 1 | 1 | 1 | 0 | 1 | | 2 | 1 | 1 | 0 | 1 | 1 | | 3 | 1 | 0 | 1 | 1 | 1 | | 4 | 1 | 1 | 1 | 1 | 0 | +------------+------------+------+------+----------+----------+ Output: +-------+------+------+----------+----------+ | name | rest | paid | canceled | refunded | +-------+------+------+----------+----------+ | bacon | 3 | 3 | 3 | 3 | | ham | 2 | 4 | 5 | 3 | +-------+------+------+----------+----------+ Explanation: - The amount of money left to pay for bacon is 1 + 1 + 0 + 1 = 3 - The amount of money paid for bacon is 1 + 0 + 1 + 1 = 3 - The amount of money canceled for bacon is 0 + 1 + 1 + 1 = 3 - The amount of money refunded for bacon is 1 + 1 + 1 + 0 = 3 - The amount of money left to pay for ham is 2 + 0 = 2 - The amount of money paid for ham is 0 + 4 = 4 - The amount of money canceled for ham is 5 + 0 = 5 - The amount of money refunded for ham is 0 + 3 = 3
Problem Overview: You are given two database tables: Product and Invoice. Each invoice records how many units of a product were sold. The task is to compute the total worth of each product across all invoices, defined as price * quantity, and return the aggregated result per product.
Approach 1: Join + Aggregation (O(n) time, O(1) extra space)
The straightforward solution joins the Product table with the Invoice table using product_id. Once the rows are combined, compute the worth of each invoice entry using price * quantity. Then aggregate the result with SUM() grouped by product. SQL handles the iteration internally during the join and aggregation phases. Time complexity is O(n) where n is the number of invoice rows processed, and space complexity is O(1) beyond the output since aggregation occurs within the query engine.
This approach relies on relational database fundamentals: SQL JOIN to combine tables and GROUP BY with aggregation functions to compute totals. The key insight is that each invoice contributes a partial value (price * quantity) that must be accumulated for the same product.
Implementation outline:
SELECT p.product_name, SUM(p.price * i.quantity) AS worth
FROM Product p
JOIN Invoice i ON p.product_id = i.product_id
GROUP BY p.product_id, p.product_name
ORDER BY worth DESC, p.product_name ASC;
The database engine scans invoice rows, performs the join using the foreign key relationship, and aggregates totals per product. Sorting the final result ensures deterministic ordering.
Recommended for interviews: The join + aggregation approach is exactly what interviewers expect for SQL problems involving transactional tables. A brute force mindset would involve manually computing totals per product, but relational databases are designed to perform this using SQL aggregation. Demonstrating correct joins, grouping, and computed columns shows strong command of SQL query design.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Join + SUM Aggregation | O(n) | O(1) | Standard relational query when invoice rows reference products |
| Subquery Aggregation | O(n) | O(1) | Useful when calculating totals in a derived table before joining product details |
LeetCode 1677 "Product's Worth Over Invoices" Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,474 views views
Practice Product's Worth Over Invoices with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor