SQL Interview Questions and Answers (2026): Joins, Window Functions, Indexes and Practice Queries

The short answer: SQL interviews test two things: whether you can write correct queries under time pressure and whether you understand what the database does with them. The questions that come up again and again are joins (and the LEFT JOIN filter trap), NULL handling (especially NOT IN), GROUP BY with HAVING, subqueries and CTEs, window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, running totals), indexes and EXPLAIN, and transactions and isolation levels. Classic practice problems: the second-highest salary, the top N per group, duplicates, customers with no orders, month-over-month growth and consecutive-day streaks. The answers and queries below are checked against the PostgreSQL and SQL Server documentation, with sources at the end. Most examples use PostgreSQL syntax, the database 55.6% of respondents used in Stack Overflow’s 2025 developer survey.

What a SQL interview looks like

It depends on the role:

• Data analysts write queries live or on a platform like HackerRank or CoderPad: aggregations, joins, window functions and date logic on business questions (“monthly revenue by region”). See our HackerRank interview tips.
• Data engineers and data scientists get the same, plus deduplication, data modeling and performance. See our data engineer interview questions and data science interview prep.
• Backend developers are asked about indexes, transactions, isolation levels, locking and schema design. See our backend developer interview questions.

Talk while you write: state your assumptions (can there be ties? NULLs? duplicates?), write the simple version first, then refine. Interviewers grade the reasoning as much as the final query.

The examples below use four tables:

employees   (id, name, department_id, manager_id, salary, hired_at)
departments (id, name)
customers   (id, name, email)
orders      (id, customer_id, amount, created_at)

Fundamentals

“In what order is a SQL query processed?”

Not in the order you write it. The logical processing order is FROM, ON, JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then TOP/LIMIT. Microsoft’s documentation explains the consequence: because SELECT comes late, “any column aliases or derived columns defined in that clause can’t be referenced by preceding clauses,” but they can be used in ORDER BY. That’s why WHERE total > 100 fails when total is a SELECT alias. The database may physically execute the query in a different order; this is the logical order that decides what each clause can see.

“What’s the difference between WHERE and HAVING?”

WHERE filters rows before grouping; HAVING filters groups after aggregation, so it can use aggregates:

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hired_at >= '2020-01-01'      -- rows
GROUP BY department_id
HAVING AVG(salary) > 80000;         -- groups

“Explain the types of joins.”

• INNER JOIN: only rows with a match on both sides.
• LEFT JOIN: every row from the left table, with NULLs where the right side has no match. RIGHT JOIN is the mirror image; most people rewrite it as a LEFT JOIN.
• FULL OUTER JOIN: every row from both sides, matched where possible.
• CROSS JOIN: every combination (a Cartesian product).
• Self join: a table joined to itself, such as employees to their managers.

Two follow-ups interviewers like: a join on a non-unique key multiplies rows (one customer with three orders appears three times, which inflates SUMs computed after the join), and an anti-join, “rows with no match,” is a LEFT JOIN ... WHERE right.id IS NULL or a NOT EXISTS.

“UNION or UNION ALL?”

UNION removes duplicate rows, which costs a sort or hash step; UNION ALL keeps everything and is faster. Use UNION ALL unless you need deduplication.

“DELETE, TRUNCATE or DROP?”

DELETE removes chosen rows (with a WHERE), one by one, firing triggers. TRUNCATE empties the whole table quickly. DROP removes the table itself. Rollback behavior depends on the database: in PostgreSQL, TRUNCATE inside a transaction can be rolled back; in MySQL it causes an implicit commit.

“Primary key, unique key and foreign key?”

A primary key identifies each row: unique, not NULL, one per table. A unique constraint also prevents duplicates but can sit on any column, and most databases allow NULLs in it. A foreign key makes a column reference a key in another table, so you can’t create an order for a customer who doesn’t exist.

NULL questions

“Why doesn’t WHERE manager_id = NULL return anything?”

NULL means “unknown,” and any comparison with it is unknown, not true. SQL uses three-valued logic (true, false, unknown), and WHERE keeps only rows where the condition is true. Use IS NULL and IS NOT NULL. COALESCE(col, default) replaces NULLs, and aggregates like SUM and AVG skip them.

“What’s the difference between COUNT(*), COUNT(col) and COUNT(DISTINCT col)?”

COUNT(*) counts rows; COUNT(col) counts rows where col is not NULL; COUNT(DISTINCT col) counts distinct non-NULL values. For a column holding 1, NULL, 1, the answers are 3, 2 and 1.

“Why is NOT IN dangerous?”

Because of NULLs. The PostgreSQL documentation: if there are no equal values “and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true.” So one NULL in the subquery makes the query return nothing:

-- Returns no rows at all if any orders.customer_id is NULL
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe: NOT EXISTS is only true or false
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Subqueries and CTEs

“What is a correlated subquery? EXISTS or IN?”

A correlated subquery refers to the outer query, so it’s evaluated per outer row (WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)). EXISTS only checks whether any row comes back, and PostgreSQL stops “long enough to determine whether at least one row is returned.” Modern planners often turn IN and EXISTS into the same join plan; the real difference is the NULL behavior of NOT IN.

“What is a CTE, and what is a recursive CTE?”

A common table expression (WITH name AS (...)) names a subquery so a long query reads top to bottom. A recursive CTE refers to itself, which is how you walk hierarchies such as an org chart:

-- Everyone who reports, directly or indirectly, to employee 42
WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE id = 42
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;

SQL Server writes WITH chain AS without the RECURSIVE keyword.

Window functions

“What is a window function, and how is it different from GROUP BY?”

A window function computes a value across related rows without collapsing them: GROUP BY returns one row per group; AVG(salary) OVER (PARTITION BY department_id) returns every employee with their department’s average beside them. PARTITION BY sets the groups, ORDER BY inside OVER sets the order, and the frame sets which rows count.

“ROW_NUMBER, RANK or DENSE_RANK?”

They differ on ties (salaries 100, 90, 90, 80):

Use ROW_NUMBER to pick exactly one row per group (deduplication), DENSE_RANK for “the second-highest value,” and RANK for competition-style rankings.

“What do LAG and LEAD do? How do you compute a running total?”

LAG(col) reads the previous row’s value and LEAD(col) the next row’s, within the partition and order; the offset defaults to 1 and the default to NULL. A running total is SUM(amount) OVER (ORDER BY created_at). For a moving average over the last seven rows, make the frame explicit: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW).

“Why does LAST_VALUE return the current row?”

The default frame with an ORDER BY runs from the start of the partition “through the last peer of the current row,” which the PostgreSQL docs say “is likely to give unhelpful results for last_value.” Fix it with an explicit frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Indexes and performance

“How does an index work, and what does it cost?”

Most indexes are B-trees: sorted structures that let the database find rows by value without scanning the whole table, and that also help ORDER BY and range queries. They cost disk space and slow down every INSERT, UPDATE and DELETE, which must update each index. So you index the columns you filter, join and sort on, not everything.

“How do composite indexes work?”

An index on (department_id, hired_at) is sorted by department first, then hire date. PostgreSQL’s docs: a multicolumn B-tree index “is most efficient when there are constraints on the leading (leftmost) columns.” It serves WHERE department_id = 3 AND hired_at > ... well, and WHERE hired_at > ... alone poorly. PostgreSQL 18 added skip scan, which can use such an index without a condition on the first column when that column has few distinct values. The docs also advise that multicolumn indexes “should be used sparingly.”

“Why isn’t my index being used?”

Common reasons: a function on the column (WHERE LOWER(email) = ...; create an expression index instead), a leading wildcard (LIKE '%son'), a type mismatch that forces a cast, a condition that matches a large share of the table (a full scan is cheaper), a tiny table, or stale statistics. Check with EXPLAIN to see the plan and EXPLAIN ANALYZE to run it with real timings. EXPLAIN ANALYZE actually executes the statement, so wrap an UPDATE or DELETE in a transaction and roll it back.

“Clustered or non-clustered index?”

In SQL Server and MySQL’s InnoDB engine, a clustered index stores the table’s rows in index order (InnoDB clusters by primary key), so there’s one per table; other indexes are secondary and point to it. PostgreSQL stores tables as unordered heaps, and every index is secondary. A covering index includes every column a query needs, so the database can answer from the index alone.

Transactions and isolation

“What does ACID mean?”

• Atomicity: all of a transaction happens, or none of it.
• Consistency: constraints hold before and after.
• Isolation: concurrent transactions don’t see each other’s partial work, to the degree the isolation level promises.
• Durability: once committed, data survives a crash.

“Explain the isolation levels.”

The SQL standard defines four levels by which anomalies they allow:

In PostgreSQL, “Read Committed is the default isolation level,” read uncommitted “behaves like Read Committed,” and repeatable read also prevents phantom reads. Higher levels give stronger guarantees but more conflicts: under serializable, the application must be ready to retry transactions that fail.

“How do you prevent two users from overselling the last item?”

Either lock the row while you check and update it (pessimistic: SELECT ... FOR UPDATE inside a transaction), or update conditionally and check the result (optimistic: UPDATE stock SET qty = qty - 1 WHERE id = 7 AND qty > 0, or a version column), then retry or fail if no row changed. A unique or check constraint is the final safety net. Deadlocks happen when two transactions lock rows in opposite orders; lock in a consistent order and keep transactions short.

Database design

“What is normalization? When do you denormalize?”

Normalization removes redundancy so each fact lives in one place. 1NF: atomic values, no repeating groups. 2NF: every non-key column depends on the whole key, not part of a composite key. 3NF: non-key columns depend only on the key, not on other non-key columns. Normalized schemas suit transactional systems (OLTP). Analytical systems (OLAP) often denormalize into star schemas, with a fact table and dimension tables, so reports need fewer joins. Materialized views and summary tables are controlled denormalization.

“View or materialized view?”

A view is a saved query that runs each time you select from it. A materialized view stores the result and must be refreshed, which trades freshness for speed.

SQL dialects: what changes between databases

Interviews rarely penalize dialect details; say which one you’re using. PostgreSQL 18, released in September 2025, added things interviewers sometimes mention: uuidv7() for time-ordered IDs, virtual generated columns as the default, OLD and NEW in RETURNING, and skip scan. PostgreSQL 19 reached its first release candidate in October 2026.

“What does this return?” puzzles

The LEFT JOIN that became an INNER JOIN

-- Goal: every customer, with their 2026 orders if they have any
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01';

Answer: customers without 2026 orders disappear. For them o.created_at is NULL, and the WHERE filters them out. Move the condition into the join: LEFT JOIN orders o ON o.customer_id = c.id AND o.created_at >= '2026-01-01'.

An aggregate in WHERE

SELECT name FROM employees WHERE salary > AVG(salary);

Answer: an error: aggregates aren’t allowed in WHERE, because WHERE runs before grouping. Use a subquery (WHERE salary > (SELECT AVG(salary) FROM employees)) or a window function in a subquery.

NOT IN with a NULL

SELECT 1 WHERE 3 NOT IN (1, 2, NULL); Answer: no row. 3 <> NULL is unknown, so the whole NOT IN is unknown, not true.

Practice problems with solutions

1. The second-highest salary

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

It returns NULL when there’s no second value, which is usually what the interviewer wants. Say how you’d handle ties, and mention the DENSE_RANK() = 2 version for the “Nth highest” follow-up.

2. The top three earners in each department

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

With DENSE_RANK, ties can return more than three people; use ROW_NUMBER if exactly three are required.

3. Find and delete duplicates

-- Find duplicate emails
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

-- Keep the lowest id for each email (PostgreSQL and SQL Server)
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

Then add a unique constraint so it can’t happen again.

4. Customers who never ordered

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

NOT EXISTS works too. Avoid NOT IN for the reason above.

5. Monthly revenue, running total and month-over-month change

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

Follow-up: months with no orders are missing; generate a calendar (PostgreSQL’s generate_series) and LEFT JOIN to it.

6. Employees who earn more than their manager

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;

7. Users with a login streak of three days or more (“gaps and islands”)

WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
grouped AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date))::int AS grp
  FROM days
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

The trick: subtracting the row number from consecutive dates gives the same value for every day in a streak, so each streak becomes a group.

8. Each customer’s first order

SELECT customer_id, id AS first_order_id, created_at
FROM (
  SELECT customer_id, id, created_at,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at, id) AS rn
  FROM orders
) o
WHERE rn = 1;

Adding id to the ORDER BY makes the result deterministic when two orders share a timestamp. PostgreSQL also has DISTINCT ON (customer_id) for this.

Questions by level

• Beginner: SELECT, filtering, sorting, joins, GROUP BY and HAVING, NULLs, primary and foreign keys, DELETE vs TRUNCATE.
• Intermediate: subqueries and CTEs, window functions, the LEFT JOIN and NOT IN traps, indexes and EXPLAIN, normalization, dates and time zones.
• Advanced: query tuning with plans, composite and covering indexes, isolation levels and locking, deadlocks, partitioning, recursive queries, gaps and islands, and how you’d design a schema for a new feature.

Behavioral questions about SQL work

Use the STAR method with a real example:

• “Tell me about a slow query you fixed. How did you find the cause?”
• “Describe a time your numbers didn’t match another team’s. How did you find the difference?”
• “Tell me about a data quality problem you caught before it reached a report.”
• “How do you explain a technical result to someone who doesn’t write SQL?”

More: our business analyst interview questions, Snowflake interview questions and Node.js interview questions.

Frequently asked questions

Sources

• Microsoft Learn, SELECT (Transact-SQL): logical processing order of the SELECT statement. Read on 4 November 2026.
• PostgreSQL 18 documentation: Subquery expressions (EXISTS, IN, NOT IN), Window functions, Multicolumn indexes and Transaction isolation. Read on 4 November 2026.
• PostgreSQL, PostgreSQL 18 released (September 25, 2025) and Versioning policy. Read on 4 November 2026.
• Stack Overflow, 2025 Developer Survey: technology (databases: PostgreSQL 55.6%, MySQL 40.5%, SQLite 37.5%, Microsoft SQL Server 30.1% of 26,083 respondents). Read on 4 November 2026.