Data Analytics Interview Q&A: WHERE, HAVING and Trustworthy Grouped Results
Answer SQL grouping interview questions with a tested query, boundary cases and reporting checks.
1. How do WHERE and HAVING differ?
WHERE filters input rows; HAVING filters the groups produced by aggregation. Explain the business rule before writing either clause. If an interviewer asks for completed sales by channel with revenue above 200, there are two separate decisions: exclude unfinished sales and then test each channel's completed total.
An effective answer starts with the reporting period, currency and row meaning. Our exercise treats each row as one sale in the same currency and period. It contains invented practice data, not AI Leaf's sales.
2. Can you demonstrate the difference with SQL?
This PostgreSQL example also works in the local PostgreSQL practice runtime used for this draft's check.
WITH sales(channel, status, amount) AS (
VALUES ('online','completed',150),
('online','completed',100),
('store','completed',180),
('store','cancelled',90)
)
SELECT channel, COUNT(*) AS sales_count, SUM(amount) AS revenue
FROM sales
WHERE status = 'completed'
GROUP BY channel
HAVING SUM(amount) > 200
ORDER BY channel;Expected result: online, sales_count 2, revenue 250. Store contributes 180 after the cancelled row is removed, so it does not pass the group condition. Counting all rows first and filtering the status afterwards answers a different question.
3. Should a cancelled amount be subtracted?
Not automatically. In this dataset a cancelled record is excluded. In a ledger, refunds may be separate negative transactions that belong in net revenue. Ask how the source represents a reversal and write that assumption in your answer. A query can run successfully while applying the wrong business meaning.
4. What checks would you make before presenting the dashboard?
Reconcile the completed input total of 430 against the grouped total before HAVING removes any channels. Then inspect the excluded 90, check whether a repeated sale identifier exists, and confirm that every amount uses the same currency. A chart showing only qualifying channels will total 250; that is a filtered view, not the entire completed business total.
5. How would you handle missing amounts?
First establish whether NULL means unknown, not applicable or zero. Replacing unknown revenue with zero without a rule can make a report look more complete than it is. Report missing-value counts separately and agree how incomplete records affect each KPI. Do not use a visually tidy dashboard as evidence that the input is reliable.
Practice interview follow-up
Add a completed store sale of 50. Predict the result before execution: online remains 250 with two sales; store becomes 230 with two sales and now qualifies. Change the threshold to greater than 250 and explain why neither channel qualifies. Compare it with greater than or equal to 250, which keeps online. This boundary exercise makes the requirement precise.
Prepared by AI Leaf Global Learn. Classroom learning is available in Mudichur/Tambaram, Chennai, and online learning across India; confirm current batches with the team. These are audience details, not a promise of employment or an additional branch.