SQL is tested at virtually every backend, data engineering, and data science interview. Beyond basic SELECT statements, interviewers test: window functions, CTEs, query optimization, and transaction isolation. This guide covers the SQL concepts and query patterns that distinguish senior engineers — with examples you can practice immediately.
Window Functions
Window functions compute a value across a set of rows related to the current row — without collapsing rows (unlike GROUP BY). Syntax: function() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN …). (1) ROW_NUMBER() — assigns a sequential number per partition. SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees. Use for: getting the top-N per group (WHERE rank <= 3), deduplication (keep the first row per group), and pagination. (2) RANK() / DENSE_RANK() — like ROW_NUMBER but handles ties. RANK: ties get the same rank, next rank skips (1,1,3). DENSE_RANK: ties share a rank, no gap (1,1,2). (3) LAG(col, N) / LEAD(col, N) — access the value N rows before/after the current row. Use for: comparing with the previous row (month-over-month growth: revenue – LAG(revenue, 1) OVER (ORDER BY month)), and detecting changes (WHERE status != LAG(status)). (4) SUM/AVG/COUNT() OVER (…) — running totals and moving averages. SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — 7-day rolling sum. (5) NTILE(N) — divide rows into N equal groups. NTILE(4) assigns quartiles. Interview tip: window functions are the most commonly tested advanced SQL topic. Practice: top-N per group, running totals, month-over-month comparisons, and median calculation.
CTEs and Recursive Queries
Common Table Expressions (CTEs): named temporary result sets. WITH active_users AS (SELECT user_id, COUNT(*) AS actions FROM events WHERE date >= CURRENT_DATE – 30 GROUP BY user_id HAVING COUNT(*) >= 10) SELECT * FROM users JOIN active_users USING (user_id). CTEs improve readability (name intermediate results), enable reuse (reference the CTE multiple times in the query), and support recursion. Recursive CTEs: process hierarchical data (org charts, category trees, graph traversal). WITH RECURSIVE subordinates AS (SELECT employee_id, manager_id, name FROM employees WHERE employee_id = 1 — root UNION ALL SELECT e.employee_id, e.manager_id, e.name FROM employees e JOIN subordinates s ON e.manager_id = s.employee_id) SELECT * FROM subordinates. This traverses the organizational hierarchy from the CEO (id=1) down to all reports. Use for: bill of materials, threaded comments, file system paths, and any tree/graph structure in SQL. Performance: CTEs are not materialized in PostgreSQL (they are inlined like subqueries). Use MATERIALIZED hint for CTEs referenced multiple times with expensive computation. In MySQL 8+: CTEs are supported. In older MySQL: use subqueries or temporary tables.
Joins: Types, Performance, and Pitfalls
Join types: (1) INNER JOIN — rows matching in both tables. The most common join. (2) LEFT JOIN (LEFT OUTER) — all rows from the left table + matching rows from the right. Non-matching right rows are NULL. Use for: including rows without matches (users without orders). (3) RIGHT JOIN — all rows from the right table. Rarely used (rewrite as LEFT JOIN with reversed table order). (4) FULL OUTER JOIN — all rows from both tables. NULLs for non-matches on either side. Use for: finding mismatches between two datasets. (5) CROSS JOIN — Cartesian product (every row from A paired with every row from B). Rarely intentional in production (usually indicates a missing join condition). (6) SELF JOIN — join a table to itself. Use for: comparing rows within the same table (find employees earning more than their manager). Performance: (1) Always index join columns (foreign keys). A JOIN without an index on the join column causes a full table scan per row. (2) Join order matters for large tables. The query planner optimizes this, but you can influence with: smaller table first, more selective filters first. (3) Avoid N+1 queries in application code — use JOINs instead. Common pitfall: LEFT JOIN with a WHERE clause on the right table: WHERE right_table.col = value. This effectively converts the LEFT JOIN to an INNER JOIN (the NULL rows are filtered out). Use the condition in the ON clause instead: LEFT JOIN right_table ON … AND right_table.col = value.
Subqueries vs Joins vs CTEs
Three ways to combine data — when to use each: (1) JOIN — combine rows from multiple tables in one result set. Best for: accessing columns from multiple tables simultaneously, and when performance matters (the optimizer handles joins well). (2) Subquery (in WHERE/HAVING) — filter based on a derived value. WHERE salary > (SELECT AVG(salary) FROM employees). WHERE department_id IN (SELECT id FROM departments WHERE location = “NYC”). Best for: filtering based on aggregate values and existence checks (EXISTS). (3) CTE — name intermediate results for readability and reuse. Best for: multi-step transformations, recursive queries, and complex queries where readability matters. Performance comparison: in most databases (PostgreSQL, MySQL 8+), CTEs and subqueries are optimized similarly. Joins are generally the most efficient for combining data. Correlated subqueries (referencing the outer query) can be slow: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) — executes the subquery for EACH outer row. Rewrite as a JOIN with a grouped subquery: JOIN (SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id) AS dept_avg ON …. This executes the aggregation once, not per-row. Interview question: “Rewrite this correlated subquery as a JOIN” is a classic SQL optimization question.
Transaction Isolation Levels
Isolation levels control what concurrent transactions can see: (1) READ UNCOMMITTED — can read uncommitted changes from other transactions (dirty reads). Almost never used in production. (2) READ COMMITTED (PostgreSQL default) — can only read committed data. No dirty reads. But: non-repeatable reads (reading the same row twice may yield different values if another transaction committed between reads). (3) REPEATABLE READ (MySQL InnoDB default) — within a transaction, the same query returns the same rows. No dirty or non-repeatable reads. But: phantom reads in some databases (new rows inserted by other transactions may appear). MySQL InnoDB prevents phantoms with gap locks. (4) SERIALIZABLE — transactions execute as if they were sequential. No anomalies. Highest isolation, lowest concurrency (most locking). Use for: financial transactions where absolute consistency is required. Practical guidance: READ COMMITTED for most web applications (good balance of consistency and performance). SERIALIZABLE for critical financial operations (account transfers, inventory decrements). REPEATABLE READ for: long-running reports that need a consistent snapshot. Always use explicit transactions for multi-statement operations: BEGIN; UPDATE accounts SET balance = balance – 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;. Without a transaction, a crash between the two UPDATEs loses money.
Common SQL Interview Patterns
Patterns frequently tested: (1) Top-N per group — ROW_NUMBER() OVER (PARTITION BY group ORDER BY metric DESC). Filter WHERE rn = N. (5) Duplicate detection — GROUP BY all columns, HAVING COUNT(*) > 1. (6) Percentile/median — PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) or use NTILE(2) and average the boundary values. (7) Gaps and islands — find sequences of consecutive records and gaps between them. Use the ROW_NUMBER difference technique. (8) Pivoting — CASE WHEN inside aggregation: SUM(CASE WHEN status = “active” THEN 1 ELSE 0 END) AS active_count. (9) Cumulative distribution — CUME_DIST() or PERCENT_RANK() window functions. (10) Self-join for hierarchy — employees joining managers table. Practice these patterns with real databases. Many interviewers give you a schema and ask you to write queries live. Speed and correctness both matter.
Drill the patterns next:
