SQL interview questions

What 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.

Where people lose the point

• ×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.

What 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.

Where people lose the point

• ×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.

What 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.

Where people lose the point

• ×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.

What 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.

Where people lose the point

• ×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.

What 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.

Where people lose the point

• ×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.