SQL Interview Questions and Answers

35 SQL interview questions with answers and queries: joins, aggregation, window functions, CTEs, indexes, transactions, query optimization and classic SQL exercises.

Updated November 20, 2026 35 questions 8 min read

Key takeaways

  • Almost every data, backend and analytics interview includes live SQL: joins, GROUP BY and window functions.
  • Window functions (ROW_NUMBER, RANK, LAG, running totals) separate mid-level candidates from juniors.
  • Expect questions on NULL behavior, WHERE vs HAVING and duplicate rows from joins: common sources of wrong answers.
  • Senior roles add indexes, query plans, transactions and isolation levels.
Contents

SQL appears in almost every data, analytics and backend interview, usually as live queries on a simple schema. Below are 35 common questions grouped by topic and level, with answers and queries you can adapt.

What SQL interviews cover

How often each topic appears in SQL interviews

Editorial estimate from typical analyst and backend interview loops.

Joins and their pitfalls 5
Aggregation and GROUP BY / HAVING 5
Window functions 4
Subqueries and CTEs 4
NULL handling 3
Indexes and query plans 3
Transactions and isolation 2

Choosing the right tool

Which SQL feature to use
You need to…Use
One row per groupGROUP BY
Keep every row and add a group valueWindow function with PARTITION BY
Top N per groupROW_NUMBER() or DENSE_RANK() in a CTE, then filter
Compare with the previous rowLAG() / LEAD()
Rows without a matchLEFT JOIN ... IS NULL or NOT EXISTS
Step-by-step logicCTEs (WITH)

See also data analyst and data engineer interview questions.

Basics

1What is the logical order of execution of a SQL query?

Junior

FROM and JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This explains why you can’t use a SELECT alias in WHERE, but can usually use it in ORDER BY.

2What is the difference between WHERE and HAVING?

Junior

WHERE filters rows before grouping; HAVING filters groups after aggregation:

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

3How does NULL behave in comparisons and aggregates?

Junior

NULL means unknown, so NULL = NULL is not true; use IS NULL / IS NOT NULL. Aggregates like SUM and AVG ignore NULLs, COUNT(*) counts all rows, COUNT(col) counts non-NULL values. COALESCE(col, 0) replaces NULLs.

4DELETE vs TRUNCATE vs DROP?

Junior

DELETE removes selected rows, can use WHERE and fires triggers. TRUNCATE quickly removes all rows, usually with minimal logging. DROP removes the table itself, including its structure.

5UNION vs UNION ALL?

Junior

UNION combines results and removes duplicates, which requires extra work; UNION ALL keeps all rows and is faster. Use UNION ALL unless you need de-duplication.

6What are primary keys and foreign keys?

Junior

A primary key uniquely identifies each row and can’t be NULL. A foreign key references a primary key in another table and enforces referential integrity: you can’t insert an order for a customer that doesn’t exist.

Joins

7Explain INNER, LEFT, RIGHT and FULL joins.

Junior

INNER returns only matching rows. LEFT returns all rows from the left table, with NULLs where there is no match on the right. RIGHT is the mirror image. FULL OUTER returns all rows from both sides. CROSS returns every combination.

8Find customers who have never placed an order.

Junior
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 is an equally good alternative and often clearer.

9Why can a join produce more rows than expected?

Mid

If the join key isn’t unique on one side, each row matches several rows on the other side (a one-to-many or many-to-many join). Sums then count the same values multiple times. Aggregate before joining, or join on a unique key.

10What is a self join? Give an example.

Mid

Joining a table to itself, for example to find each employee’s manager:

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

11Why does a filter on the right table turn a LEFT JOIN into an INNER JOIN?

Mid

A condition like WHERE o.status = 'paid' removes rows where o is NULL. To keep unmatched left rows, put the condition in the ON clause: LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'.

Get SQL answer points in real time during your interview

Sottena listens to the interviewer, detects each question and shows short answer points in about a second. 150 free credits, no card required.

Aggregation and subqueries

12Find duplicate emails in a users table.

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

13What is a correlated subquery?

Mid

A subquery that references the outer query, so it is conceptually evaluated per row, for example customers whose order count is above average. They can be slow; often a join, a CTE or a window function is faster.

14What is a CTE, and when would you use one?

Mid

A Common Table Expression (WITH ... AS) names a subquery so you can build complex queries step by step. Recursive CTEs handle hierarchies such as org charts or category trees.

WITH monthly AS (
  SELECT date_trunc('month', created_at) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT * FROM monthly WHERE revenue > 10000;

15EXISTS vs IN?

Mid

Both check membership. EXISTS stops at the first match and handles NULLs safely; NOT IN returns no rows if the subquery contains a NULL, a classic bug. Prefer NOT EXISTS for anti-joins.

16How do you pivot rows into columns?

Mid

Use conditional aggregation, which works in every database:

SELECT product_id,
       SUM(CASE WHEN region = 'EU' THEN amount END) AS eu,
       SUM(CASE WHEN region = 'US' THEN amount END) AS us
FROM sales GROUP BY product_id;

Window functions

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

Mid

A window function computes a value across related rows without collapsing them. GROUP BY returns one row per group; SUM(amount) OVER (PARTITION BY customer_id) keeps every row and adds the group total next to it.

18ROW_NUMBER vs RANK vs DENSE_RANK?

Mid

For values 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4; RANK gives 1, 2, 2, 4 (gaps after ties); DENSE_RANK gives 1, 2, 2, 3 (no gaps).

19Find the second-highest salary in each department.

Mid
SELECT department_id, name, salary
FROM (
  SELECT *, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

DENSE_RANK handles ties so that two people with the top salary don’t hide the second-highest.

20Calculate a running total.

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

21How do LAG and LEAD work? Compute month-over-month growth.

Mid

They read a value from a previous or next row in the window:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS pct
FROM monthly_revenue;

22Find users who logged in on three consecutive days.

Senior

Use the “gaps and islands” trick: subtracting the row number from the date gives a constant for consecutive days.

WITH d AS (
  SELECT DISTINCT user_id, login_date FROM logins
), g AS (
  SELECT user_id, login_date,
         login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) * INTERVAL '1 day' AS grp
  FROM d
)
SELECT user_id FROM g GROUP BY user_id, grp HAVING COUNT(*) >= 3;

Indexes and performance

23What is an index, and what are the trade-offs?

Mid

An index (usually a B-tree) lets the database find rows without scanning the whole table. It speeds up reads on the indexed columns but takes storage and slows down inserts and updates, because the index must be maintained.

24How does a composite index work? Does column order matter?

Mid

Yes. An index on (customer_id, created_at) helps queries filtering by customer_id, or by both columns, but not queries filtering only by created_at (the leftmost prefix rule). Put equality filters first and range filters last.

25Why might a query not use an index?

Mid

Functions on the column (WHERE LOWER(email) = ...), implicit type casts, leading wildcards (LIKE '%abc'), low selectivity, or outdated statistics. The planner may also decide a full scan is cheaper for large result sets.

26How do you optimize a slow query?

Senior

Read the plan with EXPLAIN (or EXPLAIN ANALYZE): look for sequential scans on large tables, bad row estimates and expensive sorts or joins. Then add or adjust indexes, rewrite to filter earlier, avoid SELECT *, aggregate before joining, and check that statistics are up to date.

27Clustered vs non-clustered index?

Senior

A clustered index defines the physical order of rows (one per table, often the primary key in SQL Server and MySQL InnoDB). A non-clustered index is a separate structure pointing to rows. PostgreSQL tables are heaps; it has no persistent clustered index in the same sense.

Transactions and design

28What does ACID mean?

Mid

Atomicity: all or nothing. Consistency: constraints hold before and after. Isolation: concurrent transactions don’t see each other’s partial work. Durability: committed data survives crashes.

29Explain transaction isolation levels.

Senior

From weakest to strongest: Read Uncommitted (dirty reads possible), Read Committed (no dirty reads), Repeatable Read (no non-repeatable reads), Serializable (as if transactions ran one by one). Higher isolation prevents anomalies at the cost of more locking or retries.

30What is normalization? When would you denormalize?

Mid

Normalization splits data into related tables to remove duplication and update anomalies (1NF, 2NF, 3NF). Denormalization adds controlled duplication to make reads faster, which is common in analytics and reporting tables.

31How would you design tables for orders and order items?

Senior

orders(id, customer_id, status, created_at) and order_items(order_id, product_id, quantity, unit_price) with a composite primary key or a surrogate key. Store the price at purchase time on the item, add foreign keys and indexes on customer_id and created_at, and use a transaction when creating an order with its items.

Practical exercises

32Calculate 7-day retention for new users.

Mid
SELECT COUNT(DISTINCT a.user_id) * 1.0 / COUNT(DISTINCT u.id) AS d7_retention
FROM users u
LEFT JOIN events a
  ON a.user_id = u.id
 AND a.event_date = u.signup_date + INTERVAL '7 day'
WHERE u.signup_date BETWEEN '2026-10-01' AND '2026-10-31';

Clarify the definition first: exactly day 7, or any day in the first week?

33Find the top 3 products by revenue in each category.

Mid

Aggregate, rank inside each category, then filter:

WITH r AS (
  SELECT category, product_id, SUM(quantity * unit_price) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(quantity * unit_price) DESC) AS rn
  FROM order_items JOIN products USING (product_id)
  GROUP BY category, product_id
)
SELECT category, product_id, revenue FROM r WHERE rn <= 3;

34Count orders per day, including days with no orders.

Junior

Generate a calendar and left join to it, so empty days appear with zero. In PostgreSQL: generate_series('2026-11-01'::date, '2026-11-30', '1 day'); other databases use a calendar table.

35Remove duplicate rows but keep one.

Mid

Rank rows within each duplicate group and delete everything after the first:

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

How to prepare

  1. Practice on real-looking schemasOrders, users, events and products cover most interview questions.
  2. Master window functionsROW_NUMBER, RANK, LAG, running totals and the gaps-and-islands pattern.
  3. Clarify definitions firstAsk what counts as active, retained or duplicate before writing the query.
  4. Check edge cases out loudNULLs, ties, empty groups and duplicate join keys.
  5. Know one dialect wellPostgreSQL, MySQL, BigQuery and Snowflake differ in date functions; say which one you use.

Get answer points in real time during your interview

Sottena listens to the interviewer, detects each question and shows short answer points in about a second. 150 free credits, no card required.

Related interview questions