Count of Frequent Customers — Derived Tables in SQL

The problem

Brightlane's growth team needs a single figure: the number of customers who have placed three or more orders.

Write a query to return that count in a single column named qualifying_customers.

Assumptions:

The orders table contains every order Brightlane has processed.
The inner result produces one row per customer with their order count; the outer query narrows that result to qualifying customers and counts them.
The output is a single number — the count of customers who survive the per-customer threshold.

Output:

A single row with one column, qualifying_customers.

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
  COUNT(*) AS qualifying_customers
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 produces one row per customer with their order count; the outer query filters those rows down to qualifying customers and then counts the survivors. Two stacked aggregations — the second one runs over the result of the first — and the derived table is the seam that makes the stacking explicit.

Clause by clause

The inner block computes a per-customer order count:

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

One row per customer, with order_count as the running tally of their orders. - FROM (...) AS customer_orders materialises that result as a derived table — a real, named source the outer query can read like any other table. - WHERE order_count >= 3 keeps only customers at or above the threshold. The comparison is inclusive: a customer with exactly three orders qualifies. - SELECT COUNT(*) AS qualifying_customers then aggregates the surviving rows. The outer COUNT(*) counts customers, not orders — each row of the derived table is already one customer, so counting rows here is counting qualifying customers. The result is 48.

You practiced an aggregate over the result of an aggregate. The recurring shape: derived tables make two-pass aggregation natural — the inner pass produces one row per customer, the outer pass produces one row total.

Return that count in a single column named `qualifying_customers`

The shape

Clause by clause

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