Advanced SQL Interview Questions and Answers (29 Q&As)

Twenty-nine questions for experienced candidates: window functions, recursive CTEs, gaps and islands, query plans, indexing, isolation levels and deadlocks. Every query was run on PostgreSQL 16 and the results are real.

By Matthew Rathbone · October 31, 2026

"Advanced" SQL interviews are aimed at people with a few years of experience. Expect fewer definitions and more problems: rank within groups, find streaks, walk a hierarchy, explain why a query is slow. This page has 29 questions of that kind, each with a short answer and, where it makes sense, a query and its real output.

If you are earlier in your journey, start with the 30 SQL interview questions and answers for the fundamentals, or the data analyst version. Use the SQL cheat sheet to refresh syntax.

Jump to: set up the data · window functions · CTEs and recursion · joins and subqueries · performance and indexing · transactions and design · how to answer · FAQ

Set up the practice database

Queries use PostgreSQL syntax and were run on PostgreSQL 16, because that is the dialect most advanced interviews allow and the one with the richest features. Each question notes where MySQL, SQL Server or SQLite differ. Paste the script below into any PostgreSQL client such as Beekeeper Studio, and try each question yourself before reading the answer.

The order_lines view joins orders, items and products so the examples stay short. The performance questions also use a larger events table, shown in that section.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, signup_date DATE);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price NUMERIC(8,2));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE, status TEXT);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER);
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER, department TEXT, salary INTEGER);
CREATE TABLE logins (user_id INTEGER, login_date DATE);

INSERT INTO customers VALUES
  (1, 'Ana Silva', 'Brazil', '2025-01-15'),
  (2, 'Ben Carter', 'USA', '2025-02-03'),
  (3, 'Chloe Dubois', 'France', '2025-02-20'),
  (4, 'Dev Patel', 'India', '2025-03-11'),
  (5, 'Emma Jones', 'USA', '2025-04-02'),
  (6, 'Felix Wagner', 'Germany', '2025-05-18'),
  (7, 'Grace Kim', 'USA', '2025-06-07'),
  (8, 'Hiro Tanaka', 'Japan', '2025-06-30');
INSERT INTO products VALUES
  (1, 'Laptop', 'Electronics', 899.00),
  (2, 'Headphones', 'Electronics', 129.50),
  (3, 'Desk Lamp', 'Home', 34.99),
  (4, 'Notebook', 'Stationery', 4.25),
  (5, 'Pen Set', 'Stationery', 12.00),
  (6, 'Office Chair', 'Home', 189.00),
  (7, 'Webcam', 'Electronics', 59.90),
  (8, 'Backpack', 'Accessories', 45.00);
INSERT INTO orders VALUES
  (1, 1, '2025-07-01', 'shipped'),
  (2, 2, '2025-07-03', 'shipped'),
  (3, 2, '2025-07-15', 'cancelled'),
  (4, 3, '2025-07-20', 'shipped'),
  (5, 5, '2025-08-02', 'shipped'),
  (6, 1, '2025-08-09', 'pending'),
  (7, 4, '2025-08-12', 'shipped'),
  (8, 5, '2025-08-21', 'shipped'),
  (9, 7, '2025-09-04', 'shipped'),
  (10, 2, '2025-09-10', 'shipped'),
  (11, 3, '2025-09-18', 'pending'),
  (12, 7, '2025-09-25', 'cancelled');
INSERT INTO order_items VALUES
  (1, 1, 1), (1, 4, 3), (2, 2, 1), (2, 7, 1), (3, 6, 1), (4, 3, 2), (4, 5, 2),
  (5, 8, 1), (5, 4, 5), (6, 2, 2), (7, 1, 1), (7, 2, 1), (8, 5, 3), (9, 6, 1),
  (9, 3, 1), (10, 4, 10), (10, 8, 2), (11, 7, 1), (12, 1, 1);
INSERT INTO employees VALUES
  (1, 'Priya Rao', NULL, 'Executive', 250000),
  (2, 'Marcus Hall', 1, 'Engineering', 180000),
  (3, 'Sofia Reyes', 1, 'Sales', 170000),
  (4, 'Liam Chen', 2, 'Engineering', 150000),
  (5, 'Aisha Khan', 2, 'Engineering', 150000),
  (6, 'Tom Becker', 4, 'Engineering', 155000),
  (7, 'Nina Petrov', 3, 'Sales', 110000),
  (8, 'Omar Haddad', 3, 'Sales', 125000);
INSERT INTO logins VALUES
  (1,'2025-01-01'),(1,'2025-01-02'),(1,'2025-01-03'),(1,'2025-01-05'),(1,'2025-01-06'),(1,'2025-01-09'),
  (2,'2025-01-01'),(2,'2025-01-03'),(2,'2025-01-04'),(2,'2025-01-05'),(2,'2025-01-06'),(2,'2025-01-07');
CREATE VIEW order_lines AS
SELECT o.id AS order_id, o.customer_id, o.order_date, o.status,
       p.id AS product_id, p.name AS product, p.category, oi.quantity,
       oi.quantity * p.price AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Window function questions

Window functions are the most common topic in advanced SQL interviews. If you are shaky on them, read LAG and LEAD and DENSE_RANK first.

1. What is the difference between a window function and GROUP BY?

GROUP BY collapses rows into one row per group. A window function computes a value across a set of related rows but keeps every row. Use a window function when you need row-level detail next to an aggregate, such as each order beside the customer's total.

SELECT order_id, customer_id,
       SUM(revenue) AS order_total,
       SUM(SUM(revenue)) OVER (PARTITION BY customer_id) AS customer_total
FROM order_lines
WHERE status = 'shipped'
GROUP BY order_id, customer_id
ORDER BY customer_id, order_id;

Result (8 rows):

order_id	customer_id	order_total	customer_total
1	1	911.75	911.75
2	2	189.40	321.90
10	2	132.50	321.90
4	3	93.98	93.98
7	4	1028.50	1028.50
5	5	66.25	102.25
8	5	36.00	102.25
9	7	223.99	223.99

The inner SUM is the GROUP BY aggregate (one value per order). The outer SUM() OVER then runs on those grouped rows. Window functions are evaluated after GROUP BY and HAVING, which is why you can wrap an aggregate inside one.

2. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

All three number rows within a partition. They differ only on ties. ROW_NUMBER gives every row a unique number (ties broken arbitrarily). RANK gives ties the same number and then skips. DENSE_RANK gives ties the same number and does not skip. Liam and Aisha both earn 150,000 here.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
WHERE department = 'Engineering';

Result (4 rows):

name	salary	row_num	rnk	dense_rnk
Marcus Hall	180000	1	1	1
Tom Becker	155000	2	2	2
Liam Chen	150000	3	3	3
Aisha Khan	150000	4	3	3

3. How do you get the top N rows per group?

Rank rows inside each group, then filter in an outer query. You cannot filter on a window function in the same WHERE, because WHERE runs before windows are computed. This returns the best-selling product (by shipped revenue) in each category.

WITH ranked AS (
  SELECT category, product, SUM(revenue) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(revenue) DESC) AS rn
  FROM order_lines
  WHERE status = 'shipped'
  GROUP BY category, product
)
SELECT category, product, revenue
FROM ranked
WHERE rn = 1
ORDER BY category;

Result (4 rows):

category	product	revenue
Accessories	Backpack	135.00
Electronics	Laptop	1798.00
Home	Office Chair	189.00
Stationery	Notebook	76.50

Use RANK or DENSE_RANK instead of ROW_NUMBER if you want to keep ties. Ask the interviewer which they want; it is a good clarifying question.

4. What is a window frame, and how do ROWS and RANGE differ?

The frame decides which rows around the current row feed the calculation. With an ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as peers and includes all of them. ROWS counts physical rows. The difference shows up on ties: the two 150,000 salaries get the same running total under RANGE, but different totals under ROWS (which of the two gets the lower total is arbitrary, because the tie has no defined order).

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary) AS running_range,
       SUM(salary) OVER (ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows
FROM employees
WHERE department = 'Engineering'
ORDER BY salary, name;

Result (4 rows):

name	salary	running_range	running_rows
Aisha Khan	150000	300000	300000
Liam Chen	150000	300000	150000
Tom Becker	155000	455000	455000
Marcus Hall	180000	635000	635000

When you want a true running total, specify ROWS explicitly. The default RANGE surprises people on tied dates.

5. How do you calculate a moving average?

Use an aggregate window with a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. The first rows average over fewer values because the frame runs off the start. This computes a 3-order moving average of shipped order totals.

WITH order_totals AS (
  SELECT order_id, order_date, SUM(revenue) AS total
  FROM order_lines
  WHERE status = 'shipped'
  GROUP BY order_id, order_date
)
SELECT order_id, order_date, total,
       ROUND(AVG(total) OVER (ORDER BY order_date
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM order_totals
ORDER BY order_date;

Result (8 rows):

order_id	order_date	total	moving_avg_3
1	2025-07-01	911.75	911.75
2	2025-07-03	189.40	550.58
4	2025-07-20	93.98	398.38
5	2025-08-02	66.25	116.54
7	2025-08-12	1028.50	396.24
8	2025-08-21	36.00	376.92
9	2025-09-04	223.99	429.50
10	2025-09-10	132.50	130.83

6. How do you find the time between a customer's consecutive orders?

Use LAG() to pull the previous row's value within each customer, then subtract. In PostgreSQL, subtracting two dates gives a number of days. The first order per customer has no previous order, so the gap is NULL.

SELECT customer_id, id AS order_id, order_date,
       order_date - LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) AS days_since_previous
FROM orders
ORDER BY customer_id, order_date;

Result (12 rows):

customer_id	order_id	order_date	days_since_previous
1	1	2025-07-01	NULL
1	6	2025-08-09	39
2	2	2025-07-03	NULL
2	3	2025-07-15	12
2	10	2025-09-10	57
3	4	2025-07-20	NULL
3	11	2025-09-18	60
4	7	2025-08-12	NULL
5	5	2025-08-02	NULL
5	8	2025-08-21	19
7	9	2025-09-04	NULL
7	12	2025-09-25	21

7. What is the gaps and islands problem? Find each user's consecutive login streaks.

Gaps and islands asks you to group consecutive values into runs. The classic trick: subtract a row number from the date. Within a streak both increase by one per row, so the difference is constant, and that constant identifies the island.

WITH numbered AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM logins
)
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       COUNT(*) AS days
FROM numbered
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Result (5 rows):

user_id	streak_start	streak_end	days
1	2025-01-01	2025-01-03	3
1	2025-01-05	2025-01-06	2
1	2025-01-09	2025-01-09	1
2	2025-01-01	2025-01-01	1
2	2025-01-03	2025-01-07	5

This assumes one login row per user per day. If duplicates are possible, SELECT DISTINCT the dates first.

8. How do you calculate a median in SQL?

There is no MEDIAN() in standard SQL. PostgreSQL has the ordered-set aggregate PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...), which interpolates between the two middle values on even counts. SQL Server uses PERCENTILE_CONT as a window function, and MySQL has no built-in, so you use ROW_NUMBER and average the middle rows. Compare the median with the mean of shipped order totals:

WITH order_totals AS (
  SELECT order_id, SUM(revenue) AS total
  FROM order_lines
  WHERE status = 'shipped'
  GROUP BY order_id
)
SELECT ROUND(AVG(total), 2) AS mean,
       ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total)::numeric, 2) AS median
FROM order_totals;

Result (1 row):

mean	median
335.30	160.95

The mean is pulled up by the few big laptop orders, which is why medians are preferred for skewed data like order values or salaries.

CTE and recursion questions

9. What is a recursive CTE? Show an org chart traversal.

A recursive CTE has an anchor query (the starting rows) and a recursive query that joins back to the CTE itself, joined with UNION ALL. It repeats until the recursive part returns no new rows. It is the standard way to walk hierarchies such as org charts, category trees and bills of materials.

WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS level, name::text AS path
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, org.level + 1, org.path || ' > ' || e.name
  FROM employees e
  JOIN org ON e.manager_id = org.id
)
SELECT level, name, path
FROM org
ORDER BY path;

Result (8 rows):

level	name	path
1	Priya Rao	Priya Rao
2	Marcus Hall	Priya Rao > Marcus Hall
3	Aisha Khan	Priya Rao > Marcus Hall > Aisha Khan
3	Liam Chen	Priya Rao > Marcus Hall > Liam Chen
4	Tom Becker	Priya Rao > Marcus Hall > Liam Chen > Tom Becker
2	Sofia Reyes	Priya Rao > Sofia Reyes
3	Nina Petrov	Priya Rao > Sofia Reyes > Nina Petrov
3	Omar Haddad	Priya Rao > Sofia Reyes > Omar Haddad

Always make sure the recursion terminates. Cyclic data (A manages B, B manages A) loops forever unless you track visited ids or cap the depth with a level < 10 condition.

10. How do you fill gaps in a time series so empty periods show as zero?

Generate the full calendar, then LEFT JOIN your data onto it. Months with no orders would otherwise be missing from the result, and a chart or report would silently skip them. PostgreSQL has generate_series; other databases use a recursive CTE or a calendar table.

WITH months AS (
  SELECT generate_series(DATE '2025-07-01', DATE '2025-10-01', INTERVAL '1 month')::date AS month
),
monthly AS (
  SELECT DATE_TRUNC('month', order_date)::date AS month, SUM(revenue) AS revenue
  FROM order_lines
  WHERE status = 'shipped'
  GROUP BY 1
)
SELECT m.month, COALESCE(ROUND(x.revenue, 2), 0) AS revenue
FROM months m
LEFT JOIN monthly x ON x.month = m.month
ORDER BY m.month;

Result (4 rows):

month	revenue
2025-07-01	1195.13
2025-08-01	1130.75
2025-09-01	356.49
2025-10-01	0

11. What is the difference between a CTE, a subquery, a view and a temporary table?

Subquery: a query nested inside another, valid for one statement.
CTE (WITH): a named subquery at the top of a statement. Mostly readability, and the only way to write recursion. Since PostgreSQL 12 a non-recursive CTE used once is inlined, and you can force a one-time evaluation with AS MATERIALIZED.
View: a saved query stored in the database. It holds no data (unless it is a materialized view).
Temporary table: real rows stored for the session. Use one when you reuse an expensive intermediate result many times or need to index it.

WITH order_totals AS MATERIALIZED (
  SELECT order_id, SUM(revenue) AS total
  FROM order_lines
  GROUP BY order_id
)
SELECT COUNT(*) AS orders_over_100
FROM order_totals
WHERE total > 100;

Result (1 row):

orders_over_100
8

Joins and subqueries

12. Why can NOT IN return no rows when NOT EXISTS works? (The NULL trap)

x NOT IN (a, b, NULL) is never true: comparing to NULL yields unknown, so no row passes. One NULL in the subquery silently empties your result. NOT EXISTS does not have this problem. Here we find customers with no orders, after adding a NULL customer_id to the subquery on purpose:

WITH order_customers AS (
  SELECT customer_id FROM orders
  UNION ALL
  SELECT NULL
)
SELECT
  (SELECT COUNT(*) FROM customers
   WHERE id NOT IN (SELECT customer_id FROM order_customers)) AS not_in_rows,
  (SELECT COUNT(*) FROM customers c
   WHERE NOT EXISTS (SELECT 1 FROM order_customers o
                     WHERE o.customer_id = c.id)) AS not_exists_rows;

Result (1 row):

not_in_rows	not_exists_rows
0	2

The correct answer is 2 (Felix and Hiro never ordered). Prefer NOT EXISTS or a LEFT JOIN ... WHERE right.id IS NULL for anti-joins.

13. What is a self join? Find employees who earn more than their manager.

A self join joins a table to itself using two aliases, one playing each role. Here e is the employee and m is their manager. See our self join guide for more.

SELECT e.name AS employee, e.salary AS employee_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;

Result (1 row):

employee	employee_salary	manager	manager_salary
Tom Becker	155000	Liam Chen	150000

14. How do you find the second highest salary in each department?

Use DENSE_RANK() partitioned by department, then keep rank 2. DENSE_RANK is the right choice because two people on the same salary should share a rank without skipping the next one. Departments with fewer than two distinct salaries return nothing, which you should mention.

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

Result (2 rows):

department	name	salary
Engineering	Tom Becker	155000
Sales	Omar Haddad	125000

15. What is a LATERAL join? Get each customer's two most recent orders.

LATERAL lets a subquery in the FROM clause reference columns from tables to its left, like a loop that runs once per row. It is ideal for "top N per row" and pairs well with LIMIT. SQL Server calls the equivalent CROSS APPLY and OUTER APPLY.

SELECT c.name, o.id AS order_id, o.order_date
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, order_date
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  LIMIT 2
) o
ORDER BY c.name, o.order_date DESC;

Result (11 rows):

name	order_id	order_date
Ana Silva	6	2025-08-09
Ana Silva	1	2025-07-01
Ben Carter	10	2025-09-10
Ben Carter	3	2025-07-15
Chloe Dubois	11	2025-09-18
Chloe Dubois	4	2025-07-20
Dev Patel	7	2025-08-12
Emma Jones	8	2025-08-21
Emma Jones	5	2025-08-02
Grace Kim	12	2025-09-25
Grace Kim	9	2025-09-04

Use LEFT JOIN LATERAL (...) o ON true if you want customers with no orders to still appear.

16. How do you find and delete duplicate rows, keeping one copy?

Number the rows inside each duplicate group with ROW_NUMBER(), then delete everything past the first. Always run the SELECT version first so you can see what would go.

CREATE TEMP TABLE signups (id SERIAL PRIMARY KEY, email TEXT, created_at DATE);
INSERT INTO signups (email, created_at) VALUES
  ('[email protected]', '2025-01-01'),
  ('[email protected]', '2025-01-02'),
  ('[email protected]', '2025-01-05'),
  ('[email protected]', '2025-01-09'),
  ('[email protected]', '2025-01-10');

DELETE FROM signups
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM signups
  ) t
  WHERE rn > 1
);

SELECT id, email, created_at FROM signups ORDER BY id;

Result (3 rows):

id	email	created_at
1	[email protected]	2025-01-01
2	[email protected]	2025-01-02
5	[email protected]	2025-01-10

17. How do you pivot rows into columns?

Most databases lack a general PIVOT, so use conditional aggregation: one aggregate per output column, each filtered with CASE WHEN (or PostgreSQL's FILTER). It works everywhere and the columns are explicit. See how to pivot in SQL.

SELECT category,
       ROUND(SUM(revenue) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 7), 2) AS jul,
       ROUND(SUM(revenue) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 8), 2) AS aug,
       ROUND(SUM(revenue) FILTER (WHERE EXTRACT(MONTH FROM order_date) = 9), 2) AS sep
FROM order_lines
WHERE status = 'shipped'
GROUP BY category
ORDER BY category;

Result (4 rows):

category	jul	aug	sep
Accessories	NULL	45.00	90.00
Electronics	1088.40	1028.50	NULL
Home	69.98	NULL	223.99
Stationery	36.75	57.25	42.50

The NULLs are months with no sales for that category. Wrap with COALESCE(..., 0) if you want zeros. The column list is fixed in the query; for a dynamic set of columns you need dynamic SQL.

18. What are ROLLUP and GROUPING SETS?

They compute several levels of aggregation in one query. ROLLUP(a, b) returns the detail rows, subtotals for each a, and a grand total. GROUPING SETS lets you list exactly which groupings you want, and CUBE gives every combination. Subtotal rows show NULL in the rolled-up column.

SELECT category, product, ROUND(SUM(revenue), 2) AS revenue
FROM order_lines
WHERE status = 'shipped'
GROUP BY ROLLUP (category, product)
ORDER BY category NULLS LAST, product NULLS LAST;

Result (13 rows):

category	product	revenue
Accessories	Backpack	135.00
Accessories	NULL	135.00
Electronics	Headphones	259.00
Electronics	Laptop	1798.00
Electronics	Webcam	59.90
Electronics	NULL	2116.90
Home	Desk Lamp	104.97
Home	Office Chair	189.00
Home	NULL	293.97
Stationery	Notebook	76.50
Stationery	Pen Set	60.00
Stationery	NULL	136.50
NULL	NULL	2682.37

19. How do you upsert in SQL? What is MERGE?

An upsert inserts a row, or updates it if the key already exists. PostgreSQL and SQLite use INSERT ... ON CONFLICT, MySQL uses INSERT ... ON DUPLICATE KEY UPDATE, and SQL Server, Oracle and PostgreSQL 15+ support the standard MERGE statement. The key must have a unique constraint for conflict detection to work.

CREATE TEMP TABLE stock (product_id INTEGER PRIMARY KEY, qty INTEGER);
INSERT INTO stock VALUES (1, 10), (2, 5);

INSERT INTO stock (product_id, qty) VALUES (2, 20), (3, 8)
ON CONFLICT (product_id)
DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

SELECT product_id, qty FROM stock ORDER BY product_id;

Result (3 rows):

product_id	qty
1	10
2	25
3	8

Product 2 existed, so its quantity became 5 + 20. Product 3 was new, so it was inserted.

Performance and indexing questions

The next questions use a table called events with 200,000 rows (id, user_id, created_at, kind). Plans below came from PostgreSQL 16 after running ANALYZE; exact cost numbers will differ on your machine, but the plan shapes should match.

CREATE TABLE events (id SERIAL PRIMARY KEY, user_id INTEGER, created_at TIMESTAMP, kind TEXT);
INSERT INTO events (user_id, created_at, kind)
SELECT (g % 5000) + 1,
       TIMESTAMP '2025-01-01' + (g || ' minutes')::interval,
       (ARRAY['view','click','buy'])[1 + g % 3]
FROM generate_series(1, 200000) g;
ANALYZE;

20. How do you read a query plan with EXPLAIN?

EXPLAIN shows how the database intends to run a query without running it. EXPLAIN ANALYZE runs it and adds actual times and row counts (be careful with UPDATE or DELETE, because they really execute). Read plans from the innermost, most-indented node outward, and look at the scan type, estimated rows against actual rows, and where most of the time goes. With no index on user_id, the database has to read the whole table:

EXPLAIN SELECT * FROM events WHERE user_id = 42;

Plan:

Seq Scan on events  (cost=0.00..3774.00 rows=40 width=21)
  Filter: (user_id = 42)

Seq Scan means every row was read and filtered. That is fine for a small table or a query returning a large share of rows, and a red flag for a selective filter on a big table. Related: how to improve SQL query performance.

21. How does adding an index change the plan?

An index lets the database jump straight to matching rows instead of reading everything. The trade-off: every index speeds up reads on its columns but slows down inserts, updates and deletes, and takes disk space. See how to create an index in PostgreSQL.

CREATE INDEX idx_events_user_id ON events (user_id);
ANALYZE events;
EXPLAIN SELECT * FROM events WHERE user_id = 42;

Plan:

Bitmap Heap Scan on events  (cost=4.60..143.84 rows=40 width=20)
  Recheck Cond: (user_id = 42)
  ->  Bitmap Index Scan on idx_events_user_id  (cost=0.00..4.59 rows=40 width=0)
        Index Cond: (user_id = 42)

The plan switched from Seq Scan to an index-driven scan touching only the matching rows. A Bitmap Heap Scan is common when an index returns a moderate number of rows; a plain Index Scan appears for very selective lookups.

22. What does it mean for a query to be sargable?

A predicate is sargable (Search ARGument ABLE) when the database can use an index to evaluate it. Wrapping the indexed column in a function, or doing arithmetic on it, usually prevents index use because the index stores the raw column values. Rewrite the condition so the column stands alone. With an index on created_at, this version has to scan the table:

CREATE INDEX idx_events_created_at ON events (created_at);
ANALYZE events;
EXPLAIN SELECT * FROM events
WHERE created_at::date = DATE '2025-02-01';

Plan:

Gather  (cost=1000.00..4138.71 rows=1000 width=21)
  Workers Planned: 1
  ->  Parallel Seq Scan on events  (cost=0.00..3038.71 rows=588 width=21)
        Filter: ((created_at)::date = '2025-02-01'::date)

23. How do you fix a non-sargable filter?

Turn the function call into a range on the bare column. Same rows, but the planner can use the index:

EXPLAIN SELECT * FROM events
WHERE created_at >= TIMESTAMP '2025-02-01'
  AND created_at <  TIMESTAMP '2025-02-02';

Plan:

Index Scan using idx_events_created_at on events  (cost=0.42..57.90 rows=1424 width=21)
  Index Cond: ((created_at >= '2025-02-01 00:00:00'::timestamp without time zone) AND (created_at < '2025-02-02 00:00:00'::timestamp without time zone))

The same idea applies to LOWER(email) = ... (create an expression index, or store normalised values), WHERE YEAR(date) = 2025 in MySQL and SQL Server, and WHERE col + 1 = 10. Leading wildcards such as LIKE '%abc' also defeat a normal B-tree index.

24. What is a composite index and why does column order matter?

A composite index covers several columns, sorted by the first, then the second within it, and so on. It can serve a query that filters on a leftmost prefix of its columns. An index on (user_id, created_at) helps WHERE user_id = 42 and WHERE user_id = 42 AND created_at > ..., but does little for WHERE created_at > ... alone. Put equality columns first and range columns last. A covering index contains every column the query needs, so the table itself is never read (in PostgreSQL, an index-only scan, with INCLUDE columns for extras).

EXPLAIN SELECT created_at FROM events
WHERE user_id = 42 AND created_at >= TIMESTAMP '2025-02-01'
ORDER BY created_at;

Plan:

Sort  (cost=144.71..144.79 rows=31 width=8)
  Sort Key: created_at
  ->  Bitmap Heap Scan on events  (cost=4.60..143.94 rows=31 width=8)
        Recheck Cond: (user_id = 42)
        Filter: (created_at >= '2025-02-01 00:00:00'::timestamp without time zone)
        ->  Bitmap Index Scan on idx_events_user_id  (cost=0.00..4.59 rows=40 width=0)
              Index Cond: (user_id = 42)

That plan still uses the single-column indexes from the last questions. Creating (user_id, created_at) would let one index satisfy both the filter and the sort.

25. Why is OFFSET pagination slow, and what is keyset pagination?

OFFSET 100000 still reads and discards the first 100,000 rows on every request, so deep pages get slower and slower. Rows inserted while a user pages can also cause skipped or repeated items. Keyset (seek) pagination remembers the last key seen and asks for rows after it, which an index answers in constant time per page.

SELECT id, user_id, kind
FROM events
WHERE id > 199990          -- last id from the previous page
ORDER BY id
LIMIT 5;

Result (5 rows):

id	user_id	kind
199991	4992	buy
199992	4993	view
199993	4994	click
199994	4995	buy
199995	4996	view

The trade-off: you cannot jump to an arbitrary page number, and the sort key must be unique (add a tiebreaker column like id if you sort by something non-unique).

Transactions and design questions

26. What are transaction isolation levels and the anomalies they prevent?

Isolation controls how much concurrent transactions can see of each other's work. Stronger isolation means fewer anomalies but more blocking or retries.

Level	Dirty read	Non-repeatable read	Phantom read
Read uncommitted	Possible	Possible	Possible
Read committed	No	Possible	Possible
Repeatable read	No	No	Possible by the standard (PostgreSQL prevents it here)
Serializable	No	No	No

A dirty read sees uncommitted data. A non-repeatable read gets different values when the same row is read twice. A phantom gets different rows when the same query runs twice. The default is Read Committed in PostgreSQL, SQL Server and Oracle, and Repeatable Read in MySQL InnoDB. PostgreSQL treats Read Uncommitted as Read Committed.

27. What is a deadlock and how do you prevent one?

A deadlock happens when two transactions each hold a lock the other needs, so neither can proceed. The database detects the cycle and aborts one transaction. Prevent them by accessing tables and rows in a consistent order everywhere, keeping transactions short, avoiding user interaction inside a transaction, indexing the columns used in UPDATE and DELETE filters (fewer rows locked), and retrying the aborted transaction in application code.

28. Normalization vs denormalization: when would you denormalize?

Normalization stores each fact once to avoid update anomalies; it is the default for transactional (OLTP) systems. Denormalization deliberately duplicates data to make reads cheaper, which is common in analytics (OLAP) warehouses with star schemas, in reporting tables, and for hot read paths where a join is too expensive. The cost is extra storage and the risk of copies drifting apart, so you need a clear process for keeping them in sync. See what is normalization.

29. A query that used to be fast is now slow. How do you debug it?

Interviewers ask this to see your process more than a single answer:

Reproduce it and capture the exact query and parameters.
Run EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows with actual rows. Big mismatches point to stale statistics: run ANALYZE.
Look for sequential scans on large tables, missing or unused indexes, non-sargable filters, and joins that multiply rows.
Check what changed: data volume, a new deployment, a dropped index, or a different plan after an upgrade.
Check for blocking (long transactions, locks) and table bloat before blaming the query.
Fix the smallest thing (index, rewrite, statistics), then measure again.

How to answer advanced SQL questions in an interview

Clarify first. Ask about ties, NULLs, duplicates and which statuses count. Interviewers often hide an edge case in the wording on purpose.
Think out loud. Say the grain of each table and what one row means after each join. Wrong answers usually come from accidental row multiplication.
Build it in steps. Write a CTE for each stage and check it. It reads cleanly and you can debug it live.
Know one alternative. "I would use NOT EXISTS, but a LEFT JOIN anti-join also works" shows depth.
Talk about cost. For any query on a big table, mention indexes and how you would check the plan.
Name your dialect. State which database you are assuming, and mention the main syntax difference if you know it.

Where to go next

Using a specific database? See the PostgreSQL, MySQL and SQL Server interview questions.
Practice with 28 SQL practice problems with solutions and 38 SQL query examples.
Revisit the basics interactively: GROUP BY, joins and LEFT JOIN.

FAQ

What counts as an advanced SQL interview question?

Questions that go beyond SELECT, joins and GROUP BY: window functions, recursive CTEs, top-N-per-group, gaps and islands, query plans and indexing, transactions and isolation levels, and trade-offs in schema design.

What SQL topics should an experienced candidate know?

Window functions and frames, CTEs (including recursive), correlated subqueries and EXISTS, NULL semantics, indexes and EXPLAIN, transactions, isolation and locking, plus normalization and when to denormalize.

How should I prepare for an advanced SQL interview?

Solve timed problems, not just read answers. Work through the questions above in your own client, then try variations (change the grouping, add ties, add NULLs). Practice explaining a query plan aloud.

Are these questions the same on MySQL and SQL Server?

The concepts are. Syntax differs in places: generate_series, LATERAL, FILTER and PERCENTILE_CONT as an aggregate are PostgreSQL-flavoured. MySQL 8 and SQL Server support window functions and recursive CTEs; SQL Server uses CROSS APPLY for lateral joins and TOP instead of LIMIT.

Where can I practice SQL for free?

Use SQL Easy's free interactive lessons, which run in your browser with instant feedback, then move on to the practice problems.