Product's Worth Over Invoices - Problem

You are given two tables: Product and Invoice.

The Product table contains product information with unique IDs and names. Each product name consists of only lowercase English letters, and no two products share the same name.

The Invoice table tracks financial data for each product across multiple invoices, including:

  • rest: Amount left to pay
  • paid: Amount already paid
  • canceled: Amount that was canceled
  • refunded: Amount that was refunded

Write a SQL query to return each product name along with the total amounts for rest, paid, canceled, and refunded across all invoices for that product.

Return the result ordered by product_name.

Table Schema

Product
Column Name Type Description
product_id PK int Unique identifier for each product
name varchar Product name (lowercase letters only, unique)
Primary Key: product_id
Invoice
Column Name Type Description
invoice_id PK int Unique identifier for each invoice
product_id int Foreign key referencing Product table
rest int Amount left to pay for this invoice
paid int Amount paid for this invoice
canceled int Amount canceled for this invoice
refunded int Amount refunded for this invoice
Primary Key: invoice_id

Input & Output

Example 1 — Basic Product Invoice Aggregation
Input Tables:
Product
product_id name
1 phone
2 laptop
Invoice
invoice_id product_id rest paid canceled refunded
1 1 100 500 0 0
2 1 200 300 100 50
3 2 0 1000 0 0
Output:
product_name rest paid canceled refunded
laptop 0 1000 0 0
phone 300 800 100 50
💡 Note:

The phone has 2 invoices: (100+200=300 rest, 500+300=800 paid, 0+100=100 canceled, 0+50=50 refunded). The laptop has 1 invoice with totals (0 rest, 1000 paid, 0 canceled, 0 refunded). Results are ordered by product name.
Example 2 — Product Without Invoices
Input Tables:
Product
product_id name
1 mouse
2 keyboard
Invoice
invoice_id product_id rest paid canceled refunded
1 1 50 150 25 10
Output:
product_name rest paid canceled refunded
keyboard 0 0 0 0
mouse 50 150 25 10
💡 Note:

The keyboard product has no invoices, so LEFT JOIN with COALESCE returns 0 for all amount columns. The mouse has one invoice with the specified amounts. Both products appear in the result, demonstrating LEFT JOIN behavior.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 1 ≤ invoice_id ≤ 1000
  • product_id is unique in Product table
  • invoice_id is unique in Invoice table
  • Product names contain only lowercase English letters
  • All amount values are non-negative integers

Visualization

Tap to expand
Product Invoice Aggregation OverviewInput: Two TablesProductname1phone2laptopInvoiceproductpaidrest1150010021300200LEFT JOINGROUP BYOutput: Aggregatednamepaidrestlaptop00phone800300
Understanding the Visualization
1
Input Tables
Product and Invoice tables
2
LEFT JOIN
Join on product_id
3
Aggregate
Sum amounts by product
Key Takeaway
🎯 Key Insight: LEFT JOIN ensures all products appear in results, even without invoices

Related Problems

Asked in
Amazon 23 Microsoft 18 Oracle 15
28.5K Views
Medium Frequency
~12 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen