23 interview questions
SQL interview questions and answers
SQL interviews usually combine concept questions with live queries against a small schema. Interviewers check that your query is correct on edge cases such as NULLs, ties and duplicates, not just that it runs. Talk through your approach before you write, and say which database dialect you are assuming.
Updated
Core concepts
01What is the difference between INNER JOIN, LEFT JOIN and FULL OUTER JOIN?
What they want to hear: The basis of almost every query, and whether you know what happens to rows without a match.
Answer outline
- INNER JOIN returns only rows that match in both tables.
- LEFT JOIN returns all rows from the left table, with NULLs for columns from the right when there is no match.
- FULL OUTER JOIN returns all rows from both sides, with NULLs where either side has no match.
- Watch out: a filter on the right table in WHERE turns a LEFT JOIN into an inner join; put it in the ON clause instead.
02What is the difference between WHERE and HAVING?
What they want to hear: Understanding of the order in which a query is evaluated.
Answer outline
- WHERE filters rows before grouping; it cannot use aggregates.
- HAVING filters groups after GROUP BY and can use aggregates such as COUNT(*) > 5.
- Logical order: FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- Filter in WHERE when you can, since it reduces the rows that get grouped.
03How does NULL behave in SQL?
What they want to hear: NULL handling is the most common source of silently wrong results.
Answer outline
- NULL means unknown; any comparison with NULL, including NULL = NULL, is unknown, not true.
- Use IS NULL and IS NOT NULL to test for it, and COALESCE to replace it.
- COUNT(*) counts rows; COUNT(column) skips NULLs; SUM and AVG ignore NULLs.
- NOT IN with a subquery that returns a NULL gives no rows; NOT EXISTS is safer.
04What is the difference between UNION and UNION ALL?
What they want to hear: A small detail that affects both correctness and performance.
Answer outline
- UNION combines results and removes duplicate rows.
- UNION ALL keeps every row and is faster because it skips the deduplication step.
- Use UNION ALL unless you actually need duplicates removed.
05What are primary keys, foreign keys and normalization?
What they want to hear: Whether you understand how good schemas protect data quality.
Answer outline
- A primary key uniquely identifies each row and cannot be NULL.
- A foreign key references a primary key in another table and enforces that the reference exists.
- Normalization splits data so each fact is stored once, which avoids update anomalies.
- Analytics schemas often denormalise on purpose for faster reads, for example a star schema.
Classic query problems
06Find the second highest salary from an employees table.
What they want to hear: A classic that tests ties, empty results and knowing more than one approach.
Answer outline
- Subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Returns NULL if there is no second value.
- Window function: DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery, then filter rank = 2; DENSE_RANK handles ties correctly.
- Offset: SELECT DISTINCT salary ORDER BY salary DESC LIMIT 1 OFFSET 1; DISTINCT is needed because of ties.
- Generalise to the Nth highest with DENSE_RANK, and mention how each version behaves with ties and empty tables.
07Find duplicate emails in a users table, then delete them keeping one row each.
What they want to hear: Grouping, window functions and care before running a destructive statement.
Answer outline
- Find: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.
- Rank within duplicates: ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn; rows with rn > 1 are the extras.
- Delete rows whose id is in that set of rn > 1, keeping the lowest id (exact syntax varies by database).
- Run it as a SELECT first, wrap it in a transaction, and add a unique constraint to stop it happening again.
08Calculate a running total of sales by date.
What they want to hear: Window function fluency, a core skill for analytics SQL.
Answer outline
- Aggregate to one row per date first if there are many sales per day.
- SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
- Add PARTITION BY customer_id or region to restart the total per group.
- Explain ROWS versus RANGE: with the default RANGE frame, rows with the same date are summed together.
09Find customers who have never placed an order.
What they want to hear: Anti-joins and NULL safety.
Answer outline
- LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL.
- Or WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id).
- Avoid NOT IN when orders.customer_id can be NULL, because it then returns nothing.
10Return the top three products by revenue in each category.
What they want to hear: Top-N per group, one of the most common analytics patterns.
Answer outline
- Aggregate revenue per product with GROUP BY category, product.
- Rank with ROW_NUMBER(), RANK() or DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC).
- Filter rank <= 3 in an outer query or CTE, since window results cannot be used in WHERE directly.
- Say how you treat ties: ROW_NUMBER picks exactly three, RANK and DENSE_RANK may return more.
11Calculate month-over-month revenue growth.
What they want to hear: Comparing rows across time with LAG and handling division safely.
Answer outline
- Aggregate revenue per month using a date truncation function.
- Use LAG(revenue) OVER (ORDER BY month) to get the previous month on the same row.
- Growth = (revenue - prev) / prev, using NULLIF(prev, 0) to avoid division by zero.
- Check for missing months, which LAG would silently skip; join to a calendar table if needed.
12Find users who logged in on three or more consecutive days.
What they want to hear: A harder pattern question: gaps and islands.
Answer outline
- Deduplicate to one row per user per day.
- Assign ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date).
- Subtract that number (as days) from the date; consecutive days share the same result, which identifies each streak.
- GROUP BY user and that group key, HAVING COUNT(*) >= 3.
13Find employees who earn more than their manager.
What they want to hear: Self-joins, a common pattern for hierarchical data.
Answer outline
- Join the employees table to itself: FROM employees e JOIN employees m ON e.manager_id = m.id.
- Filter WHERE e.salary > m.salary.
- An inner join drops employees without a manager, which is what this question needs.
Window functions and advanced SQL
14What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
What they want to hear: Choosing the right ranking for ties, which changes results.
Answer outline
- ROW_NUMBER gives unique numbers 1, 2, 3, 4 even for ties, in an arbitrary order unless you break ties.
- RANK gives ties the same number and leaves gaps: 1, 2, 2, 4.
- DENSE_RANK gives ties the same number without gaps: 1, 2, 2, 3.
- Example: use DENSE_RANK for 'second highest value', ROW_NUMBER for deduplication.
15What is a CTE and when would you use one?
What they want to hear: Writing queries others can read and maintain.
Answer outline
- A CTE is a named subquery defined with WITH at the top of a query.
- Use it to break a complex query into readable steps.
- Recursive CTEs walk hierarchies such as org charts or category trees.
- Mention that some databases may materialise CTEs, which can affect performance.
16When would you use a window function instead of GROUP BY?
What they want to hear: Understanding that window functions keep the detail rows.
Answer outline
- GROUP BY collapses rows into one per group.
- A window function computes over a group but keeps every row.
- Example: show each order with its customer's total spend using SUM(amount) OVER (PARTITION BY customer_id).
- Use it for rankings, running totals, moving averages and comparisons with previous rows.
17Write a 7-day moving average of daily active users.
What they want to hear: Window frames, a detail many candidates get wrong.
Answer outline
- Start from one row per day with the daily count.
- AVG(dau) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).
- The first six days average fewer than seven values; say whether to show or drop them.
- Fill missing days with zero using a calendar table, or the frame counts the wrong days.
18How would you pivot rows into columns?
What they want to hear: Practical reporting skill without relying on vendor-specific syntax.
Answer outline
- Use conditional aggregation: SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan, and so on, grouped by the row key.
- This works in nearly every database.
- Some databases have a PIVOT operator, but the columns still need to be known in advance.
Performance
19A query is slow. How do you speed it up?
What they want to hear: A method based on the query plan rather than guessing.
Answer outline
- Read the execution plan with EXPLAIN (or EXPLAIN ANALYZE) to see scans, joins and row estimates.
- Add or fix indexes on columns used in filters and joins.
- Avoid functions on indexed columns in WHERE, which can stop the index being used, and select only needed columns.
- Reduce rows early, check for outdated statistics, and consider pre-aggregated tables for heavy reports.
20What is an index, and what are the trade-offs?
What they want to hear: Core performance knowledge for anyone writing production queries.
Answer outline
- An index is a separate sorted structure, usually a B-tree, that finds rows without a full scan.
- Composite indexes work left to right: (a, b) helps filters on a, or a and b, not b alone.
- Indexes speed up reads but slow down inserts and updates and take space.
- Low-selectivity columns such as a yes/no flag often benefit little from an index alone.
21What is the difference between a correlated subquery and a join?
What they want to hear: Understanding how queries execute and when one form is slower.
Answer outline
- A correlated subquery refers to the outer query and is logically run once per outer row.
- A join combines the tables in one operation the optimiser can plan as a whole.
- Modern optimisers often rewrite one into the other, but not always.
- Check the plan; rewrite as a join or window function when the subquery is the bottleneck.
Working with data
22Tell me about a time a query you wrote returned wrong results.
What they want to hear: Whether you validate your work and own mistakes, which matters a lot when others act on your numbers.
Answer outline (STAR)
- What the query was for and how the error was found.
- The cause, for example a join that duplicated rows or a NULL handling issue.
- How you fixed it and told the people using the numbers.
- The checks you now run, such as row counts before and after joins.
23Describe a time a stakeholder asked for data that turned out to be the wrong question.
What they want to hear: Whether you clarify the real need before writing queries, instead of delivering exactly what was asked.
Answer outline (STAR)
- The original request and what made you question it.
- The questions you asked to find the decision behind it.
- The data you delivered instead, and how you explained the change.
- What the stakeholder did with it.
How to prepare
- Practise on a real database, not only on paper. Small syntax differences between dialects trip people up under pressure.
- Before writing a query, say what one row of the result represents. Most wrong answers come from getting the grain wrong.
- Check row counts after every join to catch duplicates caused by one-to-many relationships.
- Learn window functions well: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and SUM with frames cover most hard questions.
- Always mention how your query handles NULLs, ties and empty results.
Practice these questions out loud with NORDON
Reading answers is not the same as saying them. NORDON's interview practice asks you questions written for the role, listens to your spoken answer and shows you how to make it stronger.
- Upload your CV in the Knowledge base, so questions and feedback use your real background.
- Open Practice, enter the role (for example SQL), paste the job ad if you have it, and choose 3, 5, 8 or 10 questions and the answer language.
- Listen to each question and answer out loud, as you would in the room. You can also type an answer.
- Get a score from 1 to 10, what worked, what to improve and a stronger answer to rehearse. It uses only what you said and your documents, and leaves a [placeholder] where a fact about you is missing.
- Try the question again, or move on. For a dress rehearsal, run a mock call where an interviewer voice asks the questions out loud.
The free plan includes 10 scored practice answers a month and mock calls of up to 10 minutes, no card needed. Paid plans include more practice and mock calls of up to 30 minutes. How practice works
Practice is for preparing. Use live help in a real interview only where the interviewer allows AI assistance, never in an interview, test or assessment whose rules forbid it.
Preparing for your SQL interview
Which SQL dialect should I use in an interview?
Ask, or say which one you are assuming. Standard SQL with common functions is usually fine, and interviewers rarely mind small syntax differences if your logic is right.
What level of SQL do interviews expect?
It depends on the role. Analyst and data roles usually expect joins, aggregation, subqueries and window functions. Backend roles add schema design, indexes and transactions. Read the job description and ask the recruiter.
How should I practise SQL for interviews?
Load a small sample dataset into a local database and solve problems against it, checking results by hand. Focus on the patterns above: top-N per group, running totals, duplicates, anti-joins and consecutive streaks.