# SQL Interview Questions (2026) with Answers > 30 SQL interview questions from junior to senior, each with what a strong answer shows. Practise, then prove the skill with evidence. Source: https://getlemma.io/skills/sql/interview-questions SQL interviews test whether you can turn a business question into a correct query, then explain why it is correct and how it performs. The questions below run from junior to senior. Each one notes what a strong answer covers and the mistake that sinks a weak one. Use them to check your own answers first. [How Lemma tests SQL](https://getlemma.io/skills/sql) ## Junior SQL interview questions 1. ### 1.What is the difference between an INNER JOIN and a LEFT JOIN? What a strong answer shows An INNER JOIN keeps only rows with a match on both sides. A LEFT JOIN keeps every row from the left table and fills the right side with NULLs where nothing matches. A strong answer adds that a LEFT JOIN can still multiply left rows when the right side has several matches. 2. ### 2.What is the difference between WHERE and HAVING? What a strong answer shows WHERE filters individual rows before grouping, so it cannot reference aggregates. HAVING filters groups after aggregation, so it can test conditions like COUNT(\*) > 1. A weak answer treats them as interchangeable and puts row-level filters in HAVING, which works but groups rows it could have discarded first. 3. ### 3.Why does WHERE column = NULL return no rows? ``` -- Always empty SELECT * FROM users WHERE deleted_at = NULL; -- Correct SELECT * FROM users WHERE deleted_at IS NULL; ``` What a strong answer shows Any comparison with NULL yields UNKNOWN rather than TRUE, and WHERE keeps only rows where the condition is TRUE. You test with IS NULL or IS NOT NULL instead. A strong answer extends this to three-valued logic: NOT (x = NULL) is also UNKNOWN, so negating the condition does not help. 4. ### 4.What is the difference between COUNT(\*), COUNT(column) and COUNT(DISTINCT column)? What a strong answer shows COUNT(\*) counts rows, COUNT(column) counts rows where that column is not NULL, and COUNT(DISTINCT column) counts unique non-NULL values. A strong answer notes that SUM and AVG also skip NULLs, so an average can differ from a total divided by the row count. 5. ### 5.When would you use UNION ALL instead of UNION? What a strong answer shows UNION removes duplicate rows, which costs a sort or hash over the combined result. UNION ALL keeps every row and is cheaper, so use it when inputs cannot overlap. A weak answer defaults to UNION and pays for deduplication it does not need. 6. ### 6.What are primary keys and foreign keys, and why do they matter? What a strong answer shows A primary key uniquely identifies each row and cannot be NULL. A foreign key requires the value to exist in the referenced table, so the database rejects orphaned rows. A strong answer adds that constraints catch application bugs, and that foreign key columns usually need their own index. 7. ### 7.In what order does the database logically process a SELECT statement? What a strong answer shows Logically it runs FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That explains why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY. A strong answer separates this logical order from the physical plan, which the optimiser is free to rearrange. 8. ### 8.How would you find duplicate email addresses in a users table? ``` SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS n FROM users GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1; ``` What a strong answer shows Group by the column and keep groups with more than one row. A strong answer first asks what counts as a duplicate, such as differences in case or whitespace. It then shows how to return the full rows, by joining back or with a window function. 9. ### 9.How would you list each employee next to their manager's name? What a strong answer shows Join the employees table to itself, using one alias for the employee and another for the manager. A strong answer uses a LEFT JOIN so the top-level employee with no manager still appears. An INNER JOIN would silently drop them. 10. ### 10.What is normalisation, and what problem does it solve? What a strong answer shows Normalisation stores each fact once, which prevents update, insert and delete anomalies. A strong answer explains the first three normal forms plainly: atomic values, no partial dependency on a composite key, no dependency between non-key columns. It also admits that read-heavy systems sometimes denormalise on purpose. ## Mid-level SQL interview questions 1. ### 11.What is the difference between a window function and GROUP BY? What a strong answer shows GROUP BY collapses rows into one row per group, while a window function computes across related rows but keeps every input row. That lets you show a row next to its group total or rank. A weak answer tries to filter a window result in WHERE, which runs before windows are computed. 2. ### 12.How do ROW\_NUMBER, RANK and DENSE\_RANK differ? What a strong answer shows ROW\_NUMBER gives every row a unique number, breaking ties arbitrarily unless the ORDER BY is fully deterministic. RANK gives ties the same number and leaves gaps after them; DENSE\_RANK gives ties the same number without gaps. A strong answer picks the right one for the question, for example DENSE\_RANK for the second-highest distinct salary. 3. ### 13.How would you return the three highest-paid employees in each department? ``` SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees e ) ranked WHERE rn <= 3; ``` What a strong answer shows Rank rows within each department in a subquery or CTE, then filter on the rank outside it. A strong answer states how ties are handled, choosing ROW\_NUMBER for exactly three rows or RANK to include everyone tied at third place. 4. ### 14.How would you calculate a running total, and what is a window frame? ``` SELECT order_date, amount, SUM(amount) OVER ( ORDER BY order_date, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM orders; ``` What a strong answer shows Use SUM over a window ordered by date; the frame defines which rows feed each calculation. With ORDER BY, the default frame is RANGE up to the current row, so rows sharing a date are summed together. A strong answer knows this and uses ROWS when each row should add one at a time. 5. ### 15.How would you show each day's revenue next to the change from the previous day? ``` SELECT day, revenue, revenue - LAG(revenue) OVER (ORDER BY day) AS change FROM daily_revenue; ``` What a strong answer shows Use LAG to read the previous row's value in the same ordered window, then subtract. A strong answer points out that missing days make LAG compare against the last recorded day, not yesterday. When that matters, it fills gaps with a calendar table or generated date series. 6. ### 16.When would you use a CTE instead of a subquery? What a strong answer shows A CTE names an intermediate result, which makes multi-step logic readable, and a recursive CTE handles hierarchies such as org charts. A strong answer knows CTEs are not automatically faster. PostgreSQL 12 and later usually inlines them, while older versions always materialised them and could miss indexes. 7. ### 17.Why might a LEFT JOIN behave like an INNER JOIN? ``` -- Drops customers with no paid orders SELECT c.id, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid'; -- Keeps them, with NULL order columns SELECT c.id, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'; ``` What a strong answer shows A WHERE condition on a column from the right table rejects the NULL rows the LEFT JOIN produced, so unmatched left rows disappear. Move the condition into the ON clause to keep them. Weak answers cannot spot this in a query, and it is one of the most common causes of quietly wrong reports. 8. ### 18.Why can NOT IN return no rows when you expect some, and what would you use instead? ``` -- Empty if any orders.customer_id is NULL SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders); -- Safe SELECT c.id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); ``` What a strong answer shows If the subquery returns any NULL, x NOT IN (...) evaluates to UNKNOWN for every row, so the result is empty. NOT EXISTS has no such trap and is usually planned as an anti-join. A strong answer reaches for NOT EXISTS or a LEFT JOIN with an IS NULL check by habit. 9. ### 19.How would you count paid and refunded orders per month in a single query? ``` SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders, SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid, COUNT(*) FILTER (WHERE status = 'refunded') AS refunded FROM orders GROUP BY 1; ``` What a strong answer shows Group by month and aggregate a CASE expression for each status, or use the FILTER clause where the database supports it. A strong answer avoids running one query per status and joining the results, and checks that the CASE returns 0 or NULL deliberately. 10. ### 20.A report joins customers to orders and to support tickets, and revenue comes out too high. Why? What a strong answer shows Joining two one-to-many tables to the same parent multiplies rows: each order repeats once per ticket. The fix is to aggregate each child table to one row per customer first, then join the results. A strong answer checks row counts before and after each join rather than patching the symptom with DISTINCT. 11. ### 21.How does an index speed up a query, and when will the database ignore it? What a strong answer shows A B-tree index keeps keys sorted, so the database can seek to a value or range instead of scanning the table. The planner skips it when the filter matches many rows, the column is wrapped in a function, or statistics are stale. A strong answer mentions that every index slows down writes. 12. ### 22.You have an index on (customer\_id, created\_at). Which queries can use it? What a strong answer shows Queries filtering on customer\_id, alone or with a condition on created\_at, can use it, and get one customer's rows already sorted by date. A query filtering only on created\_at generally cannot seek into it, because the leading column is missing. A strong answer puts equality filters first and range filters last. ## Senior SQL interview questions 1. ### 23.Walk me through how you read a query plan for a slow query. What a strong answer shows Run EXPLAIN ANALYZE, or the equivalent for your database, and read the plan from the innermost nodes outwards. Compare estimated with actual row counts, since a large gap usually means stale statistics or correlated columns, and that misleads join choices. A strong answer names the expensive node, its scan or join type, and a concrete fix rather than adding indexes at random. 2. ### 24.This query has an index on created\_at but still scans the table. What would you change? ``` -- Cannot seek on the index SELECT * FROM events WHERE DATE(created_at) = '2026-10-01'; -- Can SELECT * FROM events WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'; ``` What a strong answer shows Wrapping the indexed column in a function or cast makes the predicate non-sargable, so the index cannot be used for a seek. Rewrite it as a range on the raw column, or add an expression index if the function is unavoidable. A strong answer also checks for implicit type casts and leading wildcards in LIKE, which cause the same problem. 3. ### 25.Explain transaction isolation levels and the anomalies each one prevents. What a strong answer shows Read committed prevents dirty reads, repeatable read also prevents non-repeatable reads, and serializable behaves as if transactions ran one at a time. A strong answer knows defaults differ by database and that snapshot isolation still allows write skew. It also notes that serializable transactions can fail and must be retried. 4. ### 26.Two requests try to book the last seat at the same time. How do you stop both succeeding? ``` UPDATE flights SET seats_left = seats_left - 1 WHERE id = 42 AND seats_left > 0; -- 0 rows updated means the seat was already taken ``` What a strong answer shows Make the check and the write atomic: a conditional UPDATE that checks remaining seats, SELECT ... FOR UPDATE inside a transaction, or a unique constraint that rejects the second booking. A strong answer weighs pessimistic locking against optimistic version checks and explains how deadlocks arise when transactions lock rows in different orders. 5. ### 27.How would you model sales data for analytics, and how would you handle a customer who changes region? What a strong answer shows Use a star schema: a fact table at a stated grain, such as one row per order line, joined to dimension tables. For a changing region, a type 2 slowly changing dimension adds a new row with validity dates, so past sales keep their original region. A weak answer skips the grain, which is where double counting starts. 6. ### 28.How would you find each user's longest streak of consecutive active days? ``` SELECT user_id, MIN(d) AS start_day, MAX(d) AS end_day, COUNT(*) AS days FROM ( SELECT user_id, d, d - CAST(ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY d) AS int) AS grp FROM (SELECT DISTINCT user_id, activity_date AS d FROM activity) a ) t GROUP BY user_id, grp; ``` What a strong answer shows This is a gaps-and-islands problem. Subtract a row number from each date: consecutive dates share the same result, which becomes a group key. A strong answer deduplicates dates first, because two events on one day would otherwise break the arithmetic. 7. ### 29.Why does OFFSET pagination get slow on deep pages, and what would you use instead? ``` SELECT id, created_at, title FROM posts WHERE (created_at, id) < (:last_created_at, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20; ``` What a strong answer shows OFFSET still reads and discards every skipped row, so deep pages cost far more than the first, and rows shift when data changes. Keyset pagination filters on the last seen sort key with a unique tie-breaker, served directly by an index. A strong answer accepts that you cannot jump to an arbitrary page number. 8. ### 30.When would adding an index make a system worse? What a strong answer shows Every index is updated on each insert, update and delete, takes storage and memory, and competes for cache. On write-heavy tables, or for columns rarely filtered on, the cost outweighs the gain. A strong answer checks index usage statistics, drops unused or duplicate indexes, and builds new ones without blocking writes. ![Painting of an olive grove in soft sunlight with hills beyond](https://cdn.sanity.io/images/islu4peo/production/613eb3f93566423c9adf45246f2cf1a61437c04c-1680x1120.jpg?w=1080&fm=webp&q=68&fit=max) ## Prove it instead of claiming it. Answers show you can talk about SQL. A dated test result and live sessions on a Skills Passport show you can do it. Lemma is in early access. ## SQL interviews: common questions ### How should I prepare for a SQL interview? Practise writing queries by hand against a real schema, not only reading solutions. Cover joins, aggregation, NULL behaviour and window functions until you can explain each result row by row. For senior roles, add query plans, indexing and transactions, and practise talking through a slow query out loud. ### What SQL questions come up in data analyst interviews? Analyst interviews lean on business questions: retention, revenue by period, top items per group, and change over time. Expect joins, GROUP BY with HAVING, conditional aggregation and window functions such as ROW\_NUMBER and LAG. Interviewers also watch whether you check for duplicates and NULLs before trusting a number. ### How can I check my SQL level before applying for jobs? Work through the questions on this page without looking at the guidance, then compare your answers. Lemma's adaptive skills test for SQL asks 15 to 25 questions that adjust to your answers and records a dated level. Either way, note the topics you hesitated on and practise those first. ## More interview questions [Browse the skills directory](https://getlemma.io/skills) - [Product management interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/product-management/interview-questions) - [Python interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/python/interview-questions) - [React interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/react/interview-questions) - [System design interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/system-design/interview-questions) - [JavaScript interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/javascript/interview-questions) - [AWS interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/aws/interview-questions) - [Node.js interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/nodejs/interview-questions) - [TypeScript interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/typescript/interview-questions) - [Docker interview questionsJunior to senior, with what a strong answer shows](https://getlemma.io/skills/docker/interview-questions) Practising answers is half of it; the other half is showing the skill. See [how Lemma tests SQL](https://getlemma.io/skills/sql), how [adaptive skills tests](https://getlemma.io/product/assessments) work, or [how interviewers assess technical skills](https://getlemma.io/blog/assess-technical-skills-in-interview).