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.
Key takeaways
- Almost every data, backend and analytics interview includes live SQL: joins,
GROUP BYand window functions. - Window functions (
ROW_NUMBER,RANK,LAG, running totals) separate mid-level candidates from juniors. - Expect questions on NULL behavior,
WHEREvsHAVINGand 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
Editorial estimate from typical analyst and backend interview loops.
Choosing the right tool
| You need to… | Use |
|---|---|
| One row per group | GROUP BY |
| Keep every row and add a group value | Window function with PARTITION BY |
| Top N per group | ROW_NUMBER() or DENSE_RANK() in a CTE, then filter |
| Compare with the previous row | LAG() / LEAD() |
| Rows without a match | LEFT JOIN ... IS NULL or NOT EXISTS |
| Step-by-step logic | CTEs (WITH) |
See also data analyst and data engineer interview questions.
Basics
1What is the logical order of execution of a SQL query?
JuniorFROM 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?
JuniorWHERE 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?
JuniorNULL 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?
JuniorDELETE 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?
JuniorUNION 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?
JuniorA 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.
JuniorINNER 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.
JuniorSELECT 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?
MidIf 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.
MidJoining 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?
MidA 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.
JuniorSELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
13What is a correlated subquery?
MidA 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?
MidA 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?
MidBoth 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?
MidUse 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?
MidA 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?
MidFor 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.
MidSELECT 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.
MidSELECT 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.
MidThey 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.
SeniorUse 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?
MidAn 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?
MidYes. 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?
MidFunctions 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?
SeniorRead 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?
SeniorA 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?
MidAtomicity: 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.
SeniorFrom 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?
MidNormalization 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?
Seniororders(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.
MidSELECT 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.
MidAggregate, 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.
JuniorGenerate 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.
MidRank 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
- Practice on real-looking schemasOrders, users, events and products cover most interview questions.
- Master window functions
ROW_NUMBER,RANK,LAG, running totals and the gaps-and-islands pattern. - Clarify definitions firstAsk what counts as active, retained or duplicate before writing the query.
- Check edge cases out loudNULLs, ties, empty groups and duplicate join keys.
- 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.