SQL Interview Questions for Engineers and Analysts (2026)

SQL interview questions test whether you can turn a business question into a correct, efficient query: joins, aggregation with GROUP BY and HAVING, subqueries and CTEs, NULL handling, and for engineers, indexes and transactions. The fastest way to prepare is to practice progressive questions on a single realistic schema, under time pressure, while checking edge cases out loud. This guide gives you 30 such questions with solutions, difficulty tags, and target solve times.

All solutions use PostgreSQL syntax. Where MySQL behaves differently in a way that could cost you points, it is called out.

Key Takeaways

• Three bugs cause a large share of wrong answers: a WHERE clause that silently turns a LEFT JOIN into an INNER JOIN, row fan-out that inflates SUMs, and NOT IN against a column containing NULLs.
• WHERE filters rows before grouping, and HAVING filters groups after. Expect a question on this distinction in most SQL screens.
• A CTE is a readability tool, not a performance trick. In PostgreSQL 12 and later, a single-use CTE is usually inlined and planned like a subquery.
• Wrapping an indexed column in a function, such as DATE(created_at), usually prevents a plain B-tree index from being used. Rewrite it as a range.
• Default isolation differs by engine: PostgreSQL defaults to Read Committed, MySQL InnoDB defaults to Repeatable Read.
• Pace yourself: about 4 minutes for easy, 8 for medium, and 12 to 15 for hard questions.

What Do SQL Interviews Test in 2026?

A SQL interview is a timed session where you write queries against a schema you have just been shown, usually in a shared editor or an online assessment platform. What gets emphasized depends on the role.

If you are aiming at data roles, pair this guide with the data engineer interview guide or the data scientist interview guide. Backend candidates should read the backend engineer interview guide for how SQL fits into the rest of the loop.

The Practice Schema

Every question below uses these three tables. Read them once, the way you would in a real round, and note which columns are nullable.

CREATE TABLE users (
  user_id     BIGINT PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  country     TEXT,
  referred_by BIGINT REFERENCES users(user_id),
  created_at  TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  order_id   BIGINT PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(user_id),
  amount     NUMERIC(10,2) NOT NULL,
  discount   NUMERIC(10,2),
  status     TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL
);

CREATE TABLE events (
  event_id    BIGINT PRIMARY KEY,
  user_id     BIGINT,
  event_type  TEXT NOT NULL,
  occurred_at TIMESTAMP NOT NULL
);

Things worth noticing before you write anything: country, referred_by, discount, and events.user_id can be NULL. status takes the values 'pending', 'paid', and 'refunded'. event_type is one of 'page_view', 'add_to_cart', or 'checkout'. Saying these observations out loud is free signal in a live round.

How to Use This Practice Set

Work through the questions in order with a timer. Write your answer before reading the solution, then compare edge-case handling, not just syntax.

If you finish all 30 within roughly four hours across a few sessions, you will have covered the core of most analyst and backend SQL screens. Talking through each query as you write it is a skill in itself, covered in our guide on how to think out loud in a coding interview.

SQL Joins Interview Questions and Edge Cases

Joins are where most wrong answers start. The concept is simple; the edge cases are not.

Q1 (Easy, 3 min). List every user with their number of orders, including users with zero orders.

SELECT u.user_id, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id;

Count o.order_id, not *. COUNT(*) would return 1 for users with no orders because the LEFT JOIN still produces one row for them.

Q2 (Easy, 4 min). Find users who have never placed an order.

SELECT u.user_id
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);

This is an anti-join. LEFT JOIN ... WHERE o.order_id IS NULL also works. Mention both and explain that NOT EXISTS is NULL-safe (see Q16).

Q3 (Medium, 6 min). Show every user with the total of their paid orders, keeping users who have no paid orders.

SELECT u.user_id, COALESCE(SUM(o.amount), 0) AS paid_total
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.user_id
 AND o.status = 'paid'
GROUP BY u.user_id;

The trap is writing WHERE o.status = 'paid'. For users without paid orders, o.status is NULL, the WHERE condition is not true, and those users disappear. Filters on the right-hand table of a LEFT JOIN belong in the ON clause.

Q4 (Medium, 5 min). Show each user's email next to the email of the user who referred them.

SELECT u.email, r.email AS referrer_email
FROM users u
LEFT JOIN users r ON r.user_id = u.referred_by;

A self-join with two aliases. Use LEFT JOIN so users with no referrer still appear.

Q5 (Medium, 8 min). Return each user's total paid revenue and total event count in one result.

The naive version joins orders and events directly to users, which multiplies rows: a user with 3 orders and 10 events produces 30 joined rows, and the revenue is inflated tenfold. Aggregate each side first.

WITH rev AS (
  SELECT user_id, SUM(amount) AS revenue
  FROM orders WHERE status = 'paid'
  GROUP BY user_id
),
ev AS (
  SELECT user_id, COUNT(*) AS event_count
  FROM events
  GROUP BY user_id
)
SELECT u.user_id,
       COALESCE(rev.revenue, 0) AS revenue,
       COALESCE(ev.event_count, 0) AS event_count
FROM users u
LEFT JOIN rev ON rev.user_id = u.user_id
LEFT JOIN ev  ON ev.user_id  = u.user_id;

Q6 (Easy, 2 min). Table A has 3 rows with key 1. Table B has 4 rows with key 1. How many rows does an inner join on key return?

Twelve. Every matching pair produces a row. Interviewers ask this to see if you understand fan-out before it bites you in Q5.

Aggregation, GROUP BY and HAVING

Aggregation questions check whether you know what each clause does and in what order the database applies them: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and LIMIT.

Q7 (Easy, 3 min). Paid revenue by country, highest first.

SELECT u.country, SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
ORDER BY revenue DESC;

Note that users with a NULL country form their own group. Say whether you want to keep it or label it with COALESCE(u.country, 'unknown').

Q8 (Easy, 4 min). Countries with more than 100 distinct paying users.

SELECT u.country, COUNT(DISTINCT o.user_id) AS payers
FROM orders o
JOIN users u ON u.user_id = o.user_id
WHERE o.status = 'paid'
GROUP BY u.country
HAVING COUNT(DISTINCT o.user_id) > 100;

Q9 (Medium, 6 min). What is the difference between WHERE and HAVING? Use it to find users whose paid orders in 2026 total more than 500.

WHERE filters individual rows before grouping. HAVING filters groups after aggregates are computed, so it is the only place you can filter on SUM or COUNT.

SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
  AND created_at >= '2026-01-01'
  AND created_at <  '2027-01-01'
GROUP BY user_id
HAVING SUM(amount) > 500;

Putting the date and status filters in WHERE is also faster, because fewer rows reach the grouping step.

Q10 (Medium, 8 min). Per month, show paid revenue, refunded revenue, and refund rate.

SELECT date_trunc('month', created_at) AS month,
       SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
       SUM(amount) FILTER (WHERE status = 'refunded') AS refunded,
       ROUND(
         SUM(amount) FILTER (WHERE status = 'refunded')
         / NULLIF(SUM(amount) FILTER (WHERE status IN ('paid','refunded')), 0),
         4) AS refund_rate
FROM orders
GROUP BY 1
ORDER BY 1;

This is conditional aggregation. FILTER is PostgreSQL syntax; in MySQL write SUM(CASE WHEN status = 'paid' THEN amount END). NULLIF(..., 0) prevents division by zero.

Q11 (Medium, 5 min). The email column is UNIQUE, yet support reports duplicate accounts. Find them.

SELECT LOWER(email) AS normalized, COUNT(*) AS accounts
FROM users
GROUP BY LOWER(email)
HAVING COUNT(*) > 1;

A UNIQUE constraint on TEXT is case-sensitive in PostgreSQL, so Ana@x.com and ana@x.com both pass. Grouping on an expression catches them.

Subqueries vs CTEs: When Should You Use Each?

A subquery is a query nested inside another statement. A CTE (common table expression) is a named subquery declared with WITH before the main query. They usually express the same logic; the choice is mostly about readability and reuse.

Since PostgreSQL 12, a non-recursive CTE referenced once is inlined unless you write AS MATERIALIZED. MySQL added CTE support in version 8.0.

Q12 (Easy, 5 min). Orders whose amount is above that user's own average order amount.

SELECT o.order_id, o.user_id, o.amount
FROM orders o
WHERE o.amount > (
  SELECT AVG(o2.amount) FROM orders o2 WHERE o2.user_id = o.user_id
);

A correlated subquery. A window-function version (AVG(amount) OVER (PARTITION BY user_id)) is often cleaner, and offering it shows range.

Q13 (Medium, 7 min). Users whose first order happened on the same calendar day they signed up.

WITH first_order AS (
  SELECT user_id, MIN(created_at) AS first_at
  FROM orders
  GROUP BY user_id
)
SELECT u.user_id
FROM users u
JOIN first_order f ON f.user_id = u.user_id
WHERE f.first_at::date = u.created_at::date;

Q14 (Medium, 5 min). Return the second-highest distinct order amount, or NULL if there is none.

SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);

The MAX wrapper returns NULL rather than an empty set when only one distinct amount exists. The ORDER BY ... OFFSET 1 approach needs DISTINCT and returns zero rows in that case.

Q15 (Hard, 12 min). For every user, compute the length of their referral chain back to a user who was not referred.

WITH RECURSIVE chain AS (
  SELECT user_id, referred_by, 0 AS depth, user_id AS origin
  FROM users
  UNION ALL
  SELECT c.user_id, r.referred_by, c.depth + 1, c.origin
  FROM chain c
  JOIN users r ON r.user_id = c.referred_by
)
SELECT origin AS user_id, MAX(depth) AS chain_length
FROM chain
GROUP BY origin;

Mention cycle risk. If bad data creates a referral loop, this never terminates. Add a depth cap such as WHERE c.depth < 50, or use PostgreSQL's CYCLE clause.

NULL Handling Gotchas Interviewers Love

NULL means "unknown," not zero or empty string. Any comparison with NULL using =, <>, <, or > returns UNKNOWN, and WHERE keeps only rows where the condition is TRUE.

Q16 (Easy, 4 min). Why does this query return zero rows, and how do you fix it?

SELECT user_id FROM users
WHERE user_id NOT IN (SELECT referred_by FROM users);

referred_by contains NULLs, so user_id NOT IN (..., NULL) is never TRUE. Fix it with NOT EXISTS:

SELECT u.user_id FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM users r WHERE r.referred_by = u.user_id
);

This is the single most common "gotcha" in SQL interview questions for experienced candidates.

Q17 (Easy, 3 min). Explain the difference between COUNT(*), COUNT(country), and COUNT(DISTINCT country) on users.

COUNT(*) counts rows. COUNT(country) counts rows where country is not NULL. COUNT(DISTINCT country) counts unique non-NULL values. On a table with NULL countries, all three can differ.

Q18 (Medium, 5 min). What is the average discount per order?

AVG(discount) ignores NULLs, so it gives the average among discounted orders only. AVG(COALESCE(discount, 0)) gives the average across all orders. Neither is wrong. Ask which one the business means, then write it. That clarifying question is the point of the exercise.

Q19 (Medium, 5 min). Find users whose country differs from their referrer's country, treating NULL vs a value as different.

SELECT u.user_id
FROM users u
JOIN users r ON r.user_id = u.referred_by
WHERE u.country IS DISTINCT FROM r.country;

u.country <> r.country silently drops every pair where one side is NULL. IS DISTINCT FROM is the null-safe comparison in PostgreSQL and the SQL standard. MySQL uses NOT (a <=> b). Also note that sort order differs: PostgreSQL places NULLs last in ascending order by default, while MySQL places them first.

Indexes and Query Performance

An index is a separate data structure, usually a B-tree, that lets the database find rows without scanning the whole table. Interviewers rarely ask you to define one. They ask why a specific query is slow.

Q20 (Medium, 6 min). What index best serves this query?

SELECT order_id, amount FROM orders
WHERE user_id = 42 AND created_at >= '2026-09-01'
ORDER BY created_at;

A composite index on (user_id, created_at). Equality columns go first, range and sort columns after. The index finds user 42's entries and reads them already sorted by date, so no separate sort is needed. Adding INCLUDE (amount) in PostgreSQL 11 and later allows an index-only scan.

Q21 (Medium, 4 min). Why is this slow even though created_at is indexed?

SELECT * FROM orders WHERE DATE(created_at) = '2026-09-01';

Applying a function to the column means a plain B-tree on created_at cannot be used for the lookup. Rewrite the condition as a range so it is sargable:

SELECT * FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02';

An expression index on (created_at::date) is the alternative when you cannot change the query.

Q22 (Medium, 4 min). Does an index on (status, created_at) help WHERE created_at > '2026-09-01'?

Generally no. B-tree indexes are searched from the leftmost column, so a query that skips status cannot seek directly. Some engines can do a skip scan when the leading column has very few distinct values (MySQL 8.0 has one, and newer PostgreSQL versions added one), but do not design around it. A strong answer explains the leftmost-prefix rule, then mentions skip scan as a caveat.

Q23 (Hard, 10 min). EXPLAIN shows a sequential scan on orders for WHERE status = 'paid', despite an index on status. Why?

If most orders are paid, the predicate is not selective. Reading the index and then fetching most of the table costs more than one sequential pass, so the planner correctly ignores the index. Other causes worth listing: stale statistics (run ANALYZE), a type mismatch between the column and the parameter, or a function wrapped around the column. Use EXPLAIN ANALYZE to compare estimated and actual row counts. Large gaps point to statistics problems. For the complexity intuition behind scans versus seeks, the Big O notation cheat sheet is a quick refresher.

Q24 (Medium, 6 min). The orders admin page uses LIMIT 50 OFFSET 200000 and is slow. Fix it.

OFFSET still reads and throws away 200,000 rows. Use keyset (cursor) pagination on an indexed, unique ordering:

SELECT order_id, amount, created_at
FROM orders
WHERE (created_at, order_id) < ($1, $2)
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

$1 and $2 are the values from the last row of the previous page. With an index on (created_at, order_id), each page costs about the same regardless of depth.

Transactions and Isolation Levels

A transaction is a group of statements that either all commit or all roll back. Backend and data engineering interviews use this section to test whether you can reason about concurrent writes.

Q25 (Easy, 3 min). Define ACID in one line each.

Atomicity: all statements in a transaction succeed or none do. Consistency: a transaction moves the database between states that satisfy its constraints. Isolation: concurrent transactions do not see each other's partial work, to the degree the isolation level promises. Durability: once committed, changes survive a crash.

Q26 (Medium, 6 min). Name the four standard isolation levels and the anomalies each allows.

Defaults matter. Per the PostgreSQL documentation, PostgreSQL defaults to Read Committed, treats Read Uncommitted as Read Committed, and its Repeatable Read also prevents phantom reads. Per the MySQL reference manual, InnoDB defaults to Repeatable Read. Quoting the right default for the engine in front of you is a strong signal.

Q27 (Hard, 10 min). Two workers process the same webhook and both try to mark order 991 as paid. How do you make sure it happens exactly once?

Make the state transition conditional and atomic:

UPDATE orders
SET status = 'paid'
WHERE order_id = 991 AND status = 'pending';

Then check the affected row count. The worker that gets 1 owns the side effects; the one that gets 0 skips them. The alternative is SELECT ... FOR UPDATE inside a transaction to lock the row before reading it. A plain read-then-write without either creates a lost update. Idempotent writes like this show up again in system design, especially in payment system design interviews.

Advanced SQL Interview Questions

These are the hard, multi-step questions that close out data analyst and data scientist rounds.

Q28 (Hard, 12 min). How many users went page_view, then add_to_cart, then checkout, in that order, within 7 days of their first page view?

WITH firsts AS (
  SELECT user_id,
         MIN(occurred_at) FILTER (WHERE event_type = 'page_view')   AS pv,
         MIN(occurred_at) FILTER (WHERE event_type = 'add_to_cart') AS atc,
         MIN(occurred_at) FILTER (WHERE event_type = 'checkout')    AS co
  FROM events
  WHERE user_id IS NOT NULL
  GROUP BY user_id
)
SELECT COUNT(*) FILTER (WHERE pv IS NOT NULL) AS viewed,
       COUNT(*) FILTER (WHERE atc > pv AND atc <= pv + INTERVAL '7 days') AS carted,
       COUNT(*) FILTER (WHERE atc > pv AND co > atc
                          AND co <= pv + INTERVAL '7 days') AS checked_out
FROM firsts;

State the simplification: this uses each user's first occurrence of each step. A stricter funnel would require the add_to_cart to follow a specific page view, which needs window functions.

Q29 (Hard, 12 min). Week-1 retention by signup week: the share of users with any event 1 to 7 days after signup.

SELECT date_trunc('week', u.created_at) AS cohort_week,
       COUNT(*) AS signups,
       ROUND(AVG(CASE WHEN EXISTS (
         SELECT 1 FROM events e
         WHERE e.user_id = u.user_id
           AND e.occurred_at >= u.created_at + INTERVAL '1 day'
           AND e.occurred_at <  u.created_at + INTERVAL '8 days'
       ) THEN 1.0 ELSE 0 END), 3) AS week1_retention
FROM users u
GROUP BY 1
ORDER BY 1;

Point out that the most recent cohorts have not had 8 days yet and should be excluded or flagged.

Q30 (Hard, 8 min). Each user's three largest paid orders.

SELECT user_id, order_id, amount
FROM (
  SELECT user_id, order_id, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, order_id) AS rn
  FROM orders
  WHERE status = 'paid'
) t
WHERE rn <= 3;

Top-N per group is the gateway to window functions. Know when to use RANK or DENSE_RANK instead of ROW_NUMBER for ties. Running totals, gaps and islands, sessionization, and the full set of ranking questions are in our dedicated SQL window functions interview guide.

How to Approach a SQL Coding Interview

Strong candidates follow the same short routine for every question, and it is visible to the interviewer.

1. Restate the question and confirm the grain of the output: one row per user, per month, or per order?
2. Ask about ambiguity: NULLs, ties, time zones, and which statuses count.
3. Name the tables and join keys before typing.
4. Build the query in steps, often as CTEs, and run each step if the editor allows.
5. Check edge cases: users with no rows, NULL columns, duplicates, empty results.
6. Mention performance in one sentence: what index you would want and why.

Most SQL screens run on the same platforms as general coding assessments, so the advice in our online assessment tips and technical phone screen guide applies directly. If your loop also includes a general coding round, the Python interview questions guide covers the language most data teams expect alongside SQL.