Domain 1EasyCORE

Product's Worth Over Invoices

The problem requires us to determine which products have a total worth that exceeds their total invoice amounts. Given two tables: `Products` and `Invoices`, we need to calculate the net worth of each product by subtracting the sum of its invoices from its total worth. The output should be a list of product IDs for those products where the net worth is positive.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 0

1677. Product’s Worth Over Invoices

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 73.1%


Problem Breakdown

The problem requires us to determine which products have a total worth that exceeds their total invoice amounts. Given two tables: Products and Invoices, we need to calculate the net worth of each product by subtracting the sum of its invoices from its total worth. The output should be a list of product IDs for those products where the net worth is positive.

Inputs:

  • Products table with columns: product_id, total_worth
  • Invoices table with columns: invoice_id, product_id, amount

Outputs:

  • A list of product_ids where the total worth exceeds the sum of invoice amounts.

Implicit Constraints/Edge Cases:

  • The Products table may be empty.
  • The Invoices table may be empty or contain no invoices for a product.
  • There might be duplicate entries in either table, but we should consider each entry independently.
  • Negative values are possible in the total_worth and amount columns.

Intuition

A brute-force approach would involve joining the two tables on product_id, grouping by product_id, summing the invoice amounts for each product, and then comparing this sum to the total worth. However, this approach could be inefficient if the tables are large due to the need for a full join and subsequent aggregation.

The key insight is to use SQL’s aggregation functions to efficiently compute the net worth for each product without needing to perform a full join. By using LEFT JOIN and GROUP BY, we can ensure that products with no invoices are still considered, and by filtering out those with non-positive net worth, we get the desired result.

Approach

  1. Join Tables: Perform a LEFT JOIN between the Products table (T1) and the Invoices table (T2) on the product_id to ensure all products are included even if they have no invoices.
  2. Aggregate Invoices: Use GROUP BY T1.product_id to aggregate the invoice amounts for each product.
  3. Calculate Net Worth: For each group, calculate the net worth by subtracting the sum of invoice amounts from the total worth.
  4. Filter Results: Use a HAVING clause to filter out products where the net worth is less than or equal to zero.

Complexity Analysis

  • Time Complexity: O(n + m), where n is the number of rows in the Products table and m is the number of rows in the Invoices table. The join operation is linear with respect to the total number of rows.
  • Space Complexity: O(k), where k is the number of products with a positive net worth. This is due to the storage required for the result set.

Solution

SELECT T1.product_id
FROM Products AS T1
LEFT JOIN Invoices AS T2 ON T1.product_id = T2.product_id
GROUP BY T1.product_id
HAVING SUM(COALESCE(T2.amount, 0)) < T1.total_worth;

Edge Cases Handled

  • Empty Products Table: The query will return an empty result set as there are no products to evaluate.
  • Empty Invoices Table: All products will be considered with a net worth equal to their total worth.
  • No Invoices for a Product: Products without invoices will have a net worth equal to their total worth, and if this is positive, they will be included in the result.
  • Negative Values: The use of COALESCE ensures that missing invoice amounts are treated as zero, allowing for correct calculation even with negative values.