Customers With a Return: Delivered vs Returned Value — SQL Medium Problem

Problem statement

An online shop is reviewing customers who send things back. For every customer with at least one returned order, return:

• customer_id;
• delivered_amount — the total amount of their delivered orders (0 if they have none);
• returned_amount — the total amount of their returned orders.

Cancelled orders count towards neither total. Customers who never returned an order are not in the result. Return the rows in any order.

Tables

Table: ShopOrder

Primary key: order_id.

amount is the order's value in rupees. A cancelled order never shipped; a returned one was delivered and then sent back.

Examples

Example 1

ShopOrder

Output

How to solve Customers With a Return: Delivered vs Returned Value

This is conditional aggregation: one GROUP BY customer_id, and inside each aggregate a condition that decides which rows of the group contribute.

SUM(IF(status = 'delivered', amount, 0)) adds a delivered order's amount and 0 for every other order, so it is the delivered total — and 0, not NULL, for a customer with no delivered orders. The returned total is the same expression with 'returned'. Cancelled orders match neither condition and add nothing to either column.

Which customers to keep is a property of the group, not of a single row, so the test belongs in HAVING: HAVING SUM(status = 'returned') > 0 counts the group's returned orders (a comparison is 1 or 0). Putting status = 'returned' in WHERE instead would throw the delivered rows away before grouping and make every delivered_amount 0 — a common wrong answer.

Two alternatives: pick the customers first with customer_id IN (SELECT customer_id … WHERE status = 'returned') and then aggregate with CASE; or compute the returned and delivered totals in two derived tables and LEFT JOIN the delivered one onto the returned one, with COALESCE(…, 0) for customers who have no deliveries. The single grouped query reads the table once; the others read it twice. All are linear plus the grouping.

Reference solution (MySQL)

SELECT customer_id,
       SUM(IF(status = 'delivered', amount, 0)) AS delivered_amount,
       SUM(IF(status = 'returned', amount, 0)) AS returned_amount
FROM ShopOrder
GROUP BY customer_id
HAVING SUM(status = 'returned') > 0

Another way

SELECT customer_id,
       SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered_amount,
       SUM(CASE WHEN status = 'returned' THEN amount ELSE 0 END) AS returned_amount
FROM ShopOrder
WHERE customer_id IN (SELECT customer_id FROM ShopOrder WHERE status = 'returned')
GROUP BY customer_id

Another way

SELECT r.customer_id, COALESCE(d.total, 0) AS delivered_amount, r.total AS returned_amount
FROM (SELECT customer_id, SUM(amount) AS total FROM ShopOrder WHERE status = 'returned' GROUP BY customer_id) r
LEFT JOIN (SELECT customer_id, SUM(amount) AS total FROM ShopOrder WHERE status = 'delivered' GROUP BY customer_id) d
  ON d.customer_id = r.customer_id