Scenario: Brightlane's revenue analytics team is reviewing each customer's order outcomes to understand delivery success and cancellation exposure.
Task: Write a query to return every customer who has placed at least one order, with their id, name, total revenue from delivered orders, total revenue from cancelled orders, and total order count.
Assumptions:
- A delivered order has
statusequal to'delivered'. A cancelled order hasstatusequal to'cancelled'. Other statuses do not contribute to either revenue total but do contribute to the order count. - Some customers have no delivered orders, some have no cancelled orders, and some have neither; the corresponding revenue value for those customers is reported as a missing value.
Output:
- One row per customer who has placed at least one order.
- Columns in this order:
customer_id,name,delivered_revenue,cancelled_revenue,order_count. - Sorted by
delivered_revenuedescending with missing values last; ties broken bycustomer_idascending.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
order_results AS (
SELECT
o.customer_id,
SUM(
CASE
WHEN o.status = 'delivered' THEN o.total_amount
END
) AS delivered_revenue,
SUM(
CASE
WHEN o.status = 'cancelled' THEN o.total_amount
END
) AS cancelled_revenue,
COUNT(*) AS order_count
FROM
orders o
GROUP BY
o.customer_id
),
customer_view AS (
SELECT
c.id AS customer_id,
c.name,
r.delivered_revenue,
r.cancelled_revenue,
r.order_count
FROM
customers c
JOIN order_results r ON r.customer_id = c.id
)
SELECT
customer_id,
name,
delivered_revenue,
cancelled_revenue,
order_count
FROM
customer_view
ORDER BY
delivered_revenue DESC NULLS LAST,
customer_idThe shape
Two CTEs, with the conditional aggregation done once in the first and the customer name attached afterward in the second. The per-status sums are computed using CASE inside the aggregate, so the entire status split happens in a single pass over orders and the customer table joins in only after the totals exist.
Clause by clause
WITH order_results AS (
SELECT
o.customer_id,
SUM(CASE WHEN o.status = 'delivered' THEN o.total_amount END) AS delivered_revenue,
SUM(CASE WHEN o.status = 'cancelled' THEN o.total_amount END) AS cancelled_revenue,
COUNT(*) AS order_count
FROM orders o
GROUP BY o.customer_id
)GROUP BY o.customer_id produces one row per customer. Each SUM(CASE WHEN ...) adds the total_amount only on rows matching its status and contributes nothing on the others. The CASE with no ELSE returns NULL on non-matching rows, and SUM skips nulls, which is why a customer with no delivered orders ends up with NULL for delivered_revenue instead of 0. COUNT(*) runs over every order regardless of status, so the total order count includes the other statuses too.
customer_view AS (
SELECT c.id AS customer_id, c.name, r.delivered_revenue, r.cancelled_revenue, r.order_count
FROM customers c
JOIN order_results r ON r.customer_id = c.id
)The customer record joins in only here, attaching the readable name to each per-customer totals row. The join is an inner join, so customers with no orders never appear, which matches the "at least one order" requirement.
SELECT customer_id, name, delivered_revenue, cancelled_revenue, order_count FROM customer_view ORDER BY delivered_revenue DESC NULLS LAST, customer_idsorts the biggest delivered earners first, sends the no-delivery customers to the bottom viaNULLS LAST, and breaks ties by ID.
Why CASE WHEN without ELSE 0 and not ELSE 0
The prompt asks for missing values when there is no delivered revenue, not 0. SUM(CASE WHEN ... THEN ... END) returns NULL when no row matches because every CASE evaluation produced NULL and SUM of all-nulls is NULL. Adding ELSE 0 would make those sums 0 instead, which reads as "delivered $0" rather than "no delivered orders." The two are different facts about the customer, and the prompt wants the distinction preserved.
You practiced separating per-status revenue into one CTE and attaching customer identity in another, so the conditional totals are computed once and labelled afterward.
