See exactly what you get for ₹99

SELECT dept, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'        -- row filter, before grouping
GROUP BY dept
HAVING COUNT(*) > 5;           -- group filter, after aggregation

Say this: WHERE filters individual rows before the grouping happens, so at that point COUNT(*) does not exist yet — the aggregate is only computed once the rows are grouped. HAVING runs after, which is where an aggregate condition belongs. The execution order is FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

Follow-up: "Why can't you use a SELECT alias in WHERE, then?" → Same reason — SELECT is evaluated after WHERE. It does work in ORDER BY, which runs later still.

Don’t say: "HAVING is just WHERE for GROUP BY" without the before-versus-after explanation — that sentence is what is actually being graded.

Say this: The mean is 13.6 LPA and the median is 5. Not a single person in that team earns anything close to 13.6 — one extreme value has dragged it up — so the median is the honest answer for a typical salary. The rule I follow: median when the distribution is skewed or has outliers, which income, order values and delivery times always are; the mean when totals matter, because mean times count gives the total and median does not.

Follow-up: "So would you ever report the mean here?" → Yes, for total payroll, since that is a sum. I would show both and explain the gap — that gap is itself the finding, because it tells you one person dominates the number.

Don’t say: "The average is 13.6" and stop. Quoting a mean over skewed data without flagging it is the single most common analyst mistake, and this question exists to test for exactly that.

Say this: It is not the order you write it in. The engine goes FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then DISTINCT, then ORDER BY, then LIMIT. That order explains two things people trip on constantly: you cannot use a SELECT alias in WHERE, because SELECT has not run yet — but you can use it in ORDER BY, which runs after. And an aggregate cannot appear in WHERE, because the grouping has not happened; that is what HAVING is for.

Follow-up: "So how do you filter on an alias?" → Wrap the query in a subquery or a CTE and filter in the outer query, or repeat the expression in the WHERE clause.

Don’t say: "SELECT runs first because it is written first" — that is the exact misconception the question is testing.

Say this: WHERE filters individual rows before grouping; HAVING filters the groups after aggregation. So a row-level condition goes in WHERE and an aggregate condition goes in HAVING. Putting both in one query makes the difference obvious:

SELECT city, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'          -- row filter, before grouping
GROUP BY city
HAVING SUM(amount) > 100000    -- group filter, after aggregation
ORDER BY revenue DESC;

Follow-up: "Could you put status = 'paid' in HAVING instead?" → Some engines allow it, but it is wrong in spirit and slower — WHERE cuts rows before the expensive grouping, HAVING makes the engine group everything first and then throw work away.

Don’t say: "HAVING is WHERE for GROUP BY" without the before-versus-after distinction — that is the actual answer.

Say this: It depends on how many orders each customer has, and that is the point of the question. A LEFT JOIN keeps every customer row and matches orders to it — so the 30 customers with no orders appear once each with NULLs, and the 70 with orders appear once per order. If those 70 placed 250 orders between them, the result is 280 rows, not 100. An INNER JOIN would give 250.

The trap this exposes: joining a dimension to a fact table multiplies rows, which is exactly how a report accidentally doubles its revenue. If I only need a count per customer, I aggregate — LEFT JOIN ... GROUP BY customer — and I use COUNT(o.id), not COUNT(*), because COUNT(*) counts the joined row and returns 1 for a customer with no orders.

SELECT c.name, COUNT(o.id) AS orders          -- 0 for non-buyers
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

SELECT c.name FROM customers c                 -- customers who never ordered
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Follow-up: "Condition in the ON clause versus the WHERE clause of a LEFT JOIN?" → In ON it filters what gets joined and keeps all left rows; in WHERE it filters after the join and silently turns your LEFT JOIN into an INNER JOIN.

Don’t say: "100 rows" — that is only true if every customer has at most one order.

-- 1. subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. window function
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

Say the duplicates point unprompted — it is what separates you: if two people share the top salary, LIMIT 1 OFFSET 1 returns that same top figure again, and RANK() skips rank 2 entirely. DENSE_RANK and the MAX-subquery both give the genuinely second-distinct salary.

Follow-up: "Nth highest?" → Change the rank filter to N. "Second highest per department?" → Add PARTITION BY department_id inside the OVER clause.

Don’t say: only the LIMIT/OFFSET version. It is the fastest to type and the easiest to fail on the follow-up.

Say this: RANK gives 1, 1, 3 — ties share the rank and then it skips. DENSE_RANK gives 1, 1, 2 — ties share and nothing is skipped. ROW_NUMBER gives 1, 2, 3 — it always numbers sequentially and breaks the tie arbitrarily, so which of the two 50s gets 1 is not deterministic unless you add a tie-breaker to the ORDER BY.

When I use each: DENSE_RANK for "the Nth distinct value". ROW_NUMBER for deduplication — take row number 1 per group — and for "top N rows regardless of ties". RANK when the competition-style gap is what the business actually wants.

Follow-up: "Deduplicate a table keeping the latest record per customer?" → ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) in a CTE, then filter to 1. That is the single most useful window pattern in analytics work.

Don’t say: that RANK and DENSE_RANK are the same — the skip is the whole question.

Good answer hits: FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT · it explains why a SELECT alias works in ORDER BY but not in WHERE · and why an aggregate cannot go in WHERE.

One-liner: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT — which is why aliases work in ORDER BY and not in WHERE.

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Good answer hits: the window version is DENSE_RANK() OVER (ORDER BY salary DESC) filtered to 2 · LIMIT 1 OFFSET 1 returns the same top figure again when two people tie at the top · RANK skips rank 2 after a tie, DENSE_RANK does not.

Answer: RANK 1, 1, 3 · DENSE_RANK 1, 1, 2 · ROW_NUMBER 1, 2, 3.

Good answer also hits: DENSE_RANK for "Nth distinct value" · ROW_NUMBER for deduplication — PARTITION BY key ORDER BY updated_at DESC, keep row 1 · ROW_NUMBER breaks ties arbitrarily unless you add a tie-breaker to the ORDER BY.