Product Invoice Financial Rollup

Easy

A finance analytics team needs a product-level reconciliation report from invoice events. Table: product product_id: unique product identifier name: product display name Table: invoice invoice_id: unique invoice identifier product_id: product linked to this invoice rest: amount still outstanding paid: amount already paid canceled: amount canceled before settlement refunded: amount returned after payment Task: For every product, return total rest, paid, canceled, and refunded amounts across all invoice rows linked to that product. Business requirements: Return all products, even if a product has zero invoices. For products without invoices, each total must be 0. Aggregate each numeric field independently. Output requirements: Return columns in this exact order: name rest paid canceled refunded Sort by name in ascending order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

product:
| product_id | name  |
|------------|-------|
| 1          | alpha |
| 2          | beta  |

invoice:
| invoice_id | product_id | rest | paid | canceled | refunded |
|------------|------------|------|------|----------|----------|
| 101        | 1          | 2    | 5    | 0        | 1        |
| 102        | 1          | 1    | 0    | 4        | 0        |
| 103        | 2          | 0    | 7    | 1        | 2        |

Output

[
  {"name":"alpha","rest":3,"paid":5,"canceled":4,"refunded":1},
  {"name":"beta","rest":0,"paid":7,"canceled":1,"refunded":2}
]

Explanation

Each amount column is summed independently per product. alpha combines two invoices, while beta combines one.

Example 2

Input

product:
| product_id | name   |
|------------|--------|
| 10         | item-a |
| 20         | item-b |
| 30         | item-c |

invoice:
| invoice_id | product_id | rest | paid | canceled | refunded |
|------------|------------|------|------|----------|----------|
| 700        | 20         | 4    | 1    | 0        | 0        |
| 701        | 20         | 0    | 3    | 2        | 1        |

Output

[
  {"name":"item-a","rest":0,"paid":0,"canceled":0,"refunded":0},
  {"name":"item-b","rest":4,"paid":4,"canceled":2,"refunded":1},
  {"name":"item-c","rest":0,"paid":0,"canceled":0,"refunded":0}
]

Explanation

Products with no matching invoice rows must still be returned with zero totals.

Example 3

Input

product:
| product_id | name   |
|------------|--------|
| 501        | c-item |
| 101        | a-item |
| 301        | b-item |

invoice:
| invoice_id | product_id | rest | paid | canceled | refunded |
|------------|------------|------|------|----------|----------|
| 1          | 501        | 9    | 0    | 0        | 0        |
| 2          | 301        | 0    | 6    | 1        | 0        |
| 3          | 101        | 5    | 2    | 0        | 2        |

Output

[
  {"name":"a-item","rest":5,"paid":2,"canceled":0,"refunded":2},
  {"name":"b-item","rest":0,"paid":6,"canceled":1,"refunded":0},
  {"name":"c-item","rest":9,"paid":0,"canceled":0,"refunded":0}
]

Explanation

Ordering is by product name, not by product_id or invoice sequence.

Constraints

1 <= number of rows in product <= 300000
0 <= number of rows in invoice <= 500000
product.product_id is unique
product.name is unique
invoice.invoice_id is unique
invoice.product_id references product.product_id
0 <= rest, paid, canceled, refunded <= 1000000000
Include products with no invoices
Return exactly five columns: name, rest, paid, canceled, refunded
Sort output by name ascending
For SQL submissions, use SQLite 3.27.2 behavior
For Pandas submissions, return a DataFrame with exact output schema

product: | product_id | name | |------------|-------| | 1 | alpha | | 2 | beta | invoice: | invoice_id | product_id | rest | paid | canceled | refunded | |------------|------------|------|------|----------|----------| | 101 | 1 | 2 | 5 | 0 | 1 | | 102 | 1 | 1 | 0 | 4 | 0 | | 103 | 2 | 0 | 7 | 1 | 2 |

product: | product_id | name | |------------|--------| | 10 | item-a | | 20 | item-b | | 30 | item-c | invoice: | invoice_id | product_id | rest | paid | canceled | refunded | |------------|------------|------|------|----------|----------| | 700 | 20 | 4 | 1 | 0 | 0 | | 701 | 20 | 0 | 3 | 2 | 1 |

product: | product_id | name | |------------|--------| | 501 | c-item | | 101 | a-item | | 301 | b-item | invoice: | invoice_id | product_id | rest | paid | canceled | refunded | |------------|------------|------|------|----------|----------| | 1 | 501 | 9 | 0 | 0 | 0 | | 2 | 301 | 0 | 6 | 1 | 0 | | 3 | 101 | 5 | 2 | 0 | 2 |