00:00:00

Description

Editorial

Product Invoice Financial Rollup

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

invoice =

[{"invoice_id":101,"product_id":1,"rest":2,"paid":5,"canceled":0,"refunded":1},{"invoice_id":102,"product_id":1,"rest":1,"paid":0,"canceled":4,"refunded":0},{"invoice_id":103,"product_id":2,"rest":0,"paid":7,"canceled":1,"refunded":2}]

product =

[{"product_id":1,"name":"alpha"},{"product_id":2,"name":"beta"}]

Loading problem...

101

00:00:00

Description

Editorial

Product Invoice Financial Rollup

EASY10 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

invoice =

product =

[{"product_id":1,"name":"alpha"},{"product_id":2,"name":"beta"}]

Product Invoice Financial Rollup

Hints

Product Invoice Financial Rollup

Hints