Order Count per Customer with a Subquery — Subqueries vs CTEs vs Joins in SQL

The problem

Scenario: Brightlane's CRM team needs a complete list of customers paired with each customer's order count, including customers who have placed no orders.

Task: Write a query to return each customer's name and their order count, with the count reported as a missing value for customers who have no orders on record.

Assumptions:

The result covers every customer.
A customer with no orders on record appears with order_count reported as a missing value (not 0).

Output:

One row per customer.
Columns in this order: customer_name, 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
  c.name AS customer_name,
  order_counts.order_count
FROM
  customers c
  LEFT JOIN (
    SELECT
      customer_id,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      customer_id
  ) AS order_counts ON order_counts.customer_id = c.id

The shape

A derived table pre-aggregates orders into one row per customer, and a LEFT JOIN from customers keeps every customer whether or not they appear in that aggregate. Customers with no orders end up matched to no row in the derived table, so order_count falls out as NULL on its own.

Clause by clause

FROM customers c is the driving side; every customer is preserved.
LEFT JOIN (SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id) AS order_counts ON order_counts.customer_id = c.id aggregates orders down to one row per customer who has any, then joins that result back to customers on the customer id. The pre-aggregation is the load-bearing step; without it, the join would multiply each customer row by their order count.
SELECT c.name AS customer_name, order_counts.order_count returns the customer's name and the count from the derived table, which is NULL whenever the left join found no match.

Why this and not aggregating after the join

SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.name also works, but it forces GROUP BY over every row of customers joined to every order. Pre-aggregating in a derived table aggregates first on the smaller side, then joins one row per customer to customers — cleaner intent and typically faster as orders grows. Either shape is correct.

The trap

The prompt requires NULL (not zero) for customers with no orders. Pre-aggregating preserves that automatically: the unmatched left-side rows pick up NULL from order_counts.order_count. Wrapping the column in COALESCE(..., 0) would change the contract and produce the wrong result here.

You practiced precomputing per-customer counts in a derived table before pairing it with the full customer list — preserving every customer while leaving no-order customers with a missing count.

Return each customer's name and their order count, with the count reported as a missing value for customers who have no orders on record

The shape

Clause by clause

Why this and not aggregating after the join

The trap

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