Customers with More Than Three Orders

The problem

Brightlane's growth team is analysing repeat-purchase behaviour to build a loyalty programme.

Write a query to return the customer ID and order count for every customer with more than three orders on record.

Assumptions:

The orders table contains every order Brightlane has processed.
The threshold is on the per-customer count of orders — three orders does not qualify; four or more does.
The condition applies to the per-customer count, not to individual orders.

Output:

One row per qualifying customer, with columns customer_id and 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 order_count
FROM
  orders
GROUP BY
  customer_id
HAVING
  COUNT(*) > 3

The shape

"More than three orders" is a per-customer condition, not a per-row one. GROUP BY customer_id builds the per-customer counts; HAVING COUNT(*) > 3 keeps only the groups that clear the threshold. The result is one row per qualifying customer — customer_id 17 with 5 orders, customer_id 51 with 4, and so on.

Clause by clause

SELECT customer_id, COUNT(*) AS order_count returns the grouping column with the count for each surviving group.
FROM orders is the source set: every order Brightlane has processed.
GROUP BY customer_id collapses the order rows into one row per customer. After this clause, each row in the working set represents one customer with their order count attached.
HAVING COUNT(*) > 3 filters those per-customer rows. Customers with three or fewer orders drop out; four or more survive.

Why this and not `WHERE COUNT(*) > 3`

WHERE runs before grouping happens, when the only thing in scope is an individual order row. At that point COUNT(*) has no meaning — there is no group yet to count, and PostgreSQL raises an error. HAVING runs after GROUP BY has produced the per-customer groups, which is the only moment the per-customer count exists.

You practiced filtering on an aggregate result with HAVING. The recurring rule: WHERE filters rows before they reach the aggregate; HAVING filters groups after the aggregate has computed — "more than three orders" is a group-level condition, so it belongs in HAVING.

Return the customer ID and order count for every customer with more than three orders on record

The shape

Clause by clause

Why this and not `WHERE COUNT(*) > 3`

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

The shape

Clause by clause

Why this and not WHERE COUNT(*) > 3

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

Why this and not `WHERE COUNT(*) > 3`