Problem

Table: Product

1
2
3
4
5
6
7
8

+-------------+---------+
| 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

+-------------+------+
| 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.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

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

Solution

Method 1 – Join and Aggregate Sums

Intuition

We need to sum up the invoice columns for each product and return the results ordered by product name. This is a join and group-by-aggregate problem.

Approach

  1. Join Product and Invoice on product_id.
  2. Group by product name and sum the rest, paid, canceled, and refunded columns.
  3. Order the result by product name.

Code

1
2
3
4
5

SELECT p.name, SUM(i.rest) AS rest, SUM(i.paid) AS paid, SUM(i.canceled) AS canceled, SUM(i.refunded) AS refunded
FROM Product p
JOIN Invoice i ON p.product_id = i.product_id
GROUP BY p.name
ORDER BY p.name;
1
2
3
4
5

SELECT p.name, SUM(i.rest) AS rest, SUM(i.paid) AS paid, SUM(i.canceled) AS canceled, SUM(i.refunded) AS refunded
FROM Product p
JOIN Invoice i ON p.product_id = i.product_id
GROUP BY p.name
ORDER BY p.name;
1
2
3
4
5
6
7

# Assume Product and Invoice are pandas DataFrames
import pandas as pd
def products_worth_over_invoices(Product: pd.DataFrame, Invoice: pd.DataFrame) -> pd.DataFrame:
    merged = Product.merge(Invoice, on='product_id')
    result = merged.groupby('name')[['rest', 'paid', 'canceled', 'refunded']].sum().reset_index()
    result = result.sort_values('name').reset_index(drop=True)
    return result

Complexity

  • ⏰ Time complexity: O(N) where N is the number of rows in Invoice.
  • 🧺 Space complexity: O(P) where P is the number of products.