A SQL coding interview is different from a SQL theory quiz. Instead of "what is a primary key?", you get a schema, a blank editor and a question like "find each user's longest login streak". This page collects the SQL coding questions that come up most, with a solution and real output for each. For definition-style questions, see the general SQL interview questions and advanced SQL interview questions pages.
Jump to: sample data · the 14 problems · how to approach a live coding round · practice · FAQ
Set up the sample database
All queries ran on SQLite 3.45 and the output below is real. The syntax is standard SQL except where a note says otherwise (mainly date arithmetic). Paste this into any SQL client, such as Beekeeper Studio, and try each question yourself before reading the solution.
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, department TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
(1,'Ana','Engineering',150000,NULL),(2,'Ben','Engineering',150000,1),(3,'Chloe','Engineering',120000,1),
(4,'Dev','Sales',90000,1),(5,'Emma','Sales',110000,4),(6,'Felix','Sales',70000,4),(7,'Gia','Support',65000,4);
CREATE TABLE logins (user_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
(1,'2026-03-01'),(1,'2026-03-02'),(1,'2026-03-03'),(1,'2026-03-07'),(1,'2026-03-08'),
(2,'2026-03-01'),(2,'2026-03-03'),(2,'2026-03-04'),(3,'2026-03-05');
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, amount INTEGER);
INSERT INTO orders VALUES
(1,10,'2026-01-05',50),(2,11,'2026-01-09',80),(3,10,'2026-02-02',120),(4,12,'2026-02-14',40),
(5,11,'2026-02-20',60),(6,10,'2026-03-01',30),(7,13,'2026-03-03',200);
CREATE TABLE contacts (id INTEGER, email TEXT);
INSERT INTO contacts VALUES (1,'[email protected]'),(2,'[email protected]'),(3,'[email protected]'),(4,'[email protected]'),(5,'[email protected]'),(6,'[email protected]');
CREATE TABLE sales (rep TEXT, quarter TEXT, revenue INTEGER);
INSERT INTO sales VALUES ('Ana','Q1',100),('Ana','Q2',150),('Ben','Q1',80),('Ben','Q2',60),('Ben','Q3',90);14 SQL coding interview questions with solutions
They are grouped loosely from easier to harder. Questions 1 to 7 cover what almost every interview tests; 8 to 14 are the ones that separate strong candidates.
1. Find the second highest salary.
Sort distinct salaries descending, skip the first, take one. DISTINCT matters: without it, two people tied for first would make the "second" row the top salary again. In SQL Server use OFFSET 1 ROW FETCH NEXT 1 ROW ONLY or SELECT TOP with a subquery.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;salary
120000Interviewers often follow up with "what if there is no second salary?" The query returns zero rows. Wrap it in a subquery inside SELECT (...) to return NULL instead.
2. Find the second highest salary in each department.
Now you need ranking per group. DENSE_RANK() with PARTITION BY department handles ties correctly (Ana and Ben tie at 150,000, so Chloe is second). Window functions cannot be filtered in WHERE, hence the subquery. Departments with only one salary (Support) correctly return nothing. See SQL window functions explained.
SELECT department, name, salary
FROM (
SELECT department, name, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 2;department | name | salary
Engineering | Chloe | 120000
Sales | Dev | 900003. Find duplicate rows in a table.
Group by the column that should be unique and keep groups with more than one row. HAVING filters groups after aggregation; WHERE cannot.
SELECT email, COUNT(*) AS times
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1;email | times
[email protected] | 3
[email protected] | 24. Delete duplicate rows but keep one copy.
Keep the lowest id per email and delete everything else. Run the SELECT version of the subquery first to check what you are about to keep. This works in every major database; in PostgreSQL and SQL Server you could also use ROW_NUMBER() in a CTE.
DELETE FROM contacts
WHERE id NOT IN (
SELECT MIN(id) FROM contacts GROUP BY email
);
SELECT * FROM contacts ORDER BY id;id | email
1 | [email protected]
2 | [email protected]
4 | [email protected]Always test a DELETE as a SELECT first, and wrap it in a transaction. Interviewers watch for this habit.
5. Find employees earning more than their department's average.
A correlated subquery references the outer row (e.department), so it recalculates the average for each department. The window-function alternative is salary > AVG(salary) OVER (PARTITION BY department) inside a subquery. Mention both.
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees WHERE department = e.department
);name | department | salary
Ana | Engineering | 150000
Ben | Engineering | 150000
Emma | Sales | 1100006. List each employee with their manager's name.
This is a self join: the same table joined to itself under two aliases. Use LEFT JOIN so the top of the tree (Ana, no manager) is not dropped. Read more in how to use a self join.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;employee | manager
Ana | NULL
Ben | Ana
Chloe | Ana
Dev | Ana
Emma | Dev
Felix | Dev
Gia | Dev7. Find employees who earn more than their manager.
Same self join, but an INNER JOIN and a comparison between the two aliases. Emma earns 110,000 against her manager Dev's 90,000.
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;name | salary | manager | manager_salary
Emma | 110000 | Dev | 900008. Find each user's login streaks (consecutive days).
This is the classic gaps and islands problem. Number each user's logins with ROW_NUMBER() and subtract that many days from the login date. Consecutive dates produce the same result, so that value identifies the streak. Group on it to get the start, end and length. (DATE(x, '-n days') is SQLite syntax; in PostgreSQL use login_date - (rn - 1) * INTERVAL '1 day', and in SQL Server DATEADD(day, -rn, login_date).)
WITH ranked AS (
SELECT user_id, login_date,
DATE(login_date, '-' || (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 1) || ' days') AS grp
FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
COUNT(*) AS days
FROM ranked
GROUP BY user_id, grp
ORDER BY user_id, streak_start;user_id | streak_start | streak_end | days
1 | 2026-03-01 | 2026-03-03 | 3
1 | 2026-03-07 | 2026-03-08 | 2
2 | 2026-03-01 | 2026-03-01 | 1
2 | 2026-03-03 | 2026-03-04 | 2
3 | 2026-03-05 | 2026-03-05 | 19. Find each user's longest streak.
Build on the previous query: put it in a CTE and take MAX of the streak lengths. Breaking a hard problem into named CTE steps is the habit interviewers want to see.
WITH ranked AS (
SELECT user_id, login_date,
DATE(login_date, '-' || (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 1) || ' days') AS grp
FROM logins
),
streaks AS (
SELECT user_id, COUNT(*) AS days FROM ranked GROUP BY user_id, grp
)
SELECT user_id, MAX(days) AS longest_streak
FROM streaks
GROUP BY user_id;user_id | longest_streak
1 | 3
2 | 2
3 | 110. Calculate the median salary.
Most databases have no portable MEDIAN(). Number the rows, count them, and average the one or two middle rows. For 7 rows that is row 4; for an even count it averages the two middle rows. PostgreSQL has PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), and SQL Server has PERCENTILE_CONT as a window function.
WITH ordered AS (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS n
FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);median_salary
110000.011. Turn rows into columns (pivot quarterly revenue).
Conditional aggregation: SUM(CASE WHEN ...) per target column. It is portable and works everywhere, unlike PIVOT, which only some databases support. The cost is that you must list the columns by hand. More in how to pivot in SQL.
SELECT rep,
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3
FROM sales
GROUP BY rep;rep | q1 | q2 | q3
Ana | 100 | 150 | 0
Ben | 80 | 60 | 9012. Calculate month-over-month revenue growth.
Aggregate to one row per month in a CTE, then use LAG() to bring in the previous month. The first month has no previous value, so it is NULL. Multiply by 100.0 (not 100) to avoid integer division. See LAG and LEAD.
WITH monthly AS (
SELECT STRFTIME('%Y-%m', order_date) AS month, SUM(amount) AS revenue
FROM orders
GROUP BY month
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
/ LAG(revenue) OVER (ORDER BY month), 1) AS pct_change
FROM monthly;month | revenue | prev_month | pct_change
2026-01 | 130 | NULL | NULL
2026-02 | 220 | 130 | 69.2
2026-03 | 230 | 220 | 4.513. Get each customer's first order.
ROW_NUMBER() partitioned by customer and ordered by date, then keep row number 1. Swap the sort to DESC for the latest order. This "top 1 per group" pattern comes up constantly.
SELECT customer_id, order_id, order_date, amount
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS rn
FROM orders
)
WHERE rn = 1;customer_id | order_id | order_date | amount
10 | 1 | 2026-01-05 | 50
11 | 2 | 2026-01-09 | 80
12 | 4 | 2026-02-14 | 40
13 | 7 | 2026-03-03 | 20014. Calculate a running total of order amounts.
SUM() OVER (ORDER BY ...). Adding order_id as a tiebreaker makes the order deterministic when two orders share a date. The default frame with ties is a classic trap; see the frames section of the window functions guide.
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total
FROM orders;order_date | amount | running_total
2026-01-05 | 50 | 50
2026-01-09 | 80 | 130
2026-02-02 | 120 | 250
2026-02-14 | 40 | 290
2026-02-20 | 60 | 350
2026-03-01 | 30 | 380
2026-03-03 | 200 | 58015. Show the full management chain with levels (recursive CTE).
A recursive CTE has an anchor (the CEO, who has no manager) and a recursive step that joins employees to the rows found so far. It stops when no new rows appear. Ana is level 0, her reports level 1, and so on. Recursive CTEs need WITH RECURSIVE in SQLite, PostgreSQL and MySQL; SQL Server and Oracle use plain WITH.
WITH RECURSIVE chain(id, name, level) AS (
SELECT id, name, 0 FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, c.level + 1
FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT name, level FROM chain ORDER BY level, name;name | level
Ana | 0
Ben | 1
Chloe | 1
Dev | 1
Emma | 2
Felix | 2
Gia | 2How to approach a live SQL coding round
- Restate the question and ask about edge cases. Ties? NULLs? Duplicates? Empty groups? Asking is a positive signal, not a weakness.
- Look at the schema first. Identify keys and which table holds each column you need.
- Build in steps. Write the innermost query, run it, then wrap it in a CTE. Named steps are easier to debug and to explain.
- Say your reasoning out loud, including the trade-off between a subquery, a join and a window function.
- Check the result against a few rows by hand, especially boundaries such as the first row, ties and the last group.
- Mention performance briefly: which columns you would index, and whether the query scans the table once.
Practice before the interview
You will not memorize your way through a coding round; you need reps. Work through the free interactive SQL lessons if the basics feel shaky, then do the SQL practice problems. Keep the SQL cheat sheet nearby for syntax, and read SQL window functions explained because ranking, LAG and running totals appear in most coding rounds.
Try these without looking at the solutions above, on the same sample tables:
- Find the department with the highest average salary.
- Find customers whose latest order is larger than their first order.
- Show each rep's revenue as a percentage of the team total.
- Find users who logged in on three consecutive days.
FAQ
What are the most common SQL coding interview questions?
Nth highest salary, finding and deleting duplicates, top-N per group, employee and manager self joins, running totals, month-over-month change, and consecutive-days problems.
Which SQL dialect should I use in a coding interview?
Whichever the company uses; ask at the start. The logic is the same everywhere, and only functions for dates, strings and limiting rows differ. Standard window functions and CTEs work in PostgreSQL, MySQL 8+, SQL Server and SQLite.
How hard are SQL coding interviews?
For analyst roles expect joins, aggregation and one window function problem. For data engineer or senior roles expect gaps and islands, recursive CTEs and query optimization. See the data analyst and data engineer question lists.
Can I use window functions in a SQL interview?
Yes, and you usually should. They are cleaner than self-join workarounds. Just be ready to explain what PARTITION BY and ORDER BY do.
How long does it take to prepare?
If you already know basic SELECT, JOIN and GROUP BY, about one to two weeks of daily practice on the patterns above is enough for most analyst-level rounds.