Counting and Filtering Groups
Finding groups that meet a threshold, the canonical 'customers with more than N orders' question.
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;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