Delivered Orders per Customer — GROUP BY in SQL

The problem

Brightlane's CRM team is building a customer success report and needs to see how many fulfilled orders each buyer has accumulated.

Write a query to return each customer's ID alongside the count of their delivered orders.

Assumptions:

The orders table contains every order Brightlane has processed.
A delivered order has status = 'delivered'; only those orders should be counted.
Customers with zero delivered orders will not appear — WHERE removes their rows before grouping, so they are simply absent from the output.

Output:

One row per customer with at least one delivered order, with columns customer_id and delivered_order_count.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  customer_id,
  COUNT(*) AS delivered_order_count
FROM
  orders
WHERE
  status = 'delivered'
GROUP BY
  customer_id

The shape

WHERE runs before GROUP BY. The query first throws away every order whose status is not 'delivered', then partitions the survivors by customer_id, then counts rows in each partition. The count is therefore the count of delivered orders per customer, not the count of all orders per customer.

Clause by clause

SELECT customer_id, COUNT(*) AS delivered_order_count returns each buyer's ID and their delivered order count. customer_id is a plain column that appears in GROUP BY, and COUNT(*) is the aggregate.
FROM orders is the input population, the full order history.
WHERE status = 'delivered' filters first. Pending, shipped, and cancelled orders are removed from the row set before any grouping happens.
GROUP BY customer_id partitions the remaining delivered rows by buyer. COUNT(*) then runs once per buyer's bucket.

The trap

The prompt notes that customers with zero delivered orders simply do not appear in the output. That is the natural consequence of WHERE removing all their rows before grouping. If a customer's every order is cancelled, none of their rows survive the filter, so there is no bucket to count, so no row in the result. There is no 0 row for them. If you need every customer to appear with a zero where appropriate, that is a different query shape and not what this one returns.

You practiced combining WHERE, GROUP BY, and an aggregate. The evaluation order matters: WHERE filters rows out of the input, then GROUP BY partitions what's left, then the aggregate runs per partition — which is why customers with zero matching rows simply don't show up.

Return each customer's ID alongside the count of their delivered orders

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.