Counting and Filtering Groups
Finding groups that meet a threshold, the canonical 'customers with more than N orders' question.
Counting and Filtering Groups is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
The Most Common Grouping Question
'Find customers with more than N orders' is the canonical GROUP BY interview problem. Variations appear constantly: products sold more than X times, departments with at least Y employees, days with over Z logins.
Every one is the same pattern: group, count, then filter the groups with HAVING.
Step One: Count Per Group
Start by counting rows within each group. Group by the key that defines a 'customer' or 'product', then apply COUNT(*).
This gives one row per group with its size. You have not filtered yet, you are just measuring each group.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;Step Two: Filter With HAVING
Now keep only the groups that meet the threshold. The condition is on an aggregate, so it must live in HAVING, not WHERE.
Read it as: 'group orders by customer, then keep customers whose count exceeds five.' This is the complete answer to the classic prompt.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;Count Distinct vs Count All
Watch the wording. 'Customers who ordered more than 3 different products' needs COUNT(DISTINCT product_id), not COUNT(*).
COUNT(*)counts rows in the group.COUNT(DISTINCT col)counts unique non-NULL values.
Interviewers slip 'different' or 'unique' into the prompt to test whether you reach for DISTINCT.
SELECT customer_id, COUNT(DISTINCT product_id) AS distinct_products
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) > 3;Filtering on SUM Instead of COUNT
The same structure handles sums. 'Customers who spent more than 1000 total' groups by customer and filters on SUM(amount).
Any aggregate can drive the HAVING condition: COUNT, SUM, AVG, MAX, MIN. Recognizing the prompt's measure tells you which aggregate to filter on.
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;Multiple Group Conditions
HAVING can combine conditions with AND/OR, including different aggregates. 'Customers with over 5 orders AND average order above 100' is one HAVING clause.
Each condition references its own aggregate; all are evaluated after grouping. This shows you can express compound business rules cleanly.
SELECT customer_id,
COUNT(*) AS orders,
AVG(amount) AS avg_order
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5 AND AVG(amount) > 100;Combine WHERE and HAVING
Often you must filter rows before counting. 'Among orders from this year, customers with more than 5 orders' uses WHERE for the date and HAVING for the count.
WHERE shrinks the rows first, then grouping and the HAVING threshold apply. Getting both clauses right in one query is the mid-level signal.
SELECT customer_id, COUNT(*) AS orders_this_year
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 5;Finding Duplicates
A close cousin: 'find duplicate emails.' Group by the column that should be unique and keep groups with a count above one.
This single pattern detects duplicate keys, repeated transactions, or any value that appears more than it should. Interviewers ask it to test whether you see grouping as a dedup tool.
SELECT email, COUNT(*) AS times_seen
FROM users
GROUP BY email
HAVING COUNT(*) > 1;Groups That Meet a Count Exactly
Thresholds are not always 'greater than.' 'Customers with exactly one order' uses HAVING COUNT(*) = 1; 'at least 3' uses >= 3.
Listen for the boundary word in the prompt: 'more than', 'at least', 'exactly', 'fewer than'. Each maps to a different comparison operator, and interviewers test that precision.
-- One-time customers
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1;Returning Only the Keys
Sometimes the prompt wants just the qualifying identifiers, not the counts, often to feed another query. You still group and filter, but SELECT only the key.
You may then wrap this in an IN subquery or a join to pull full records for those customers. Knowing this composition is a senior touch.
SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5;Interview Playbook
For any 'groups meeting a threshold' question, walk through four steps aloud: (1) identify the grouping key, (2) pick the aggregate measure, (3) decide the comparison operator from the wording, (4) put row filters in WHERE and the aggregate filter in HAVING.
This template solves the entire family of counting-and-filtering problems.
Quick Check
Pick the correct query for the prompt.
Recap
Pattern: group by the key, aggregate, filter groups with HAVING. This solves 'more than N orders', 'top spenders', duplicates, and one-time customers.
- Use
COUNT(*)for rows,COUNT(DISTINCT col)for unique values. - Match the comparison operator to the prompt wording.
- Row filters go in WHERE, aggregate filters in HAVING.
- SELECT just the key to feed an IN subquery or join.
Frequently asked questions
Is the “Counting and Filtering Groups” lesson free?
Yes — the full text of “Counting and Filtering Groups” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.
What will I learn in “Counting and Filtering Groups”?
Finding groups that meet a threshold, the canonical 'customers with more than N orders' question. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Interview Prep?
No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Counting and Filtering Groups” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Interview Prep lesson?
Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- The GROUP BY Rule for SELECT Columns
- HAVING vs WHERE
- Grouping by Multiple Columns and Expressions
- Counting and Filtering Groups