What Actually Comes Up in These Interviews

Most people study the wrong SQL queries before walking into an interview. They memorize generic questions like "write a query to find the second highest salary" without understanding the underlying patterns that show up repeatedly across companies. I've sat on both sides of these interviews at different points, and the gap between someone who's just drilled problems and someone who actually understands what they're doing is usually obvious within five minutes. The queries themselves aren't complicated. What separates candidates is whether they know how to handle edge cases and whether they can explain their reasoning out loud. That matters more than getting the syntax perfect on the first try.

Important Sql Queries For Interview

Let's talk about the ones that actually appear and what they're really testing.

Finding duplicates in a table This shows up constantly because it tests whether you understand GROUP BY and HAVING. The basic version asks you to find duplicate emails or IDs. The follow-up almost always adds a twist — like finding duplicates while keeping only the most recent record. SELECT id, email, created_at FROM ( SELECT id, email, created_at, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) as rn FROM users ) t WHERE rn = 1; The window function approach is what you want here. Beginners will go for self-joins or subqueries with COUNT, which works but falls apart when the table gets large. I once had a candidate who wrote a query using three nested subqueries and a correlated subselect to remove duplicates from a table with 40 million rows. We stopped the interview right there because the query would have taken hours to run instead of seconds. Left join vs inner join pitfalls People know the difference between left and inner joins on paper. They struggle when you ask them to write a query that finds customers who never placed an order. The trick is knowing which null check to use. SELECT c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL; The WHERE clause filtering on the right table being null is the key detail. Put that condition in the ON clause instead and you get everything back, which is the opposite of what you want. I've seen this exact question flip candidates who seemed confident until they wrote the WHERE clause against the wrong table alias. Running totals and cumulative sums This is where window functions separate people who use SQL regularly from people who learned it once in college. SELECT date, amount, SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as cumulative FROM daily_transactions; TheROWS UNBOUNDED PRECEDING part is what trips people up. Without it, some databases default to RANGE semantics, which can give wrong results if you have duplicate dates. I ran into this in production once — our daily reporting query was showing inflated cumulative totals because two transactions had the same timestamp and RANGE was grouping them together incorrectly. Switching to ROWS fixed it immediately. Ranking with ties and gaps RANK, DENSE_RANK, and ROW_NUMBER are tested together because you need to know when each one fails. The classic question is ranking employees by salary with the correct tie behavior. SELECT name, salary, RANK() OVER (ORDER BY salary DESC) as rank_with_gaps, DENSE_RANK() OVER (ORDER BY salary DESC) as rank_without_gaps, ROW_NUMBER() OVER (ORDER BY salary DESC) as arbitrary_order FROM employees; RANK gives 1, 2, 2, 4 when two people tie for second. DENSE_RANK gives 1, 2, 2, 3. ROW_NUMBER gives 1, 2, 3, 4 arbitrarily. The interviewer doesn't care which one you pick — they care that you know the difference and can explain when each is appropriate. Pivoting rows to columns This comes up less often but when it does, it's usually the differentiator. You might need to convert monthly transaction counts into a horizontal layout. SELECT employee_id, SUM(CASE WHEN month = 1 THEN transactions ELSE 0 END) as jan, SUM(CASE WHEN month = 2 THEN transactions ELSE 0 END) as feb, SUM(CASE WHEN month = 3 THEN transactions ELSE 0 END) as mar FROM monthly_stats GROUP BY employee_id; The CASE expression inside an aggregate is the pattern. Dynamic pivots exist but they're rarely what the interview wants — they want to see that you understand conditional aggregation at a fundamental level. Recursive CTEs Hierarchical data queries are the last category that separates casual SQL users from people who work with complex schemas daily. WITH RECURSIVE hierarchy AS ( SELECT id, name, manager_id, 1 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, h.level + 1 FROM employees e JOIN hierarchy h ON e.manager_id = h.id ) SELECT * FROM hierarchy; The recursion stops when there are no more matching rows. The danger here is infinite loops from circular references in your data. I inherited a report once where the org chart had a cycle — someone's manager chain looped back three levels up. The query ran until the connection timed out. Adding a MAX_RECURSION limit or a visited-node check is something senior candidates should mention proactively.

What Interviewers Are Really Evaluating

They're not just checking if you can write a working query. They're watching how you think through ambiguous requirements, whether you consider performance implications, and if you ask clarifying questions before jumping into code.

A good candidate will ask about the table size, whether indexes exist, what the data volume looks like, and what "correct" means in context before writing a single line of SQL. A candidate who starts typing immediately is assuming they already know everything, which is usually when things go wrong. Performance awareness matters more than fancy syntax. Understanding that a full table scan on a billion-row table changes the entire approach to a problem is something you learn through experience, not through a tutorial list. I've rewritten queries that were technically correct but destroyed a database because someone used a NOT IN with a nullable subquery — it returns zero rows instead of the expected result set, and the optimizer often can't find a better plan either. NOT IN versus NOT EXISTS is one of those things that looks identical but behaves completely differently with nulls. Switch to NOT EXISTS or use COALESCE to handle the null case, and you avoid the silent failure that nobody notices until reports start showing empty dashboards. The queries I listed cover the bulk of what you'll encounter. Practice writing them from scratch without looking at documentation. Write them for different database engines if you can — PostgreSQL, MySQL, and SQL Server have enough subtle differences in window function support and CTE behavior that knowing your target platform matters.