SQLMaxx
Sign in Start free
N015-M1 Tier 2 · Core SQL · medium ecommerce · Brightlane

Return the customer ID and count of delivered orders for every customer with **more than two** fulfilled orders

Part of HAVING in SQL

The problem

Brightlane's fulfilment team is reviewing high-volume customers for a dedicated-account programme.

Write a query to return the customer ID and count of delivered orders for every customer with more than two fulfilled orders.

Assumptions:

  • The orders table contains every order Brightlane has processed.
  • A delivered order has status = 'delivered'; only delivered orders should be counted.
  • The status condition is a per-row qualifier (rejecting non-delivered rows before the count runs); the > 2 threshold is a per-group qualifier (rejecting customers whose delivered count is too low).

Output:

  • One row per qualifying customer, with columns customer_id and delivered_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 delivered_count
FROM
  orders
WHERE
  status = 'delivered'
GROUP BY
  customer_id
HAVING
  COUNT(*) > 2

The shape

Two filters at two different stages. WHERE status = 'delivered' is a per-row qualifier — it eliminates non-delivered orders before any counting happens. HAVING COUNT(*) > 2 is a per-group qualifier — it eliminates customers whose surviving delivered-order count is too low. Together they answer "customers with more than two delivered orders" rather than "customers with more than two orders of any kind."

Clause by clause

  • SELECT customer_id, COUNT(*) AS delivered_count returns each qualifying customer's id alongside their count of delivered orders. The COUNT(*) operates on the filtered, grouped row set, not the full orders table.
  • FROM orders is the source set.
  • WHERE status = 'delivered' runs first. Every order whose status is anything other than 'delivered' drops out before the next clause sees it. The working set after this filter contains only delivered orders.
  • GROUP BY customer_id partitions the surviving rows by customer. Each group's row count is the customer's number of delivered orders.
  • HAVING COUNT(*) > 2 filters those groups. Customers with two or fewer delivered orders drop out; three or more survive. customer_id 1 has five delivered orders and qualifies; a customer whose only orders are cancelled never enters the grouping at all.

Why this and not all in HAVING

Both filters could technically live in HAVING. HAVING status = 'delivered' AND COUNT(*) > 2 is valid SQL, but it forces PostgreSQL to group every customer's entire order history first and then discard the non-delivered rows after the fact. Pushing the status filter into WHERE removes those rows before grouping, so the groups are smaller and the count is correct on the first pass.

The rule: per-row conditions belong in WHERE, per-group conditions belong in HAVING. A condition on status can be decided from a single order. A condition on COUNT(*) requires the group to exist before the comparison can run.

You practiced combining WHERE and HAVING in the same query. Per-row conditions go in WHERE; per-group conditions go in HAVING — that division of labour is the everyday shape of a grouped query with filters.

How you actually get good at SQL

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

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.