Brightlane's support team is investigating order patterns and needs each customer's activity broken down by pipeline stage.
Write a query to return each unique customer-status pairing and the number of orders that fall into it.
Assumptions:
- The
orderstable contains every order Brightlane has processed. - The output has one row for each unique combination of
customer_idandstatusthat appears in the data — a customer with orders in three different statuses contributes three rows.
Output:
- One row per unique (
customer_id,status) combination, with columnscustomer_id,status, andorder_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,
status,
COUNT(*) AS order_count
FROM
orders
GROUP BY
customer_id,
statusThe shape
Listing two columns in GROUP BY makes the unit of aggregation the unique pair, not the individual columns. Every distinct (customer_id, status) combination that appears in the data becomes its own bucket, and COUNT(*) runs once inside each one. Customer 8 with five delivered orders is one row in the output. Customer 8 with one shipped order would be a second row.
Clause by clause
SELECT customer_id, status, COUNT(*) AS order_countreturns the two grouping columns and the per-pair count. Bothcustomer_idandstatusare in the SELECT list because both appear inGROUP BY.FROM ordersreads the full order table as input.GROUP BY customer_id, statuspartitions by the unique combination. The order in which the two columns are listed does not change the partitioning (the same pairs result either way); it only changes how a database tool might choose to sort or display the output.
Why this and not two queries
The support team could pull a per-customer count and a per-status count separately and try to reconstruct the breakdown by hand. A two-column GROUP BY returns the breakdown directly. Customer 38 shows up three times because they have orders in three different statuses, with delivered, pending, and cancelled each getting their own row. That is the shape an investigation into a single customer's pipeline actually needs.
The trap
Grouping by two columns produces more output rows than grouping by either one alone, often many more. A single-column GROUP BY status returns four rows. A single-column GROUP BY customer_id returns one row per customer. The two-column version returns one row per unique pair, which can be close to the row count of the source table on a high-cardinality combination. Check the result-set size against expectation, because a misread of the grouping unit is the easiest way to confuse a per-pair count with a per-customer count.
You practiced grouping by two columns at once. The rule is the same as one-column grouping: each unique combination of the listed columns forms its own group, and the aggregate runs once per combination.
