Product's Worth Over Invoices - Practice Coding Problems

Product's Worth Over Invoices - Problem

Database Easy

Imagine you're working as a financial analyst for an e-commerce company that needs to analyze their invoice data across all products. You have access to two important databases:

Product Database: Contains all products with their unique IDs and names
Invoice Database: Contains detailed financial information for each invoice including amounts that are still owed, paid, canceled, or refunded

Your task is to create a comprehensive financial report that shows, for each product, the total amounts across all four financial categories from all invoices associated with that product.

Goal: Generate a summary report showing each product's name along with the total amounts due (rest), paid (paid), canceled (canceled), and refunded (refunded) across all invoices.

Input: Two tables - Product table with product information and Invoice table with financial transaction details.

Output: A result set with product names and their aggregated financial totals, ordered alphabetically by product name.

Input & Output

basic_example.sql — Basic Case

$ Input: Product table: +------------+--------+ | product_id | name | +------------+--------+ | 0 | laptop | | 1 | mouse | +------------+--------+ Invoice table: +------------+------------+------+------+----------+----------+ | invoice_id | product_id | rest | paid | canceled | refunded | +------------+------------+------+------+----------+----------+ | 23 | 0 | 2 | 1500 | 25 | 0 | | 12 | 0 | 0 | 1000 | 0 | 15 | | 1 | 1 | 5 | 3000 | 0 | 0 | +------------+------------+------+------+----------+----------+

› Output: +--------+------+------+----------+----------+ | name | rest | paid | canceled | refunded | +--------+------+------+----------+----------+ | laptop | 2 | 2500 | 25 | 15 | | mouse | 5 | 3000 | 0 | 0 | +--------+------+------+----------+----------+

💡 Note: For laptop: rest=2+0=2, paid=1500+1000=2500, canceled=25+0=25, refunded=0+15=15. For mouse: rest=5, paid=3000, canceled=0, refunded=0. Results are ordered alphabetically by product name.

no_invoices.sql — Product Without Invoices

$ Input: Product table: +------------+--------+ | product_id | name | +------------+--------+ | 0 | laptop | | 1 | mouse | | 2 | screen | +------------+--------+ Invoice table: +------------+------------+------+------+----------+----------+ | invoice_id | product_id | rest | paid | canceled | refunded | +------------+------------+------+------+----------+----------+ | 23 | 0 | 10 | 1000 | 0 | 0 | | 12 | 1 | 0 | 500 | 0 | 0 | +------------+------------+------+------+----------+----------+

› Output: +--------+------+------+----------+----------+ | name | rest | paid | canceled | refunded | +--------+------+------+----------+----------+ | laptop | 10 | 1000 | 0 | 0 | | mouse | 0 | 500 | 0 | 0 | | screen | 0 | 0 | 0 | 0 | +--------+------+------+----------+----------+

💡 Note: The screen product has no invoices, so all its financial metrics are 0. The LEFT JOIN ensures all products appear in the result, and COALESCE handles NULL values by converting them to 0.

single_product.sql — Edge Case

$ Input: Product table: +------------+--------+ | product_id | name | +------------+--------+ | 1 | tablet | +------------+--------+ Invoice table: +------------+------------+------+------+----------+----------+ | invoice_id | product_id | rest | paid | canceled | refunded | +------------+------------+------+------+----------+----------+ | 1 | 1 | 100 | 200 | 50 | 25 | +------------+------------+------+------+----------+----------+

› Output: +--------+------+------+----------+----------+ | name | rest | paid | canceled | refunded | +--------+------+------+----------+----------+ | tablet | 100 | 200 | 50 | 25 | +--------+------+------+----------+----------+

💡 Note: Simple case with one product and one invoice. All values are taken directly from the single invoice record.

Constraints

1 ≤ number of products ≤ 10⁴
0 ≤ number of invoices ≤ 10⁵
Product names contain only lowercase English letters
All financial amounts are non-negative integers
Each product_id in Product table is unique
Each invoice_id in Invoice table is unique

Visualization

Tap to expand

Understanding the Visualization

Start with Products

Begin with all products in your catalog

LEFT JOIN Invoices

Match each product with its invoices (keeping products without invoices)

GROUP BY Product

Group all invoice records by product name

SUM Financial Metrics

Calculate totals for rest, paid, canceled, and refunded amounts

Handle NULLs

Use COALESCE to convert NULL sums to 0 for products without invoices

ORDER Results

Sort the final results alphabetically by product name

Key Takeaway

🎯 Key Insight: LEFT JOIN ensures all products appear in results, GROUP BY aggregates invoice data per product, and COALESCE converts NULL sums to 0 for products without invoices - all in one efficient query.

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses a LEFT JOIN between Product and Invoice tables combined with GROUP BY and SUM aggregations. This approach efficiently processes all data in a single pass, ensuring products without invoices still appear with zero values using COALESCE. The query is ordered alphabetically by product name for consistent output.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries Approach	O(n * m * 4)	O(1)	Use separate subqueries for each financial metric
LEFT JOIN with GROUP BY (Optimal)	O(n + m)	O(n)	Join tables once and aggregate all metrics in a single query

Nested Subqueries Approach — Algorithm Steps

Create separate subqueries for each financial metric
Join Product table with each subquery
Handle products that might not have any invoices
Order results by product name

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Products

Start with Product table

Query 1: REST

For each product, scan Invoice table for rest amounts

Query 2: PAID

For each product, scan Invoice table again for paid amounts

Query 3: CANCELED

For each product, scan Invoice table again for canceled amounts

Query 4: REFUNDED

For each product, scan Invoice table again for refunded amounts

Code -

solution.c — C

// SQL Solution using nested subqueries
char* query = "\
SELECT \
    p.name, \
    COALESCE( \
        (SELECT SUM(i.rest) \
         FROM Invoice i \
         WHERE i.product_id = p.product_id), 0 \
    ) AS rest, \
    COALESCE( \
        (SELECT SUM(i.paid) \
         FROM Invoice i \
         WHERE i.product_id = p.product_id), 0 \
    ) AS paid, \
    COALESCE( \
        (SELECT SUM(i.canceled) \
         FROM Invoice i \
         WHERE i.product_id = p.product_id), 0 \
    ) AS canceled, \
    COALESCE( \
        (SELECT SUM(i.refunded) \
         FROM Invoice i \
         WHERE i.product_id = p.product_id), 0 \
    ) AS refunded \
FROM Product p \
ORDER BY p.name;";

Time & Space Complexity

Time Complexity

⏱️

O(n * m * 4)

Where n is products and m is invoices, multiplied by 4 separate scans

✓ Linear Growth

Space Complexity

O(1)

No additional space needed beyond result set

✓ Linear Space

42.0K Views

High Frequency

~15 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler