Customers With Repeat Completed Orders

Given an orders table, find customers who placed at least two completed orders. Cancelled orders must not count toward the total.

Schema

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount      NUMERIC NOT NULL,
  status      TEXT NOT NULL   -- 'completed' or 'cancelled'
);

Sample data:

Task

Considering completed orders only, return:

• customer_id
• order_count — the number of completed orders for that customer

Include only customers whose completed order count is at least 2. Order by order_count descending, then customer_id ascending.

Expected output

Approach

This problem needs two filters at two different stages. WHERE status = 'completed' runs first and removes cancelled rows before any grouping happens, so they never reach the count. After grouping by customer_id, HAVING COUNT(*) >= 2 drops the groups whose surviving count is too small. The distinction between row-level (WHERE) and group-level (HAVING) filtering is the whole point.

Query

SELECT
  customer_id,
  COUNT(*) AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY order_count DESC, customer_id ASC;

Walkthrough

• WHERE status = 'completed' discards order 3 (customer 100) and order 8 (customer 103) up front.
• Remaining rows group by customer: customer 100 has 2 completed orders, customer 101 has 1, customer 102 has 3. Customer 103 has none left, so it forms no group at all.
• HAVING COUNT(*) >= 2 keeps 100 (2) and 102 (3), and drops 101 (1).
• ORDER BY order_count DESC lists 102 (3) before 100 (2).

Complexity & notes

• One filtered scan plus a grouped aggregate, O(n).
• Key pitfall: you cannot write WHERE COUNT(*) >= 2 — aggregates are not allowed in WHERE because it is evaluated before grouping. That predicate must live in HAVING.
• Equally, filtering status in HAVING would still work numerically here only if you aggregated conditionally, but doing it in WHERE is cheaper and clearer because it shrinks the input before grouping.
• Customer 103 illustrates that a group never appears if all its rows are filtered out by WHERE; there is no zero-count row to exclude.
• HAVING can reference aggregates directly; some engines also let it reference the SELECT alias, but repeating COUNT(*) is the portable form.