SQL interview questions & practice path

A strong answer covers

• ·WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY has aggregated them.
• ·Aggregate functions (COUNT, SUM, AVG) can appear in HAVING but not in WHERE, because aggregates don't exist yet at row-filtering time.
• ·Logical evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• ·Performance note: filtering early in WHERE shrinks the working set before the (more expensive) grouping step.

Common mistakes

• ×Saying they're interchangeable, or that HAVING is just 'WHERE for GROUP BY' without mentioning aggregates.
• ×Putting an aggregate condition in WHERE and expecting it to work.
• ×Not knowing the logical clause evaluation order when pressed on why.

A strong answer covers

• ·INNER returns only matching rows; LEFT keeps every left row and NULL-fills missing right matches; RIGHT is the mirror; FULL OUTER keeps both sides.
• ·Concrete use cases: LEFT JOIN for 'all customers including those with zero orders'; INNER for 'only customers who ordered'.
• ·Row-count intuition: joins can multiply rows when the join key isn't unique on one side — a classic silent-bug source.
• ·A strong answer sketches a two-table example and states what each join returns for it.

Common mistakes

• ×Only defining the joins without a use case for choosing one.
• ×Missing that a one-to-many join duplicates rows and inflates downstream aggregates.
• ×Confusing which side LEFT preserves.

A strong answer covers

• ·Window functions compute across a set of related rows without collapsing them the way GROUP BY does — every input row survives.
• ·Anatomy: function OVER (PARTITION BY ... ORDER BY ...); PARTITION resets the window, ORDER defines ranking/frame order.
• ·Example: SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) FROM employees;
• ·Knows RANK vs DENSE_RANK vs ROW_NUMBER tie-handling differences.

Common mistakes

• ×Reaching for GROUP BY + self-join when a window function is the direct answer.
• ×Forgetting PARTITION BY so the rank is global instead of per-department.
• ×Not knowing how ties behave across RANK/DENSE_RANK/ROW_NUMBER.

A strong answer covers

• ·Clean approach: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1 — DISTINCT handles duplicate top salaries.
• ·Alternative: MAX(salary) WHERE salary < (SELECT MAX(salary) ...), or DENSE_RANK() = 2 in a subquery for 'nth' generality.
• ·Robustness: returns NULL/empty when only one distinct salary exists — a strong answer says what happens and how the caller should handle it.
• ·Generalizes the pattern to nth-highest and mentions the trade-offs between the approaches.

Common mistakes

• ×LIMIT 1 OFFSET 1 without DISTINCT — breaks when the top salary is duplicated.
• ×Not addressing the empty/one-row edge case at all.
• ×Hardcoding for 'second' with no idea how to generalize to nth.

A strong answer covers

• ·Start with evidence: EXPLAIN/EXPLAIN ANALYZE to see the actual plan — scans vs index use, join strategy, row estimates vs reality.
• ·Common culprits: missing/unused indexes, functions wrapping indexed columns, leading-wildcard LIKE, SELECT *, stale statistics, exploding joins.
• ·Fix ladder: right index (including composite/covering), query rewrite, then schema-level answers (denormalization, materialized views, partitioning).
• ·Mentions verifying the fix under production-like data volume, and that adding an index costs write performance.

Common mistakes

• ×Jumping straight to 'add an index' without reading a query plan first.
• ×No mention of EXPLAIN at all.
• ×Ignoring that recent data growth or plan changes — not the query text — are often what changed.