SQL Interview Questions and Answers (9 Examples)

Most “SQL interview questions” lists are just vocabulary: define a JOIN, define an index. Real interviews test whether you can turn a business question into a correct query, and whether you notice the trap hiding in it. This guide covers nine SQL interview questions that come up again and again, each with sample data, the answer, the output, and the wrong answer most people give first.

Every query below was run, and the outputs are real. Use the setup script just below the overview table to try them yourself.

The 9 questions at a glance

CREATE TABLE salaries (name VARCHAR(30), salary INT);
INSERT INTO salaries VALUES ('Asha', 90000), ('Ravi', 90000), ('Meera', 80000), ('Kabir', 70000);

CREATE TABLE customers (customer_id INT, name VARCHAR(30));
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi'), (3, 'Meera'), (4, 'Kabir');
CREATE TABLE orders (order_id INT, customer_id INT, amount INT, status VARCHAR(20));
INSERT INTO orders VALUES (101, 1, 500, 'completed'), (102, 1, 300, 'cancelled'), (103, 2, 700, 'completed'), (104, 3, 200, 'refunded');

CREATE TABLE users (id INT, email VARCHAR(40));
INSERT INTO users VALUES (1, 'a@x.com'), (2, 'b@x.com'), (3, 'a@x.com'), (4, 'c@x.com'), (5, 'b@x.com');

CREATE TABLE employees (emp_id INT, name VARCHAR(30), department VARCHAR(20), salary INT, manager_id INT);
INSERT INTO employees VALUES
  (1, 'Asha', 'Sales', 90000, NULL), (2, 'Ravi', 'Sales', 85000, 1), (3, 'Meera', 'Sales', 95000, 1),
  (4, 'Kabir', 'IT', 70000, 2), (5, 'Neha', 'IT', 70000, 2), (6, 'Arjun', 'IT', 60000, 4);

CREATE TABLE monthly_revenue (sale_month VARCHAR(7), revenue INT);
INSERT INTO monthly_revenue VALUES ('2026-01', 10000), ('2026-02', 12500), ('2026-03', 11000), ('2026-04', 15000);

CREATE TABLE logins (user_id INT, day_no INT);
INSERT INTO logins VALUES (1,1),(1,2),(1,3),(1,5),(2,1),(2,3),(2,4),(3,2),(3,3),(3,4),(3,5);

1. Second-highest salary

A classic, because the obvious answer is wrong. Here are the salaries. Asha and Ravi tie for the top:

The answer most people give first: sort descending and skip one row.

SELECT salary
FROM salaries
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

The top salary appears twice, so skipping one row lands on the top salary again. The question is really about the second-highest distinct value. Two correct answers:

SELECT MAX(salary) AS second_highest
FROM salaries
WHERE salary < (SELECT MAX(salary) FROM salaries);

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM salaries
) t
WHERE rnk = 2
LIMIT 1;

The DENSE_RANK() version generalises to “the Nth highest” by changing rnk = 2. See SQL window functions for how the ranking functions treat ties.

2. Customers who never ordered

Also written as “products never sold” or “users who never logged in”. You need the rows that have no match in another table:

The wrong answer: an INNER JOIN throws away customers without orders before you can look for them.

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

The right answer: keep every customer with a LEFT JOIN, then keep the rows where the order is missing.

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

Kabir has never ordered. NOT EXISTS also works, and is safer than NOT IN when NULLs may be present. See SQL joins and NULL in SQL.

3. Find duplicate rows

“Find users who registered twice with the same email.” Group by the column and keep the groups that appear more than once:

SELECT email, COUNT(*) AS times
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

This is why HAVING exists: the condition is on a group’s count, which does not exist until after grouping. See WHERE vs HAVING.

4. Top earner in each department

“Highest-paid employee per department” is one of the most common questions. Rank inside each department and keep rank 1. Because window functions cannot go directly in WHERE, the ranking goes in a subquery:

SELECT name, department, salary
FROM (
  SELECT name, department, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 1
ORDER BY department, name;

IT returns two people because Kabir and Neha tie, and RANK() gives tied rows the same rank. If the question wants exactly one row per department, use ROW_NUMBER(). Ask the interviewer which they want. That question alone often earns points.

5. Employees who earn more than their manager

The manager is another row in the same table, so join the table to itself with two aliases:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

Meera earns 95000 and reports to Asha at 90000. Join the employee’s manager_id to the manager’s emp_id, then compare.

6. Month-over-month change

You need to compare each row with the previous one. That is what LAG does:

SELECT sale_month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY sale_month) AS change_vs_prev
FROM monthly_revenue;

January has no previous month, so its change is NULL. Mention that when you present the result.

7. Users active 3 days in a row

This is the “gaps and islands” problem. The trick: subtract each row’s position from its day number. Days in an unbroken run all produce the same value, so they land in the same group. Here day_no is a plain day counter, which keeps the query portable across databases:

SELECT DISTINCT user_id
FROM (
  SELECT user_id,
         day_no - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day_no) AS grp
  FROM logins
) t
GROUP BY user_id, grp
HAVING COUNT(*) >= 3
ORDER BY user_id;

User 1 logged in on days 1, 2 and 3. User 3 has 2 to 5. User 2 has 1, then 3 and 4, so the streak is broken. With real dates, you subtract the row number from the date instead, using your database’s date arithmetic.

8. Total revenue (with a hidden filter)

“What is our total revenue?” sounds like SUM(amount). Look at the data first:

SELECT SUM(amount) AS revenue
FROM orders;

That 1700 includes a cancelled order and a refunded one. Before writing anything, ask what should count. If only completed orders count:

SELECT SUM(amount) AS revenue
FROM orders
WHERE status = 'completed';

The interviewer is not testing whether you can type SUM. They want to see you ask “should cancelled and refunded orders count?” before you answer.

9. Count by category in one row

“Show the number of completed, cancelled and refunded orders as columns in a single row.” Use CASE inside an aggregate, called conditional aggregation:

SELECT SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'refunded'  THEN 1 ELSE 0 END) AS refunded
FROM orders;

Each CASE turns a matching row into 1 and everything else into 0, and SUM adds them up. This pattern is also how you pivot data in SQL.

How to answer in the interview

Getting the query right matters, but how you get there is graded too:

1. Restate the question and ask about edge cases. Ties? NULLs? Should cancelled orders count?
2. Say your approach before typing. “I will join the tables, group by department, then rank.”
3. Build it in small steps. Get the join right, check the rows, then add the aggregation.
4. Test on a tiny example. Walk one or two rows through your query out loud.
5. Name the traps. “This would double count if an order has several items.”

A candidate who asks a good clarifying question before writing usually looks stronger than one who immediately writes a confident, wrong query.

Practise these properly

Reading answers is not the same as producing them. Rewrite each query from a blank editor without looking, run it in our free SQL editor, then test your recall with the timed SQL quiz. For a full plan, see how to prepare for a SQL interview. Each topic also has a deeper guide: joins, window functions, subqueries and CTEs.

Frequently asked questions

What SQL topics are asked most in interviews?

Commonly joins (especially LEFT JOIN and finding missing rows), GROUP BY with HAVING, NULL handling, subqueries and CTEs, and window functions such as RANK, ROW_NUMBER and LAG. Exact questions vary by company and role.

How do I find the second-highest salary in SQL?

Use a subquery: SELECT MAX(salary) FROM t WHERE salary < (SELECT MAX(salary) FROM t), or rank with DENSE_RANK() and keep rank 2. Avoid LIMIT 1 OFFSET 1 alone, because tied top salaries make it return the highest value.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All number rows within a window. ROW_NUMBER is always unique, RANK gives ties the same number and skips the next, and DENSE_RANK gives ties the same number without skipping.

How do I prepare for a live SQL coding interview?

Practise writing queries from a blank editor under time pressure, on data with NULLs and ties, and practise explaining your approach out loud. Reviewing your wrong answers teaches more than re-reading solutions.

Should I use a subquery or a CTE in an interview?

Either is fine if the result is correct. A CTE often reads more clearly for multi-step problems. Be ready to explain why you chose it.

Related reading

• How to Prepare for a SQL Interview – a practical plan using quizzes and practice.
• SQL Joins Explained – questions 2 and 5 in depth.
• SQL Window Functions Explained – questions 1, 4 and 6 in depth.