SQL Interview Questions and Answers: 16 With Queries (2026)

Last updated: October 2026

Quick Answer

SQL interviews test whether you can turn a business question into a correct query, explain why it works, and spot the edge cases: duplicates, NULLs and ties. Interviewers usually start with joins and aggregation, then move to window functions, subqueries and CTEs, and finish with performance. Practice by writing queries aloud, not just reading answers. Rehearse in OphyAI Coding Interview for the problem, and in OphyAI Interview Practice for the spoken explanation.

#	Question	What it tests
1	INNER vs LEFT JOIN	Join logic
2	WHERE vs HAVING	Filtering order
3	Find duplicates	GROUP BY, HAVING
4	Second highest salary	Subqueries, ranking
5	RANK vs DENSE_RANK vs ROW_NUMBER	Window functions
6	Top N per group	Window plus filter
7	Running total	Window frames
8	NULL handling	Three-valued logic
9	CTE vs subquery	Readability
10	Rows with no match	Anti-join

The Questions, With Sample Answers

1. “What is the difference between INNER JOIN and LEFT JOIN?”

Why they ask. It is the fastest check of join understanding. Sample answer. An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN returns every row from the left table and fills the right side with NULL when no match exists. I use LEFT JOIN when I must keep all customers even if they have no orders, then count orders with COUNT(o.id), which ignores NULLs. A common trap is filtering the right table in WHERE, which silently turns a LEFT JOIN into an INNER JOIN; put that condition in the ON clause instead. Weak answers. Reciting definitions with no example, or missing the WHERE trap.

2. “What is the difference between WHERE and HAVING?”

Why they ask. It checks that you know the order of execution. Sample answer. WHERE filters rows before grouping, so it cannot use aggregates. HAVING filters groups after aggregation. For example, SELECT dept, COUNT(*) FROM emp WHERE active = 1 GROUP BY dept HAVING COUNT(*) > 5 first removes inactive employees, then keeps only departments with more than five. Putting a row-level condition in HAVING works but is slower and less clear. Weak answers. Saying they are “the same but for groups” without mentioning order.

3. “How do you find duplicate rows?”

Why they ask. Data cleaning is daily work. Sample answer. Group by the columns that define a duplicate and keep groups with a count above one: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. To delete duplicates while keeping one, number rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) in a CTE and delete rows where the number is greater than one. I would first check which columns truly define a duplicate with the business owner. Weak answers. Using DISTINCT, which hides duplicates rather than finding them.

4. “Find the second highest salary.”

Why they ask. It has several valid solutions and tests ties. Sample answer. With a window function: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM emp) t WHERE r = 2. DENSE_RANK handles ties correctly, so two people at the top do not skip rank two. A subquery alternative is SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp). I would ask whether a missing second value should return NULL. Weak answers. Using LIMIT 1 OFFSET 1 without discussing duplicates.

5. “What is the difference between RANK, DENSE_RANK and ROW_NUMBER?”

Why they ask. It is a standard window-function check. Sample answer. All three number rows within a partition. ROW_NUMBER gives unique numbers even for ties. RANK gives tied rows the same number and then skips ahead (1, 1, 3). DENSE_RANK gives ties the same number without gaps (1, 1, 2). I choose ROW_NUMBER to deduplicate, DENSE_RANK for “Nth highest” questions, and RANK when gaps are meaningful, as in competition rankings. Weak answers. Not knowing which one skips numbers.

6. “Get the top 3 products by revenue in each category.”

Why they ask. It is the classic top-N-per-group problem. Sample answer. Rank within each category, then filter: WITH r AS (SELECT category, product, revenue, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) rn FROM sales) SELECT * FROM r WHERE rn <= 3. I would use DENSE_RANK or RANK if ties should all be included, and state that choice before writing it. You cannot filter on a window function in the same SELECT, so the CTE or subquery is required. Weak answers. Trying LIMIT 3, which limits the whole result, not each group.

7. “Calculate a running total.”

Why they ask. It tests window frames. Sample answer. SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running FROM orders. Specifying ROWS explicitly avoids the default RANGE frame, which treats rows with the same date as peers and can produce surprising totals. For per-customer totals, add PARTITION BY customer_id. Weak answers. A self-join solution when a window function is available, or ignoring the frame.

8. “How does SQL handle NULL?”

Why they ask. NULL bugs are common in real data. Sample answer. NULL means unknown, so comparisons with it are neither true nor false. col = NULL never matches; use IS NULL. Aggregates like SUM and AVG ignore NULLs, while COUNT(*) counts rows and COUNT(col) skips NULLs. NOT IN with a subquery that returns a NULL returns no rows, so I prefer NOT EXISTS. COALESCE supplies a default. Weak answers. Treating NULL as zero or an empty string.

9. “What is a CTE and when would you use one instead of a subquery?”

Why they ask. It shows how you structure complex queries. Sample answer. A common table expression, written with WITH, names a temporary result set for one statement. I use CTEs to break a long query into readable steps, to reference the same result more than once, and for recursive queries such as walking an org chart. Performance is usually similar to a subquery, depending on the database, so I choose based on clarity. Weak answers. Claiming a CTE is always faster.

10. “Find customers who have never placed an order.”

Why they ask. It tests anti-joins. Sample answer. SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL. The alternative is WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). I prefer NOT EXISTS over NOT IN because NULLs in the subquery can break NOT IN. Weak answers. Using INNER JOIN, which can only return matches.

11. “What is the difference between UNION and UNION ALL?”

Why they ask. It checks attention to performance and duplicates. Sample answer. UNION combines results and removes duplicates, which needs a sort or hash step. UNION ALL keeps every row and is faster. If I know the inputs cannot overlap, or I want duplicates, I use UNION ALL. Both require the same number of columns with compatible types. Weak answers. Not knowing UNION removes duplicates.

12. “How would you compare each month’s revenue with the previous month?”

Why they ask. It tests LAG and business thinking. Sample answer. Aggregate by month in a CTE, then LAG(revenue) OVER (ORDER BY month) AS prev, and compute growth as (revenue - prev) / NULLIF(prev, 0). NULLIF avoids a divide-by-zero. The first month has no previous value, so I would show NULL, not zero. Weak answers. A self-join with date arithmetic that breaks on missing months.

13. “What is an index and when does it hurt?”

Why they ask. Performance awareness separates analysts from engineers. Sample answer. An index is a data structure, often a B-tree, that lets the database find rows without scanning the whole table. It speeds up lookups, joins and sorts on the indexed columns but slows writes and uses storage. I index columns used in joins and selective filters, check the query plan with EXPLAIN, and avoid functions on indexed columns in WHERE, which can prevent index use. Weak answers. “Indexes make everything faster.”

14. “Explain GROUP BY with multiple columns and conditional aggregation.”

Why they ask. Pivot-style reports are common. Sample answer. Grouping by several columns creates one row per distinct combination. For a pivot, use SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) to total one category in its own column. Every non-aggregated column in SELECT must be in GROUP BY in standard SQL. Weak answers. Selecting a non-grouped column and hoping the database allows it.

15. “Find users active on three consecutive days.”

Why they ask. It tests creative window use. Sample answer. Deduplicate to one row per user per day, then subtract ROW_NUMBER() days from the date; consecutive days share the same difference. Group by user and that difference and keep groups with a count of at least three. Alternatively, use LEAD twice and compare the dates. I would state my assumption about timezones. Weak answers. Not deduplicating first, so multiple events per day break the count.

16. “How would you debug a slow query?”

Why they ask. It shows your process. Sample answer. Run EXPLAIN (or EXPLAIN ANALYZE) to see scans, joins and row estimates. Look for full table scans, missing indexes, joins on mismatched types, and SELECT * on wide tables. Filter early, reduce joined rows before joining, and check statistics are current. I change one thing at a time and re-measure. Weak answers. Rewriting the query at random without reading the plan.

How to Practice These

Write each query from a blank editor, run it on a small dataset, and then explain it aloud as if to an interviewer. Rehearse the problem-solving side with OphyAI Coding Interview, which streams an approach for a problem you screenshot, then solve a fresh variant yourself. For the spoken part, run technical mock sessions in OphyAI Interview Practice and read the transcript for gaps in your explanation. Use Interview Copilot for mock video calls only, and check the employer’s rules in the AI-in-interviews tracker before using any tool in a real round. Start practicing →

Frequently Asked Questions

What SQL topics are asked most in interviews?

Guides consistently list joins, GROUP BY with HAVING, subqueries, CTEs and window functions such as RANK, DENSE_RANK, ROW_NUMBER and LAG. One guide recommends 35-50 practice problems with emphasis on window functions. Start with joins and aggregation, then move to windows.

How do I answer a SQL interview question I have never seen?

Restate the problem, ask about ties, NULLs and duplicates, sketch the output columns, then build the query in small steps, checking each with an example. Say what you are doing aloud. Interviewers weigh your reasoning and your handling of edge cases alongside the final syntax.

Which SQL dialect should I practice?

Use the dialect named in the job description; most interview questions work in PostgreSQL, MySQL and SQL Server with small differences such as LIMIT versus TOP and date functions. Ask the interviewer which dialect to assume, and say so when you rely on a feature that varies.

Are window functions required for data analyst interviews?

They are common. Many analyst and engineering loops include a ranking, running total or LAG question. Learn PARTITION BY, ORDER BY and frames, and practice top-N-per-group and period-over-period problems. Our data analyst interview guide covers more of the analytics side, including A/B testing and metric questions.

How long should I spend preparing for a SQL interview?

Two to four weeks of daily practice is typical for a first loop: one week on joins and aggregation, one on window functions and CTEs, then timed mocks. If you already write SQL at work, a week of timed practice on tricky cases may be enough.

What is the most common SQL interview mistake?

Forgetting NULLs, duplicates or ties. Typical failures include NOT IN with NULLs, joining before deduplicating, and using ROW_NUMBER when ties should share a rank. State your assumptions out loud and test with a tiny three-row example before you finish, so the interviewer sees you checking your own work.

Related Guides

• Datadog interview guide
• Snowflake interview guide
• Databricks interview guide
• Oracle interview guide
• Amazon interview guide
• MongoDB interview guide
• Data analyst interview questions and answers

Sources and verification notes

Sources checked October 2026 through search summaries; the sample queries were written for this guide and use standard SQL, so test them on your database.

• roadmap.sh, SQL queries interview questions: a 30-question guide covering SELECT, JOIN, GROUP BY and window functions. Third-party, checked October 2026.
• GoLinuxCloud, SQL technical interview questions: 47 questions and the 35-50 problem practice recommendation. Third-party, checked October 2026.
• DataDriven, SQL GROUP BY and HAVING practice: HAVING versus WHERE and conditional aggregation. Third-party, checked October 2026.