Scenario: Brightlane's CRM team is preparing a re-engagement campaign and needs every customer classified as 'Active' (has placed at least one order) or 'Inactive' (has placed none).
Task: Write a query to return each customer's id, name, and customer_status — set to 'Active' if they have placed at least one order and 'Inactive' otherwise.
Assumptions:
- A customer's
customer_statusis'Active'when at least one order is on record for them and'Inactive'when none is on record. - The result covers every customer.
Output:
- One row per customer.
- Columns in this order:
customer_id,customer_name,customer_status. - Sorted by
customer_nameascending.
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
c.id AS customer_id,
c.name AS customer_name,
CASE
WHEN COUNT(o.id) > 0 THEN 'Active'
ELSE 'Inactive'
END AS customer_status
FROM
customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY
c.id,
c.name
ORDER BY
c.nameThe shape
COUNT(o.id) returns the number of matched orders per customer, and a CASE expression over that count labels each customer 'Active' or 'Inactive'. The LEFT JOIN is what makes the count reach zero for customers with no orders — without it, those customers would be dropped before the count step ran.
Clause by clause
SELECT c.id AS customer_id, c.name AS customer_name, CASE WHEN COUNT(o.id) > 0 THEN 'Active' ELSE 'Inactive' END AS customer_statusreturns each customer's id and name alongside the status label. TheCASEevaluates the count once the group has been formed; a customer with at least one matched order gets'Active', and a customer whoseCOUNT(o.id)is0falls through to'Inactive'.FROM customers c LEFT JOIN orders o ON o.customer_id = c.idpairs each customer with their orders. TheLEFT JOINpreserves customers who have none; for those customers,o.idis NULL.GROUP BY c.id, c.namecollapses the customer-and-orders rows back to one row per customer.ORDER BY c.namesorts the report alphabetically by customer name.
Why this and not COUNT(*) > 0
COUNT(*) counts rows regardless of whether the right side of the LEFT JOIN matched. For a customer with no orders, the LEFT JOIN still emits one placeholder row with every orders column set to NULL, and COUNT(*) would count it as 1. Every customer would arrive at the CASE with a count of at least one, so every customer would be labeled 'Active' and the 'Inactive' branch would never fire. COUNT(o.id) ignores NULL values, which means the placeholder row contributes nothing — the orderless customer's count lands on 0 and the 'Inactive' branch fires.
You practiced classifying parents by whether they have any matching children — translating a count-based existence check into a categorical label per customer.
