Customers Above an Order Count Threshold

The problem

Brightlane's CRM team is building a high-value customer list and needs to identify buyers with substantial order history.

Write a query to return the customer ID and total order count for every customer who has placed more than three orders.

Assumptions:

The orders table contains every order Brightlane has processed.
The threshold (> 3) applies to the per-customer count.
Each customer's order count is computed first, then the per-customer counts are narrowed to those above the threshold.

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,
  order_count
FROM
  (
    SELECT
      customer_id,
      COUNT(*) AS order_count
    FROM
      orders
    GROUP BY
      customer_id
  ) AS customer_orders
WHERE
  order_count > 3

The shape

The inner query counts orders per customer and the outer query filters that result down to customers with more than three. The derived table is what lets WHERE reach a value — the per-customer count — that doesn't exist until after the grouping has run.

Clause by clause

The inner block computes one row per customer with their order count:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id

FROM (...) AS customer_orders wraps that result as a derived table. The alias customer_orders is mandatory — drop it and PostgreSQL raises a syntax error before the query runs.
WHERE order_count > 3 filters the derived table's rows. order_count is a real column at this layer, so a plain WHERE comparison works the way it would against any table.
SELECT customer_id, order_count returns the two columns the CRM team's high-value list needs. The outer query can only see columns the inner SELECT exposed.

You practiced wrapping an aggregate query as a derived table so the outer query can filter on the aggregate result with WHERE. The recurring shape: any time aggregation produces a value that needs to be filtered, joined, or further aggregated, a derived table makes that second pass possible.

Return the customer ID and total order count for every customer who has placed more than three orders

The shape

Clause by clause

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