SQL Interview Questions – Product’s Worth Over Invoices

Problem Description –

Write an SQL query that will, for all products, return each product name with the total amount due, paid, canceled, and refunded across all invoices.

Return the result table ordered by product_name.

The query result format is in the following example.

Problem Link – Invoices

Difficulty Level – Easy

Solution –

SELECT
    p.name,
    IFNULL(SUM(i.rest),0) as rest,
    IFNULL(SUM(i.paid),0) as paid,
    IFNULL(SUM(i.canceled),0) as canceled,
    IFNULL(SUM(i.refunded),0) as refunded
FROM Product as p LEFT JOIN Invoice as i
on i.product_id = p.product_id
GROUP BY 1
ORDER BY 1