SQL interviews test both correctness and depth. A junior engineer writes a query that returns the right rows; a senior engineer explains why it is fast, when it will fall over, and how to fix it. This guide covers the questions that separate levels - from join semantics to isolation levels to window functions.
JOIN
The single most-tested SQL concept in data and backend interviews
EXPLAIN
Reading query plans is the fastest way to diagnose slow queries
4
Standard isolation levels every backend engineer should be able to name and contrast
Join types
Misunderstanding NULLs in outer joins is the most common SQL mistake in interviews. Know exactly which rows each join type produces.
| Join | Rows returned | Typical use case |
|---|---|---|
| INNER JOIN | Only rows where the condition matches in both tables. | The default. Use when you only want records that have a match on both sides. |
| LEFT (OUTER) JOIN | All rows from the left table; matched rows from the right; NULLs on the right where there is no match. | Find all orders and their customers, including orders with no customer (orphaned records). |
| RIGHT (OUTER) JOIN | All rows from the right table; NULLs on the left where there is no match. | Equivalent to a LEFT JOIN with tables swapped. Rarely preferred - rewrite as LEFT JOIN for readability. |
| FULL OUTER JOIN | All rows from both tables; NULLs on whichever side has no match. | Reconciling two data sets - find rows in A not in B and rows in B not in A in one pass. |
| CROSS JOIN | Cartesian product - every row in A paired with every row in B (m x n rows). | Generating all combinations (date ranges, test cases). Almost always intentional; an accidental CROSS JOIN on large tables is catastrophic. |
| SELF JOIN | A table joined to itself, with two aliases. | Hierarchical data (employees and managers in the same table), comparing rows within the same table. |
NULLs in join conditions
NULL = NULL evaluates to NULL (not TRUE) in SQL. A row where the join column is NULL will never match - even in a self-join. Use IS NULL / IS NOT NULL explicitly, or COALESCE to substitute a sentinel value.
GROUP BY, HAVING, and WHERE
| Question | Sharp answer |
|---|---|
| Execution order of a SELECT? | FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT. Knowing this order explains every "you can't use an alias in WHERE" gotcha. |
| WHERE vs HAVING? | WHERE filters rows before grouping - it cannot reference aggregate functions. HAVING filters groups after aggregation - it can reference aggregates. WHERE is more efficient because it reduces rows before the aggregation step. |
| What columns can appear in SELECT with GROUP BY? | Only columns in the GROUP BY clause and aggregate expressions (COUNT, SUM, MAX, etc.). Selecting a non-grouped, non-aggregated column is a SQL error in standard SQL (MySQL historically permitted it but with undefined results). |
| COUNT(*) vs COUNT(col)? | COUNT(*) counts all rows including those with NULLs. COUNT(col) counts only non-NULL values in that column. COUNT(DISTINCT col) counts distinct non-NULL values. The difference matters when the column is nullable. |
Window functions
Window functions are the most-tested advanced SQL topic in data engineering and backend interviews at companies that take SQL seriously.
| Function | What it computes | Key detail |
|---|---|---|
| ROW_NUMBER() | A unique sequential integer for each row within the partition, ordered by ORDER BY. | Always unique - ties get arbitrary distinct numbers. Use to deduplicate: keep WHERE rn = 1. |
| RANK() | Rank within the partition; ties get the same rank and the next rank is skipped. | Ties produce gaps: two rows ranked 1 means the next rank is 3. Contrast with DENSE_RANK. |
| DENSE_RANK() | Like RANK() but without gaps after ties. | Two rows ranked 1 means the next rank is 2. Use when you want the top N distinct values. |
| LAG(col, n) | Value of col from n rows before the current row within the partition. | Default n is 1. The first row returns NULL (or a provided default). Used for period-over-period comparisons. |
| LEAD(col, n) | Value of col from n rows after the current row within the partition. | Mirror of LAG. Common for computing the next event's timestamp to derive session duration. |
| SUM / AVG OVER (...) | Running or windowed aggregate without collapsing rows. | With ORDER BY in the OVER clause it becomes a running total (default frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). |
| NTILE(n) | Divides rows in the partition into n roughly equal buckets and assigns a bucket number. | Used for percentile assignments (quartiles, deciles). Bucket sizes differ by at most 1 when rows are not evenly divisible. |
Window vs GROUP BY
Window functions do not collapse rows. A GROUP BY with SUM reduces 10 rows to 1; SUM() OVER () on those same 10 rows returns 10 rows, each showing the aggregate value. This is the core distinction interviewers test.
PARTITION BY
Defines the window
PARTITION BY col resets the function for each group of col, analogous to GROUP BY but without collapsing rows. Omitting PARTITION BY treats the entire result set as one partition.
ORDER BY in OVER
Defines ordering and framing
ORDER BY inside OVER determines the ordering within each partition and implicitly sets a running frame. To compute a total over the whole partition regardless of order, omit ORDER BY inside OVER.
Indexing
Indexing questions appear in both SQL rounds and system design discussions. Know what an index actually is, when it helps, and when it hurts.
| Question | Sharp answer |
|---|---|
| What is a B-tree index? | The default index type in PostgreSQL, MySQL, and SQL Server. A balanced tree that keeps keys sorted, enabling O(log n) lookups, range scans, and ORDER BY operations. Well-suited for high-cardinality columns with range or equality predicates. |
| When does an index NOT help? | Low-cardinality columns (e.g. a boolean flag) - the optimizer may prefer a full scan. Leading wildcard LIKE '%term' - the index cannot be used for prefix matching in reverse. Functions on the indexed column: WHERE LOWER(email) = 'x' bypasses the index unless a functional index exists. |
| What is a composite index and how is column order chosen? | An index on multiple columns (a, b, c). It can satisfy queries on (a), (a, b), or (a, b, c) - but not on (b) alone. The leading column should be the one most commonly used in equality predicates, followed by range predicates. This is the "leftmost prefix" rule. |
| Covering index? | An index that includes all columns needed to answer a query - no heap lookup required. PostgreSQL calls this an "index-only scan". Add non-key columns with INCLUDE (PostgreSQL) to cover a query without affecting sort order. |
| When do indexes hurt? | Write-heavy tables: every INSERT, UPDATE, DELETE must also update every index on the table. Large numbers of indexes slow writes and consume disk/memory. Partial indexes (WHERE clause on the index) and deferred/batch indexing help on write-heavy paths. |
EXPLAIN and query optimization
EXPLAIN (ANALYZE in PostgreSQL, EXPLAIN in MySQL) shows how the database plans to execute a query. Reading it is the fastest way to diagnose slowness.
- 1
Run EXPLAIN ANALYZE
Step 1In PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) SELECT ... - shows actual row counts, actual vs estimated rows, buffer hits and reads. In MySQL: EXPLAIN SELECT ... then SHOW WARNINGS for extra detail. - 2
Find the most expensive node
Step 2Read the plan bottom-up (innermost nodes execute first). Look for Seq Scan on large tables, large estimated row counts with wide mismatches against actual rows, and Sort nodes on unsorted data. - 3
Check index usage
Step 3Index Scan or Index Only Scan is usually good. Seq Scan on a large table with a WHERE predicate usually means a missing or unused index. Check whether the predicate is sargable (can be indexed). - 4
Fix row-count estimate errors
Step 4A large discrepancy between estimated and actual rows means stale statistics. Run ANALYZE (PostgreSQL) or ANALYZE TABLE (MySQL) to update them. The planner uses statistics to choose join order and access paths. - 5
Check join strategy
Step 5Hash Join is good for large unsorted inputs. Nested Loop is good when the outer side is small and the inner side has an index. Merge Join needs both inputs sorted. Wrong join type often follows from stale statistics or a missing index.
Transactions and isolation levels
| Isolation level | Dirty read | Non-repeatable read | Phantom read | Notes |
|---|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible | Rarely used. Allows reading uncommitted changes from other transactions. |
| READ COMMITTED | Not possible | Possible | Possible | Default in PostgreSQL and Oracle. Each statement sees a fresh snapshot. |
| REPEATABLE READ | Not possible | Not possible | Possible (in theory) | Default in MySQL InnoDB. PostgreSQL's implementation also prevents phantoms in practice. |
| SERIALIZABLE | Not possible | Not possible | Not possible | Full isolation. Transactions behave as if run serially. Highest overhead; may cause more transaction retries. |
| Anomaly | Definition |
|---|---|
| Dirty read | Transaction A reads a row modified by Transaction B before B commits. If B rolls back, A has read data that never existed. |
| Non-repeatable read | Transaction A reads a row, Transaction B updates and commits it, A reads again and sees different data. |
| Phantom read | Transaction A runs a range query, Transaction B inserts a row matching the range and commits, A runs the same range query and sees the new row. |
| Lost update | Two transactions read a value, both modify it, and the second write overwrites the first. Common bug in read-modify-write patterns without locking or optimistic concurrency. |
Classic query tasks
These two patterns appear so frequently in SQL rounds that every candidate should know the idiomatic approach.
| Task | Approach |
|---|---|
| Second-highest salary | Use a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or use DENSE_RANK(): SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2. The window function approach generalises to the Nth highest trivially - just change the filter. |
| Running total | SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM transactions. The frame clause is explicit here; with ORDER BY in OVER, most databases use this frame by default. Add PARTITION BY user_id to compute a per-user running total. |
| Deduplicate rows - keep latest per group | SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM events) t WHERE rn = 1. This is the canonical deduplication pattern with window functions. |
| Find users with no matching rows (anti-join) | Three equivalent approaches: LEFT JOIN ... WHERE right.id IS NULL; NOT EXISTS (SELECT 1 FROM ... WHERE ...); NOT IN (SELECT id FROM ...). Prefer NOT EXISTS - it handles NULLs correctly and typically has the best query plan. |
SQL rounds benefit from talking through your plan
In a SQL interview, state the approach before writing - "I'll use a window function here because I need per-group ranking without collapsing rows." That commentary is a scored signal. If you want to work through SQL rounds with an engineer who runs them regularly, book a free call and we'll run one.
SQL questions often arise in system design discussions too - see System Design Fundamentals for how indexing, sharding, and replication interact with the query patterns here. For the algorithmic side, LeetCode Patterns covers the data structures that map to SQL joins and aggregations in code.
Sources & further reading
- 1PostgreSQL documentation - Query Planning / EXPLAIN — postgresql.org
- 2PostgreSQL documentation - Transaction Isolation — postgresql.org
- 3MySQL 8.0 reference - Window functions — dev.mysql.com
- 4SQL standard ISO/IEC 9075 overview - Wikipedia — Wikipedia