SQL Queries Interview Questions: 10 Problems with Answers

SQL Queries Interview Questions: 10 Problems with Answers

Most SQL interviews do not test trivia. They hand you a schema and ask you to write a query on the spot. If you are searching for sql queries questions for interview, you want real problems with working answers, not a glossary of definitions.

Here is the short answer. Expect questions on joins, aggregation with GROUP BY and HAVING, subqueries, window functions, and finding duplicates or the Nth highest value. Ten problems cover most of what analyst and developer interviews ask. Each one below includes the example query, the logic behind it, and the mistake candidates make most often.

This guide is also useful for recruiters. At Olibr, we build hiring tools for recruiters and staffing agencies, and screening technical candidates is a daily task for them. Knowing what a good SQL answer looks like helps you judge candidates faster, and helps candidates practice the right way. Work through the problems in order, and write each query yourself before you read the solution.

1. Find the second highest salary

The problem

This is the question that opens more SQL interview questions rounds than any other. You get an employees table and must return the second highest salary. It looks easy, but the trap is in the details. If two people share the top salary, is the second highest the same number again? Ask before you type. Most interviewers want the second highest distinct value, and they expect NULL when no such value exists.

Sample table and query

Use this small table. The tie at the top is deliberate.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Asha and Meera both earn 90000, so the correct result is 75000. A query that simply skips one row would return 90000 and fail the tie test.

How the query works

The inner query finds the highest salary, which is 90000. The outer query keeps only rows below that value, then takes the maximum of what remains. That gives 75000.

The approach also handles the edge case for free. If every employee earns the same amount, the filter removes all rows. MAX over an empty set returns NULL, which is the answer you want.

Clarify ties and NULLs first, because the right query depends on what "second highest" means.

Follow-up questions interviewers ask

Expect the interviewer to push on your solution right away. These are the usual follow-ups in any set of sql query interview questions and answers:

• Find the Nth highest salary. Use DENSE_RANK() OVER (ORDER BY salary DESC) in a subquery and filter where the rank equals N.
• Why not ORDER BY salary DESC LIMIT 1 OFFSET 1? Because it returns 90000 on our sample data. It only works with SELECT DISTINCT salary.
• Find the second highest salary per department. Add PARTITION BY department_id to the window function.

The window function version is the one to practice until it is automatic, since it scales to every variation. You will meet the same idea again in problem 7.

2. Find duplicate records in a table

The problem

Duplicates appear in almost every set of interview questions on SQL, because dirty data is normal in real jobs. You get a customers table and must list every email that appears more than once. Before you write anything, define "duplicate". Is it one column or a combination of columns?

Sample table and query

Here, asha@example.com is entered twice under different IDs.

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

The output is one row, asha@example.com, with a count of 2.

How the query works

GROUP BY email collapses rows with the same email into one group, and COUNT(*) counts the rows in each group. HAVING then keeps only groups with more than one row. Many candidates write WHERE COUNT(*) > 1, which throws an error because WHERE runs before grouping. Conditions on aggregates belong in HAVING.

Use WHERE to filter rows and HAVING to filter groups.

Follow-up questions interviewers ask

Interviewers usually push on scope and output. Be ready for these:

• Show the full duplicate rows, not just the email. Join the grouped result back to the table, or filter on COUNT(*) OVER (PARTITION BY email) in a subquery.
• Find duplicates across several columns. Group by name, email together.
• Remove the duplicates. That is the next problem.

3. Delete duplicates while keeping one row

The problem

Now you must remove the extra rows, not just find them. The rule is simple: keep exactly one row per email, usually the one with the lowest id. Interviewers use this to see whether you check the result before you destroy data.

Sample table and query

Using the customers table from problem 2, rows 1 and 3 share an email. Keep row 1 and delete row 3.

DELETE FROM customers
WHERE id NOT IN (
  SELECT MIN(id)
  FROM customers
  GROUP BY email
);

Afterward, ids 1, 2, and 4 remain.

How the query works

The subquery groups rows by email and returns the smallest id in each group, which gives 1, 2, and 4. The DELETE then removes every row whose id is not in that list, so only row 3 goes. MySQL rejects a subquery on the table you are deleting from, so there you either wrap it in a derived table or use a self join instead.

Run the SELECT version of your filter first, then switch it to DELETE.

Follow-up questions interviewers ask

Expect these in most sql programming interview questions and answers sets:

• Do it with ROW_NUMBER(). Number rows with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) in a CTE, then delete rows where the number is above 1. This works directly in SQL Server.
• How do you protect against a mistake? Wrap the statement in a transaction and check the row count before you commit.
• How do you stop duplicates coming back? Add a UNIQUE constraint on email.

4. Find customers with no orders using a LEFT JOIN

The problem

Anti-join problems appear in most interview questions about SQL, because they test how joins treat missing matches. You get a customers table and an orders table. Return every customer who has never placed an order. An INNER JOIN cannot do this, since it drops the exact rows you want.

Sample table and query

Meera has no orders in this data.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;

The result is one row, Meera.

How the query works

A LEFT JOIN keeps every row from the left table. When no order matches, the order columns come back as NULL. Meera has no match, so her order_id is NULL, and the WHERE clause keeps only rows like hers.

A LEFT JOIN with an IS NULL filter returns the rows that have no match.

Test a column that can never be NULL in a real match, such as the primary key. Testing a nullable column can give false results.

Follow-up questions interviewers ask

These follow-ups come up often in SQL questions for interview rounds:

• Rewrite it with NOT EXISTS. Use WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). It is just as correct and often easier to read.
• Why is NOT IN risky? If the subquery returns a single NULL, NOT IN returns no rows at all.
• List every customer with an order count, including zero. Use LEFT JOIN with COUNT(o.order_id), not COUNT(*), which would count Meera as one order.

5. Count records per group with GROUP BY and HAVING

The problem

Aggregation shows up in nearly every set of interview questions sql candidates practice. You get an employees table and must list each department with its headcount, but only departments with more than two employees. The question checks whether you can tell filtering rows from filtering groups.

Sample table and query

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 2;

Only Engineering comes back, with a headcount of 3.

How the query works

Rows are grouped by department first. COUNT(*) then counts each group, which gives Engineering 3, Sales 2, and HR 1. HAVING runs after grouping and drops every group that fails the test. If you also need to filter individual rows, such as active employees only, add a WHERE clause before GROUP BY.

Group first, then filter the groups, and keep every non-aggregated SELECT column in GROUP BY.

The second half of that rule trips up many candidates. MySQL may let a stray column through, but PostgreSQL and SQL Server will reject the query.

Follow-up questions interviewers ask

Expect small twists on the same pattern in most sql query questions for interview rounds:

• Sort by the biggest team. Add ORDER BY headcount DESC at the end.
• What is the difference between COUNT(*) and COUNT(column)? The second one skips NULLs. COUNT(DISTINCT column) counts unique values only.
• Group by two columns. List both in GROUP BY, for example department and job title, and each pair becomes its own group.

6. Find employees earning above their department average

The problem

This one appears in many interview questions for SQL analysts because it compares each row against a group. You must return every employee who earns more than the average for their own department, not the company-wide average. The catch is that the comparison value changes from row to row.

Sample table and query

SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Engineering averages 80000 and Sales averages 55000. The result is Asha and Priya. Meera earns exactly the average, so the strict > leaves her out.

How the query works

The inner query is a correlated subquery. It reruns for every outer row and uses e.department to calculate the average for that employee's department only. The outer WHERE then compares the salary to that number.

A correlated subquery recalculates its answer for each row of the outer query.

Say this out loud in the interview. Interviewers want to hear that you understand why the subquery depends on the outer row.

Follow-up questions interviewers ask

Expect a push toward cleaner or faster versions:

• Rewrite it with a window function. Compute AVG(salary) OVER (PARTITION BY department) in a subquery, then filter on it. It scans the table once.
• Rewrite it with a join. Join to a grouped query of department averages. It is often the fastest on older engines.
• Show how far above average each person is. Return salary - dept_avg as a column.

7. Get the top N rows per group with window functions

The problem

Top-N-per-group is a favorite in interview questions in SQL because GROUP BY alone cannot solve it. You must return the two highest-paid employees in each department. Aggregates collapse rows, but here you need to keep the full rows.

Sample table and query

WITH ranked AS (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rnk <= 2;

The result is Asha, Meera, Priya, and John. Ravi drops out because he ranks third in Engineering.

How the query works

PARTITION BY department restarts the ranking inside each department. ORDER BY salary DESC puts the highest salary first, so rank 1 is the top earner.

You cannot filter on a window function in the same SELECT, because WHERE runs before windows are calculated. That is why the ranking sits in a CTE and the filter sits outside it.

Rank inside a CTE or subquery, then filter on the rank in the outer query.

Follow-up questions interviewers ask

The real test is whether you know the ranking functions apart:

• ROW_NUMBER vs RANK vs DENSE_RANK? ROW_NUMBER gives unique numbers, RANK leaves gaps after ties, and DENSE_RANK does not.
• Return exactly N rows per group, even with ties. Switch to ROW_NUMBER() and add a tiebreaker column to ORDER BY.
• Return only the top earner per department. Filter on rnk = 1.

8. Calculate a running total

The problem

Finance and sales teams live on cumulative numbers, so this appears in many interview sql questions for data analyst roles. You get a sales table and must show each day's amount next to the cumulative total so far. GROUP BY cannot do it, because you need to keep every row.

Sample table and query

SELECT order_date, amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

The running totals are 100, 250, 300, and 500.

How the query works

Adding OVER turns SUM into a window function. The ORDER BY sets the sequence, and the frame tells SQL to add every row from the first one through the current row. Spelling out ROWS matters. Without it, the default frame is RANGE, so rows with the same date get the same running total.

A running total is SUM with an ORDER BY inside OVER.

Follow-up questions interviewers ask

Expect these variations in most sql query interview questions with answers sets:

• Restart the total for each customer. Add PARTITION BY customer_id inside OVER.
• Calculate a 7-day moving average. Use AVG(amount) with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
• Solve it without window functions. Use a correlated subquery or a self join. It works, but it rereads the table for every row, so it is slower.

9. Compare rows with LAG and LEAD

The problem

Comparing a row with its neighbor is a standard item in interview questions of SQL for analyst roles. You get a monthly_sales table and must show each month's revenue next to the change from the previous month. A self join can do it, but interviewers want to see that you know LAG.

Sample table and query

SELECT month_start, revenue,
  LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
  revenue - LAG(revenue) OVER (ORDER BY month_start) AS revenue_change
FROM monthly_sales;

The changes are NULL, 200, -300, and 600. January has no earlier month, so its result is NULL.

How the query works

LAG(revenue) returns the revenue from one row back, in the order set by ORDER BY. LEAD does the same thing looking forward. Both accept an offset and a default, so LAG(revenue, 1, 0) returns 0 instead of NULL on the first row.

LAG reads the previous row, LEAD reads the next row, and both need an ORDER BY to mean anything.

Follow-up questions interviewers ask

Expect the interviewer to build on the same pattern:

• Find months where revenue dropped. Put the query in a CTE and filter revenue_change < 0 outside it, because WHERE cannot see window results.
• Calculate percentage change. Divide the change by LAG(revenue) and wrap the divisor in NULLIF(..., 0) to avoid divide-by-zero errors.
• Compare to the same month last year. Use LAG(revenue, 12) on monthly data.

10. Find an employee's manager with a self join

The problem

Org charts live in a single table, so this question tests whether you can join a table to itself. You get an employees table where each row stores a manager_id that points to another row's id. Return every employee next to their manager's name. The trap is the top person, who has no manager, so your join type decides whether they appear.

Sample table and query

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

The result pairs Ravi and Meera with Asha, John with Ravi, and Asha with NULL.

How the query works

The same table appears twice under two aliases. e plays the employee and m plays the manager, and the ON clause matches the employee's manager_id to the manager's id. Aliases are mandatory here, since SQL cannot tell the two copies apart without them. The LEFT JOIN keeps Asha with a NULL manager, while an INNER JOIN would silently drop her.

A self join treats one table as two, and the aliases are what keep it readable.

Follow-up questions interviewers ask

Expect these in any set of sql queries interview questions and answers:

• Find employees who earn more than their manager. Keep the join and add WHERE e.salary > m.salary.
• Show the full reporting chain up to the top. Use a recursive CTE, because a single self join only reaches one level.
• Count direct reports per manager. Run SELECT manager_id, COUNT(*) FROM employees GROUP BY manager_id, then join back for the manager's name.

How to prepare for a SQL query interview

Practice on a real database

Reading solutions teaches you very little. Install PostgreSQL or MySQL, load the sample tables from this article, and type every query from memory. Then break each one on purpose by adding a NULL, a tie, or an empty table. Interviewers love edge cases, and you only spot them once you have run the queries yourself.

Writing queries beats reading them, so practice on a real database every day.

Use the same steps every time

Interviewers watch your process as closely as your result. A repeatable routine keeps you calm when the problem is new:

1. Restate the problem and ask about ties, NULLs, and duplicates.
2. Sketch the output columns and two or three expected rows.
3. Write the simplest query that works, then improve it.
4. Test it against one edge case out loud.

Focus on the topics that repeat

Most sql queries questions for interview rounds draw from the same pool: joins, GROUP BY with HAVING, subqueries, and window functions. Spend your time there. Aim for 10 to 15 minutes per problem when you practice, because live interviews rarely give you more.

Also learn the quirks of your target engine. MySQL, PostgreSQL, and SQL Server differ on LIMIT versus TOP, on deleting with a subquery, and on how strictly they enforce GROUP BY. Candidates who name these differences unprompted stand out.

Final tips before your interview

The ten problems above cover the core of sql queries questions for interview rounds: joins, grouping, subqueries, and window functions. Learn the pattern behind each problem rather than memorizing the exact query, because interviewers change the table and the twist, not the idea.

On the day, clarify ties and NULLs before you type, and explain your reasoning out loud. A correct query with no explanation scores lower than a slightly flawed one you can defend. If you get stuck, write the simple version first and improve it from there.

Recruiters can use this list as a screening rubric. A candidate who explains why NOT IN fails on NULLs knows SQL beyond the basics. If you are building a data team, you can hire vetted SQL developers on Olibr and browse verified profiles with skill data, experience, and salary insights.

For engineers

Find work worth your time.

Live engineering roles across India and the US, matched to your stack. Build a profile recruiters actually discover.

Browse jobsHow it works