Brightlane's loyalty program team is identifying high-engagement customers for a rewards campaign.
Write a query to return the ID and name of every customer who has placed more than 3 orders on record.
Assumptions:
- A customer's order count is the number of orders linked to that
customer_id. - Only customers whose order count is strictly greater than
3should appear.
Output:
- One row per qualifying customer, with columns
idandname.
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
c.id,
c.name
FROM
customers c
WHERE
(
SELECT
COUNT(*)
FROM
orders o
WHERE
o.customer_id = c.id
) > 3The shape
The qualifying condition is "this customer has more than three orders," which is a per-customer count compared to a constant. A correlated \COUNT(*)\ in the \WHERE\ clause computes that count once per customer and the outer query keeps only the customers where it exceeds \3\.
Clause by clause
- \
SELECT c.id, c.name\returns the two columns the spec asks for, taken from the customer row. - \
FROM customers c\reads every customer. The outer alias \c\is the row the inner subquery correlates against. - \
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 3\is the correlated filter. The inner \WHERE o.customer_id = c.id\ties the count to the outer customer, so the subquery returns one integer per customer. The outer comparison \> 3\keeps the customer only when that integer is strictly greater than three. A customer with exactly four orders qualifies; a customer with exactly three does not, which matches the "strictly greater than \3\" wording.
Why this and not a \JOIN\ plus \GROUP BY\ plus \HAVING\
\SELECT c.id, c.name FROM customers c JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name HAVING COUNT(*) > 3\ returns the same customers. The two forms are interchangeable for this question, and the planner often rewrites one into the other internally. The correlated form is shorter and does not require a \GROUP BY\, which is sometimes the more readable expression when the answer is a yes/no filter on a per-outer-row count rather than a grouped aggregation that needs to be displayed.
You practiced a correlated COUNT(*) in WHERE — the per-customer count is computed inside the predicate, so the outer record is kept only when the count beats the threshold.
