Product's Worth Over Invoices - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase5 min read

Problem Statement

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

Approach Overview

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

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

Video Solution

LeetCode 1677 "Product's Worth Over Invoices" Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,525 views views

Frequently Asked Questions

Is Product's Worth Over Invoices easy or hard?

Product's Worth Over Invoices is classified as an Easy SQL problem. It mainly tests understanding of JOIN operations, computed columns like price * quantity, and GROUP BY aggregation.

Product's Worth Over Invoices Python/Java solution

This problem is a database query problem rather than a traditional algorithm implementation. The expected solution is written in SQL (typically MySQL) using JOIN and SUM aggregation instead of Python or Java code.

How to solve Product's Worth Over Invoices in O(n)?

Join Product and Invoice on product_id, compute price * quantity for each joined row, and use SUM() with GROUP BY to accumulate totals per product. The database processes each invoice row once, giving linear time complexity.

What is the best approach for Product's Worth Over Invoices?

The optimal approach uses an INNER JOIN between the Product and Invoice tables followed by SUM aggregation. For each invoice row, compute price * quantity and group the results by product_id. This runs in O(n) time where n is the number of invoice records.

Is Product's Worth Over Invoices asked at Google/Amazon/Meta?

SQL aggregation and join problems like this frequently appear in data engineering and analytics interviews at companies such as Amazon, Google, and Meta. Variations usually involve computing revenue, totals, or metrics across transaction tables.

What data structure is used in Product's Worth Over Invoices?

The problem relies on relational database tables and SQL aggregation. Conceptually, the GROUP BY operation behaves like a hash aggregation where rows with the same product_id are combined and summed.

What is the time complexity of Product's Worth Over Invoices?

The SQL solution runs in O(n) time because the database engine scans the Invoice table once and aggregates results by product. Space complexity is O(1) beyond the output since aggregation is handled internally by the query engine.

Ready to solve this problem?

Practice Product's Worth Over Invoices with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance38.2%

Approaches1

Reading time5 min

Practice this problem

Open in Editor