Lesson and practical work
Professional outcomes
- Aggregate metrics by dimensions at a defensible grain.
- Explain WHERE versus HAVING in business terms.
- Avoid double counting when grouping joined tables.
- Interpret segment differences without ignoring size, mix, or incomplete periods.
Need a refresher?Rows first, groups secondView concise foundation
WHERE filters source rows before grouping. GROUP BY defines the dimensions of the result. HAVING filters the aggregated groups. Every selected non-aggregate field normally belongs in GROUP BY.
Find regions with material quarterly revenue
Management wants completed-order revenue and order volume by region, but only regions above $250,000 should appear in the executive review.
Filter the population, aggregate, then filter groups
SELECT
c.region,
COUNT(DISTINCT o.order_id) AS orders,
SUM(o.net_revenue) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
AND o.order_date >= '2026-04-01'
AND o.order_date < '2026-07-01'
GROUP BY c.region
HAVING SUM(o.net_revenue) >= 250000
ORDER BY revenue DESC;WHERE defines eligible orders. HAVING applies the materiality threshold after regional revenue exists. Keep an unfiltered reconciliation version so excluded regions remain visible in QA.
What the result means—and does not mean
The output identifies large-revenue regions, not necessarily high-performing regions. A large region may underperform target; a small region may grow rapidly. Add comparison and scale context before ranking performance.
Analyst's checklist
- Reconcile grouped totals before HAVING.
- Count NULL and unknown dimension values.
- Confirm dimension keys are unique.
- Compare rates alongside absolute totals.
- Check whether all periods are complete.
Common mistakes and failure modes
- Using HAVING for ordinary row filters without reason.
- Dropping NULL groups silently.
- Ranking totals and calling them efficiency.
- Grouping by a field added from a duplicated dimension.
Challenge yourself
A report shows the highest-revenue region last in average margin percentage.
- Would you call it the best region?
- Which absolute and rate measures belong together?
- How would product mix affect interpretation?
Show hint
Scale, profitability, and target attainment answer different questions.
Show analytical guidance
Present revenue contribution, margin dollars, margin rate, and relevant targets. Then test whether product or customer mix explains the rate difference.
How this may appear in an interview
What is the difference between WHERE and HAVING?
What a strong candidate should communicate
WHERE filters rows before aggregation; HAVING filters groups after aggregation. A strong answer includes a practical example and notes that clear query design matters more than memorizing clause order.
Can HAVING be used without GROUP BY, and would you normally do so?
Where this skill becomes evidence
Show a grouped KPI table plus the reconciliation query that proves segment totals match the overall measure before presentation filters.
Starter SQL
SELECT
region,
SUM(net_revenue) AS revenue
FROM regional_orders
WHERE /* row filters */
GROUP BY region
HAVING /* group filter */;Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
Why does a revenue threshold belong in HAVING?
Show guidance
How can grouped totals be reconciled when HAVING hides small groups?
Show guidance
Design a balanced regional performance table.
Show guidance
Move from reviewing expert reasoning to performing the analysis.
Guided practice can add the synthetic dataset, multi-step assignment, validation checks, solution comparison, project workflow, and progress tracking.
- Work with the data
- Make and defend assumptions
- Validate the result
- Build portfolio evidence