SQL Interview Questions: The Query Scenarios You Will Actually Face

What are the most common SQL interview questions and query scenarios?

SQL interview questions are technical tasks that test how well you filter, group, join and rank data, and how you reason about query performance. The scenarios that come up most are totals per customer, customers with no orders, the second highest value, top N per group, duplicates, running totals and slow query analysis.

In this guide I walk through those scenarios one by one. For each one I explain what the question really asks, show a working query, and then point out the trap the interviewer is hoping you miss. If you are starting SQL from zero, this is not the right first read. Learn the basic commands first, then come back. You can find roadmap style posts in the software category.

I have worked in digital marketing since 2012. Even so, SQL sits on my desk every day because of reporting, campaign data and e-commerce orders. I also ask these exact questions when I hire developers and data analysts. So the scenarios below come from real interviews, not from a textbook.

What do interviewers actually test with SQL interview questions?

The interviewer wants more than a correct result. What they really want to see is how you think about data. For example, if you start typing before asking what one row of the table represents, that is a red flag. People call this the grain of the table.

My own scoring checklist looks roughly like this:

• Does the candidate clarify the question and ask about ties, NULLs and date ranges?
• Do they know the grain: is one row an order or an order line?
• Is the first answer simple and correct, with optimisation saved for later?
• Do they sanity check the output, for instance by comparing row counts?
• Can they talk about indexes and query plans when performance comes up?

In short, memorising syntax is not enough. Thinking out loud, stating your assumptions and testing the result against a tiny sample in your head often earns more points than a perfect query typed in silence.

Which sample tables do these scenarios use?

Every example here uses a simple e-commerce schema. That way you can picture each query against the same data. The tables are:

• customers: id, name, city, created_at.
• orders: id, customer_id, order_date, status, total_amount.
• order_items: order_id, product_id, quantity, unit_price.
• products: id, name, category.
• employees: id, name, department_id, salary, manager_id.

I keep the queries close to standard SQL. However, some functions differ between PostgreSQL and MySQL. Tell the interviewer which database you are using at the start. Also mention that date functions vary by dialect, because that small remark signals care.

One more note. In orders, one row is one order. In order_items, one row is one line of an order. That distinction decides the outcome of about half the scenarios below.

How do you explain the difference between WHERE and HAVING?

This question arrives in the first five minutes of almost every interview. The short answer: WHERE filters individual rows before grouping, and HAVING filters groups after GROUP BY. That is why you cannot put an aggregate condition inside WHERE.

Sample task: list customers with more than three orders in 2025. One solution: SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01' GROUP BY customer_id HAVING COUNT(*) > 3.

Here the date filter lives in WHERE and the count filter lives in HAVING. The extra point interviewers look for: if you move the date filter into HAVING, the query might still run, but it groups rows it did not need. Also, a half open date range works better than BETWEEN. With timestamp columns, BETWEEN can silently drop most of the last day. Mention that and you will usually see the interviewer write something down.

How do you get the total order value for every customer?

The question looks simple, yet it hides two traps. First, should customers with no orders appear at all? Second, should their total show NULL or zero? So clarify before you type.

If every customer must appear, you use a LEFT JOIN: SELECT c.id, c.name, COALESCE(SUM(o.total_amount), 0) AS total FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name.

COALESCE turns NULL into zero for customers without orders. With an INNER JOIN, those customers would quietly vanish. On the other hand, where you place an extra filter matters a lot. Say you want to exclude cancelled orders. Put that condition in WHERE and your LEFT JOIN effectively becomes an INNER JOIN. The right place is the ON clause: LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'. A candidate who can explain this truly understands joins.

How do you find customers who never placed an order?

This is the anti join scenario, and it has three common solutions. Interviewers often ask whether you know all three and how they differ.

1. LEFT JOIN with IS NULL: SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL.
2. NOT EXISTS: SELECT c.* FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id).
3. NOT IN: SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders).

The real trap sits in option three. If the subquery returns a single NULL, NOT IN returns no rows at all. The reason is that "x NOT IN (1, 2, NULL)" evaluates to unknown, and WHERE drops unknown rows. The PostgreSQL comparison operators documentation describes this three valued logic.

For that reason I prefer NOT EXISTS in practice. It is safe with NULLs, and most modern engines turn it into an efficient anti join plan. Saying this out loud shows you have gone beyond memorised answers.

How do you find the second highest salary?

A classic question, and your answer reveals your level quickly. The first idea is usually a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). It works, and it handles ties correctly.

Then the interviewer extends the task: what about the Nth highest salary? At this point you should move to a window function. One solution: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2.

You also need to justify DENSE_RANK. If two people share the top salary, RANK jumps to 3 for the next value, so rank 2 does not exist. ROW_NUMBER picks one of the tied people at random and calls them second. DENSE_RANK leaves no gaps, therefore it answers "second highest distinct value" correctly. Finally, ask what should happen when no second value exists. Empty result or NULL? That question alone sets you apart.

How do you return the top earner in each department?

This pattern is known as top N per group, and it is a favourite in mid level interviews. The skeleton never changes: split with PARTITION BY, sort inside each partition, then filter outside.

The query: WITH ranked AS (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn FROM employees e) SELECT * FROM ranked WHERE rn = 1.

You cannot use a window function result directly in WHERE, because WHERE runs before window functions. That is why you need a CTE or a subquery. The follow up is almost always about ties. If two people share the top salary in a department, should both appear? If yes, swap ROW_NUMBER for RANK. The PostgreSQL window functions tutorial shows the mechanics with short examples.

The same pattern solves "top three products per category" and "latest order per customer". Once the pattern clicks, you can handle the whole family of questions.

How do you find and delete duplicate rows?

This question has two steps, and the second step trips most candidates. Finding duplicates is easy: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1. Now you know which email appears more than once.

Deleting them means you must keep one row per group, usually the oldest. With a window function, first assign ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at, id) to every row. Then delete the ids whose number is greater than 1.

Signs of maturity the interviewer hopes to see:

• Running the same logic as a SELECT first and checking the affected row count.
• Wrapping the delete in a transaction and committing only after checking the result.
• Normalising case and whitespace before comparing email addresses.
• Suggesting a UNIQUE constraint as the permanent fix.

The last point matters most. Cleaning duplicates is a one off job. Real engineering means making sure they never come back.

How do you calculate a running total and a moving average?

In reporting roles this question is almost guaranteed. For example: show year to date revenue by day. First you total revenue per day, then you add a window function on top.

The query: SELECT day, revenue, SUM(revenue) OVER (ORDER BY day) AS running_total FROM (SELECT CAST(order_date AS DATE) AS day, SUM(total_amount) AS revenue FROM orders GROUP BY CAST(order_date AS DATE)) d.

For a moving average you define the frame explicitly: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). That gives the average of the last seven rows. Still, there is a subtle trap. If days without sales are missing from the table, "last seven rows" is not the same as "last seven days". Experienced candidates spot this and suggest filling gaps with a calendar table.

I use this calculation constantly in ad reports. When digital marketing KPIs swing from day to day, a moving average shows the real trend.

How do you find users who logged in on consecutive days?

This is the gaps and islands problem, and it separates senior candidates in advanced interviews. A typical prompt: find users who logged in at least three days in a row. It looks hard at first, but there is a well known trick.

Here is the trick. Sort each user's login dates and subtract the row number from the date. On consecutive days both values rise by one, so the difference stays constant. Rows that share the same difference form one island.

1. Deduplicate same day logins: SELECT DISTINCT user_id, CAST(login_at AS DATE) AS day.
2. Compute ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) for each user.
3. Subtract the row number from the day to build a group key.
4. Group by user_id and the group key, then filter with HAVING COUNT(*) >= 3.

Candidates who skip step one break the streak for anyone who logged in twice on the same day. So say the deduplication step out loud. Date subtraction syntax differs by database; simply mentioning that is enough.

How do you calculate month over month growth with LAG?

This question tests whether you can look at the previous row. The usual prompt: show revenue per month and the percentage change versus the prior month. LAG handles it neatly.

First build monthly revenue. Then LAG(revenue) OVER (ORDER BY month) brings the previous month onto the same row. The percentage change is (revenue - prev) * 100.0 / NULLIF(prev, 0).

Two details in that formula earn points. First, 100.0 instead of 100: in engines that use integer division, the integer version truncates the result. Second, NULLIF: if the previous month is zero, you get NULL instead of a division by zero error. Also, the first month has no previous value, so it shows NULL too. Explain that as expected behaviour, not a bug.

The same logic powers retention questions. On the marketing side I read tables like this all the time; my guide on how to read a digital marketing report covers how to interpret such comparisons.

How do you pivot rows into columns?

An interviewer might ask you to show each customer's spend per category on a single row. In other words, categories become columns. Not every database has a PIVOT command, so the portable answer is conditional aggregation.

Example: SELECT o.customer_id, SUM(CASE WHEN p.category = 'Electronics' THEN oi.quantity * oi.unit_price ELSE 0 END) AS electronics, SUM(CASE WHEN p.category = 'Clothing' THEN oi.quantity * oi.unit_price ELSE 0 END) AS clothing FROM orders o JOIN order_items oi ON oi.order_id = o.id JOIN products p ON p.id = oi.product_id GROUP BY o.customer_id.

The key detail is computing the amount from order_items. If you join orders.total_amount to the line items and sum it, every order repeats once per line and the total inflates. I see this mistake more than any other in interviews. If you use PostgreSQL, mentioning the FILTER clause as an alternative earns a bonus point.

Why are NULLs a trap in SQL interview questions?

NULL means unknown. It is not zero and it is not an empty string. Short NULL questions show up often among SQL interview questions because they test attention quickly. Know these behaviours by heart:

• NULL = NULL does not return true; use IS NULL instead.
• COUNT(*) counts every row, while COUNT(column) skips NULLs.
• AVG ignores NULL rows in the denominator, so the result can look higher than you expect.
• A single NULL inside a NOT IN list can make the query return nothing.
• Whether NULLs sort first or last in ORDER BY depends on the database.

A typical prompt: why do COUNT(*) and COUNT(email) return different numbers? Because rows with an empty email field drop out of the second count. Take it one step further and note that COUNT(*) - COUNT(email) gives you the number of missing values. That shows you actually use data in practice.

How do you analyse a slow query?

For mid level roles and above this question is close to certain. The right answer is a method, not a guess. I want to hear this order: look at the query plan, find the bottleneck, then measure the change.

EXPLAIN shows the plan. EXPLAIN ANALYZE actually runs the query and reports real timings and row counts. The PostgreSQL EXPLAIN documentation explains why large gaps between estimated and actual rows matter.

Common causes include:

• No index on the column you filter or join on.
• Wrapping an indexed column in a function, such as YEAR(order_date) = 2025.
• Pulling wide rows with an unnecessary SELECT *.
• Stale statistics that push the planner towards a poor choice.

The second item is the classic follow up. Once a function wraps the column, the engine cannot use a plain index on it directly. The fix is to rewrite the condition as a date range. I cover the website side of slowness in my post on how site speed affects SEO.

What index questions should you expect?

Index questions follow the slow query question. The most common one: what happens if we index every column? Answer: reads may get faster, but every INSERT, UPDATE and DELETE must also update each index. As a result, write cost rises and storage grows.

The second common question concerns column order in a composite index. With an index on (customer_id, order_date), searches on customer_id alone can use it. On the other hand, a search on order_date alone usually cannot use it efficiently. A phone book analogy lands well here: if the book sorts by surname, you cannot look people up by first name.

The third question concerns selectivity. An index on a status column with only two values rarely helps on its own, because you still read a large share of the rows. Here the most mature answer is simple: I would measure before deciding.

What should you know about transactions and isolation levels?

This topic comes up mostly in backend interviews. First, explain ACID in one line: atomicity, consistency, isolation and durability. Then comes the classic scenario: two users try to buy the last item in stock at the same moment. What happens?

To answer, you need isolation levels. According to the PostgreSQL documentation, the default level is Read Committed. The MySQL documentation states that InnoDB defaults to Repeatable Read. Knowing this shows you understand that the same code can behave differently on two engines.

Practical answers to the stock question: lock the row with SELECT ... FOR UPDATE, or write a conditional update such as UPDATE products SET stock = stock - 1 WHERE id = 5 AND stock > 0, then check the affected row count. The conditional update is the simplest fix because it checks and decrements in one step. In e-commerce consulting projects, stock mismatches are among the most expensive bugs I run into.

How do you approach normalisation and schema design questions?

Some interviews swap queries for design: model posts, authors and tags for a blog. The interviewer checks whether you set up the relationships correctly. Posts and tags have a many to many relationship, so you need a junction table such as post_tags.

For normalisation, give a practical example instead of reciting normal forms. Suppose you store the customer's city on every order. When the customer moves, old orders and new records disagree. That is an update anomaly in concrete form.

A strong candidate also knows normalisation is not always the goal. In reporting tables you may duplicate data on purpose to speed up reads. What counts is explaining why you made that choice. I discuss architecture trade offs at a different layer in my post on micro frontends.

Which technique fits which SQL interview question?

I built the table below from my own interview notes. When you spot the key phrase in a prompt, it helps you recall the right technique fast.

Do not memorise the table of SQL interview questions. Instead, write each query yourself once. Then the pattern is ready in the interview, and your mind is free to focus on edge cases.

What mistakes do candidates make during live coding?

Most mistakes I see come from haste, not missing knowledge. The candidate hears the prompt and starts typing without asking about the grain. As a result, the query looks right but counts wrong.

The mistakes I see most often:

• Selecting a column that is not in GROUP BY.
• Joining order totals to line items and inflating the sum.
• Dropping the last day of a date range.
• Declaring victory without checking the output.
• Chasing one giant query and ending up with something unreadable.

For the last point my advice is clear. Split the query into steps with CTEs and explain each step. Interviewers prefer a readable query over a clever but tangled one, because other people on the team will read it too.

How should you prepare for SQL interview questions?

Prepare by scenario, not by topic. For each scenario in the table, create your own sample data and solve it at least two ways. For instance, solve the anti join with both NOT EXISTS and LEFT JOIN, then compare the results.

A practical one week plan could look like this:

1. Days one and two: GROUP BY, HAVING, joins and anti joins.
2. Days three and four: window functions, ranking, LAG and running totals.
3. Fifth day: gaps and islands plus pivot questions.
4. Day six: reading EXPLAIN output, indexes and transactions.
5. Final day: timed, out loud practice runs.

That final day matters most. Without out loud practice, you may freeze on a query you know well. This plan is a starting point based on my field experience, not a guarantee, so stretch it to fit your level.

Is SQL useful outside software engineering roles?

Absolutely. I work in marketing, yet I use SQL to combine ad, order and search data. For example, export data from Google Search Console and match it with orders. Then you can see which pages actually drive sales.

That is why SQL questions also appear in interviews for data analysts, marketing analysts and product managers. The difficulty varies. Still, the scenarios stay largely the same: aggregation, joins, ranking and time comparisons.

If your website or store does not capture data cleanly, even great SQL skills cannot produce meaningful answers. If you want a measurable setup, take a look at my web design service, or reach me through the contact page.