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's Worth Over Invoices INPUT Product Table product_id name 0 Apple 1 Orange Invoice Table inv_id prod_id rest paid cancel 1 0 100 50 10 2 0 200 100 20 3 1 150 80 5 Two relational tables linked by product_id Input Values: Products: Apple(0), Orange(1) Invoices: 3 records Columns: rest,paid,cancel,refund ALGORITHM STEPS 1 JOIN Tables Product LEFT JOIN Invoice ON product_id 2 GROUP BY Product Group all invoice rows by product name 3 SUM Aggregations SUM(rest), SUM(paid), SUM(canceled), SUM(refunded) 4 ORDER BY Name Sort results alphabetically by product name SQL Query: SELECT name, SUM(rest), SUM(paid), SUM(canceled), SUM(refund) FROM Product LEFT JOIN Invoice GROUP BY name ORDER BY name FINAL RESULT Output Table name rest paid cancel refund Apple 300 150 30 0 Orange 150 80 5 0 Calculation Breakdown: Apple (prod_id=0): rest: 100+200 = 300 paid: 50+100 = 150 Orange (prod_id=1): rest: 150, paid: 80 canceled: 5, refund: 0 OK - Results ordered by name Apple before Orange Output: 2 rows (sorted A-Z) Key Insight: Use LEFT JOIN to include products with no invoices (will show NULL/0 values). IFNULL or COALESCE handles NULL values from missing invoice records. GROUP BY aggregates all invoices per product; ORDER BY ensures alphabetical output. TutorialsPoint - Product's Worth Over Invoices | Optimal Solution Time: O(n log n) for sorting | Space: O(n) for result storage | n = number of 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