Brightlane's logistics team wants a per-customer order-status breakdown across three buckets:
pending_count— orders withstatus = 'pending'.in_transit_count— orders withstatus = 'shipped'.final_count— orders in any final state (status = 'delivered'orstatus = 'cancelled').
Write a query to return all four columns per customer.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The three buckets are mutually exclusive and together cover every status value — every order contributes to exactly one of the three columns.
- The
final_countbucket combines two statuses with anORinside theCASEpredicate.
Output:
- One row per customer with at least one order, with columns
customer_id,pending_count,in_transit_count, andfinal_count.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
customer_id,
COUNT(
CASE
WHEN status = 'pending' THEN 1
END
) AS pending_count,
COUNT(
CASE
WHEN status = 'shipped' THEN 1
END
) AS in_transit_count,
COUNT(
CASE
WHEN status = 'delivered'
OR status = 'cancelled' THEN 1
END
) AS final_count
FROM
orders
GROUP BY
customer_idThe shape
Three conditional counts split each customer's orders into pending, in-transit, and final-state buckets. The final_count predicate uses OR inside the CASE so that two raw status values ('delivered' and 'cancelled') consolidate into one report column. Customer 1 has 5 final-state orders and nothing in transit or pending; customer 22 has 1 pending and 2 final-state.
Clause by clause
customer_idis the grouping column; each customer's orders form one per-customer group.COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_countandCOUNT(CASE WHEN status = 'shipped' THEN 1 END) AS in_transit_counteach test a single status literal. The'shipped'rows feed the in-transit count; the prompt's column name maps the logistics team's vocabulary onto the database's status value.COUNT(CASE WHEN status = 'delivered' OR status = 'cancelled' THEN 1 END) AS final_countconsolidates two statuses into one bucket. TheORoperator combines two equality checks into a single boolean expression. TheCASEreturns1for any row whose status matches either side; everything else falls through toNULLandCOUNTskips it.FROM orders GROUP BY customer_idpartitions the rows per customer before the aggregates run.
Why OR and not two separate COUNT(CASE) calls
The alternative would be COUNT(CASE WHEN status = 'delivered' THEN 1 END) + COUNT(CASE WHEN status = 'cancelled' THEN 1 END). Same number, more moving parts. The OR form expresses the report's logic directly: these two raw statuses are one bucket. If 'returned' later becomes a final state, the OR form adds one term inside one predicate; the addition form needs a third COUNT(CASE) and a re-balanced sum.
The trap
OR binds looser than AND, so a WHEN such as status = 'delivered' OR status = 'cancelled' AND customer_id > 50 reads, with default precedence, as status = 'delivered' OR (status = 'cancelled' AND customer_id > 50) rather than (status = 'delivered' OR status = 'cancelled') AND customer_id > 50. The query runs without error and returns a plausible-looking count, but it has counted every delivered row regardless of customer_id instead of just the high-ID final-state rows.
The rule is the same one from WHERE: when OR and AND share a predicate, parentheses around the OR-group are mandatory. They cost nothing to add and they eliminate the ambiguity at the source.
You practiced a CASE predicate that combines two statuses with OR to produce a single bucket. The recurring shape: any time multiple raw values map to one report bucket, the boolean expression inside CASE does the consolidation — the surrounding COUNT(CASE) scaffold doesn't change.
