Guide
SQL GROUP BY and HAVING: Patterns for Interview Questions
Learn the GROUP BY / HAVING patterns behind duplicate detection, threshold filtering, and per-group aggregation with real examples.
The rule that defines GROUP BY
Every column in SELECT that is not wrapped in an aggregate must appear in GROUP BY. Violating this is the most common compile-time error in SQL interviews, and silently wrong in databases that allow it.
WHERE filters rows, HAVING filters groups
WHERE runs before grouping, so it cannot reference aggregates. HAVING runs after, so it can: HAVING COUNT(*) > 1 finds duplicates, HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM product) finds customers who bought everything.
In SQLite, HAVING can reference SELECT aliases, which keeps the query readable.
COUNT(DISTINCT ...) and the NULL question
COUNT(*) counts rows. COUNT(col) counts non-NULL values. COUNT(DISTINCT col) counts distinct non-NULL values. 'How many distinct users per day' is COUNT(DISTINCT user_id) — the DISTINCT is easy to forget.
Practice what you learned
- easyActive Users Per Day
- easyAverage Process Time Per Machine
- easyClasses With At Least 5 Students
- easyDuplicate Emails
- easySecond Highest Salary
- easySubjects Taught By Each Teacher
- easyVisits Without Transactions
- mediumCustomers Who Bought All Products
- mediumDepartments by Average Salary
- mediumFirst and Last Order Per Customer
- mediumGame Play Analysis IV
- mediumImmediate Food Delivery
- mediumMonthly Sales Ranking
- mediumPercentage of Total Sales
- mediumPivot Quarterly Sales
- mediumRunning Total
- mediumSeven-Day Rolling Average
- mediumUsers With the Most Friends