Brightlane's CRM team needs to track delivery performance at the customer level.
Write a query to return every customer ID, the total number of orders they have placed, and the number of those orders with status = 'delivered'.
Assumptions:
- The
orderstable has one row per order with acustomer_idand astatus. - Each
customer_idwith at least one order should appear once. - For each customer, the total count covers every order linked to that
customer_id. The delivered count covers only orders withstatus = 'delivered'.
Output:
- One row per customer with at least one order, with columns
customer_id,total_orders, anddelivered_orders.
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(*) AS total_orders,
COUNT(*) FILTER (
WHERE
status = 'delivered'
) AS delivered_orders
FROM
orders
GROUP BY
customer_idThe shape
GROUP BY customer_id collapses the order rows into one row per customer, and the FILTER on the second COUNT restricts that count to delivered orders only — within the same partition that the unfiltered COUNT(*) is operating on.
Clause by clause
SELECT customer_id, COUNT(*) AS total_orders, COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_ordersreturns the customer, their total order count across every status, and their count of delivered orders alone. Both counts evaluate inside the sameGROUP BYpartition; theFILTERnarrows the second count without touching the first.FROM ordersreads the order records.GROUP BY customer_idpartitions the rows into per-customer groups. Each customer becomes one output row, and the two counts run independently inside that group.
Why this and not COUNT(CASE WHEN status = 'delivered' THEN 1 END)
Both produce the same delivered count per customer. PostgreSQL rewrites them similarly under the hood. FILTER reads as one decision — count these rows, restricted to this condition — instead of a per-row CASE returning 1 or NULL inside the aggregate's argument. On a per-group aggregation like this, where the unfiltered total and the conditional count sit next to each other, the FILTER syntax keeps the parallel between them obvious: same aggregate, same partition, one extra restriction on the second.
You practiced FILTER on a per-group aggregation — within each GROUP BY partition, one count covers every record while another covers only the records meeting a condition.
