Sql Interview Questions For Experienced

What Actually Matters When You Are Preparing For Senior Sql Interviews

Most candidates walk into these interviews thinking they need to memorize syntax. They do not. I have sat on both sides of the table, and the difference between a hire and a pass usually comes down to how someone reasons through a broken query when they do not know the answer off the top of their head.

Sql Interview Questions For Experienced Candidates

Let me be straightforward about what experienced interviewers are looking for. We want to see that you understand execution plans, not just write clean SQL. The questions that separate mid-level from senior are the ones where the obvious answer is wrong, and the candidate has to explain why. One pattern that comes up constantly is query optimization under constraints. Here is a real scenario I dealt with recently that mirrors exactly what interviewers ask. We had a stored procedure running against a 40 million row transaction table. The query used a correlated subquery in the SELECT clause to pull the latest status for each order. It worked fine for three years. Then the data grew, and the query went from two seconds to forty minutes. The interviewer will not give you the data. They will describe the symptom and ask how you fix it. The answer is never "add an index" as the first move. You rewrite the correlated subquery as a window function with ROW_NUMBER() partitioned by the grouping column, filtering where the rank equals one. That single change dropped execution time from forty minutes to under three seconds because the engine stopped doing a nested loop lookup on every single row. I have seen the opposite mistake too. A candidate once optimized a query by adding seven indexes to a heavily written reporting table. The reads got faster, but the write throughput tanked by sixty percent. The interviewer's real question was whether you understood the tradeoff between read and write performance. If you cannot articulate why that happened, you are going to fail the deeper follow-ups.

Core Topics That Actually Get Asked

Recursive CTEs come up more often than you would expect. Not the basic tree traversal stuff from three levels deep, but the edge cases where the recursion hits the default 100-level limit or where a self-join in the recursive part causes a cartesian explosion. I had to debug a bill-of-materials query where a single malformed parent-child relationship in the data caused the CTE to loop infinitely until it hit the MAXRECURSION limit. The workaround was adding a visited path column using STRING_AGG or CONCAT in the recursive step and filtering out rows where the child already appeared in the ancestor chain. You should be able to write a recursive CTE from scratch on a whiteboard without looking it up. Window functions are another major area. ROW_NUMBER versus RANK versus DENSE_RANK is almost guaranteed to come up. The nuance that trips people up is when to use each one in practice. ROW_NUMBER gives you a unique sequential number, which is useful for pagination and deduplication. RANK leaves gaps after ties, which matters for competition-style rankings. DENSE_RANK does not leave gaps, which is what you want for things like salary percentile calculations where two people at the same level should share the same rank. An interview question might ask you to find the second highest salary per department, and the naive GROUP BY approach will fail when there are duplicate salaries. The correct answer uses DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and filters for rank equals two. Join strategy and query plan interpretation is where most experienced candidates reveal their actual skill level. You should be comfortable reading an execution plan and identifying scan versus seek, key lookups, spools, and sort operations. A table scan on a large table where a seek is possible usually means a missing index or a sargable predicate problem. I remember a case where a query had a LIKE '%term%' pattern that invalidated any index on that column. The fix was implementing full-text search instead, which changed the query from a full scan to an index-based lookup that could handle millions of rows in milliseconds rather than tens of seconds.

Advanced Patterns That Separate Good From Great

Pivoting and unpivoting data is something every experienced developer should handle fluently. The PIVOT operator in SQL Server is convenient but limited. It does not handle dynamic column lists well, and error handling around it is poor. I prefer the CASE statement approach with aggregation because it is more transparent and works consistently across dialects. An interview might ask you to pivot monthly sales data into columns without knowing the months in advance. The answer involves dynamic SQL with sp_executesql, building the column list programmatically, and validating input to prevent injection. Skipping the validation step is an automatic red flag for anyone who has been burned by SQL injection in production. Handling slowly changing dimensions, particularly type 2 SCDs, is another topic that comes up frequently for senior roles. You need to show that you understand the pattern of maintaining historical records with effective date ranges, current flag columns, and surrogate key chains. The tricky part is writing the MERGE statement correctly to handle inserts, updates, and deletes in a single operation without creating duplicates or orphaned records. I once worked on a data warehouse migration where a poorly written SCD type 2 process created duplicate active records because the overlap detection logic had a fencepost error on the end date boundary. The fix required switching from <= to

in the end date comparison and adding a validation query that checked for overlapping periods after each load. Transaction isolation levels and locking behavior are non-negotiable topics. You should understand the difference between READ COMMITTED, REPEATABLE READ, and SERIALIZABLE, and more importantly, when to use snapshot isolation or read committed snapshot to reduce blocking without sacrificing consistency. I have seen production outages caused by developers using TABLOCKX hints to "speed things up" during bulk operations, which ended up blocking all other queries against those tables for hours. The correct approach is usually to use snapshot isolation at the session or database level and redesign the bulk operation to work in smaller chunks with proper logging.

What Experienced Candidates Get Wrong

The most common failure point is over-reliance on ORM-generated queries without understanding what the ORM is actually doing underneath. When an interviewer asks you to optimize a slow query, and your first instinct is to add an index without examining the execution plan, you are missing the real problem. Indexes are not free. They consume disk space, increase write latency, and maintain overhead. In one case I investigated, a reporting query was slow because the optimizer was choosing a bad plan due to outdated statistics, not because of missing indexes. Updating the statistics on the key columns reduced query time from forty five seconds to two seconds with zero schema changes. Another frequent mistake is treating SQL as a programming language rather than a declarative query language. Writing procedural logic inside stored procedures with cursors and while loops when a set-based operation would do the job is a red flag. I once reviewed a migration script that used a cursor to process ten thousand rows one at a time. Rewriting it as a single set-based UPDATE with a JOIN took the execution time from roughly forty minutes down to eight seconds on the same hardware. Understanding NULL handling is basic but still catches people. COALESCE, ISNULL, and NULLIF are not interchangeable. ISNULL takes exactly two arguments and is database-specific, COALESCE is standard SQL and can take multiple arguments, and NULLIF returns NULL when two expressions are equal. Using the wrong one in a critical calculation can introduce subtle bugs that are hard to trace. I found a production issue where a NULLIF was being used to prevent division by zero, but the column containing the denominator could itself be NULL, which meant the NULLIF did not trigger and the query still failed. The fix was wrapping the denominator in ISNULL or COALESCE first, then applying NULLIF.

Practical Preparation Strategy

Working through LeetCode medium and hard SQL problems is useful, but it is not enough. You need to practice explaining your reasoning out loud. Interviewers care about the thought process more than the final answer. When you are solving a problem, narrate what you are considering, why you are rejecting alternatives, and what tradeoffs you are making. This is the same skill you use when debugging a production issue at 2 AM and need to communicate with your team. Reading actual execution plans from your own database or a public dataset will help more than any tutorial. Run queries against a real database, check the actual plan, and try to predict what the optimizer will do before you look. If your prediction is wrong, figure out why. This builds intuition that no amount of memorization can replicate. One resource that helps is setting up a local PostgreSQL or SQL Server instance with a reasonably sized sample database and timing your queries with different approaches. You should also practice writing queries from scratch without an IDE. Whiteboard or paper interviews are still common for senior positions, and autocomplete is not available. Knowing the exact syntax for a lateral join, a merge statement, or a recursive CTE under pressure matters more than you might think. I once watched a candidate who knew the concept perfectly but blanked on the exact CTE syntax and could not recover because they had never practiced writing it without assistance.

The field changes enough between interviews that sticking to one database platform is a liability. PostgreSQL, SQL Server, and MySQL all handle window functions, CTEs, and query optimization differently. Understanding the differences between them, like how PostgreSQL uses MVCC while SQL Server uses row versioning with tempdb, will make you stand out. An interviewer might ask about a specific edge case that behaves differently across platforms, and having awareness of those differences signals that you actually work with databases rather than just passing courses.