These SQL interview questions work for data analysts, analytics engineers and backend developers, and for candidates brushing up before a technical screen. Most use one simple schema: customers(id, name, country) and orders(id, customer_id, amount, status, created_at). Each question shows the approach a strong candidate takes and, where useful, the mistake to watch for. Syntax follows standard SQL, with PostgreSQL notes where dialects differ. Pick 6 to 8, and for analyst roles, ask the candidate to sanity-check their own result.
What a strong SQL interview checks
- Joins and filters: choosing the right join and knowing what it does to row counts.
- NULL handling: three-valued logic and how aggregates treat missing values.
- Aggregation and windows: grouping, ranking, running totals and comparisons to prior rows.
- Performance: indexes, sargable filters and reading a query plan.
- Data quality: finding duplicates and checking that numbers are right before sharing them.
SQL interview questions: fundamentals
1. List every customer who has never placed an order.
What good looks like: LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL, or WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Strong candidates explain that an inner join would drop exactly the customers you want.
2. What's the difference between WHERE and HAVING?
What good looks like: WHERE filters rows before grouping, and HAVING filters groups after aggregation. "Customers with more than five paid orders" filters status = 'paid' in WHERE and COUNT(*) > 5 in HAVING. Filtering early in WHERE also means less work for the database.
3. Why does WHERE country = NULL return nothing?
What good looks like: any comparison with NULL is unknown, not true, so no rows pass. Use IS NULL or IS NOT NULL. Good candidates add that COUNT(*) counts rows while COUNT(country) skips NULLs, and that AVG ignores NULLs, which can quietly change a result.
4. Why can NOT IN return no rows at all?
What good looks like: if the subquery returns even one NULL, x NOT IN (...) can never be true, so the query returns nothing. NOT EXISTS doesn't have this problem and is the safer default.
Red flag: never having heard of this, for a senior analyst role.
5. Why can't you use a SELECT alias in the WHERE clause?
What good looks like: logically, the query runs FROM and JOIN, then WHERE, GROUP BY, HAVING, then SELECT, then DISTINCT, ORDER BY and LIMIT. The alias doesn't exist yet when WHERE runs. ORDER BY can use it because it runs later. Wrap the query in a CTE or subquery to filter on a computed column.
6. UNION or UNION ALL?
What good looks like: UNION removes duplicate rows, which costs a sort or hash step. UNION ALL keeps everything and is faster. Use UNION ALL unless you actually want duplicates removed, and know why you do.
Window functions and analysis
7. What's the difference between ROW_NUMBER, RANK and DENSE_RANK?
What good looks like: with scores 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 (ties broken arbitrarily unless you add a tiebreaker). RANK gives 1, 2, 2, 4. DENSE_RANK gives 1, 2, 2, 3. A strong candidate picks based on the business rule, such as "top 3 including ties."
8. Return each customer's most recent order.
What good looks like: in a CTE, add ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) AS rn, then select where rn = 1. The extra id DESC breaks ties on identical timestamps. In PostgreSQL, DISTINCT ON (customer_id) with a matching ORDER BY also works.
Red flag: joining on MAX(created_at) without handling two orders at the same time.
9. Show a running total of revenue by day.
What good looks like: aggregate to one row per day first, then SUM(daily_total) OVER (ORDER BY day). Strong candidates know the default window frame with ORDER BY is RANGE, which includes tied rows, and write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW when they want strictly row-by-row behavior.
10. Calculate month-over-month revenue growth.
What good looks like: group revenue by month (for example date_trunc('month', created_at) in PostgreSQL), then use LAG(revenue) OVER (ORDER BY month) to get the previous month and compute the percentage change. Guard against division by zero with NULLIF(previous, 0), and fill in months with no orders if they matter.
11. When would you use a CTE, and what is a recursive CTE for?
What good looks like: CTEs break a long query into named, readable steps. Recursive CTEs walk hierarchies like an org chart or category tree: an anchor query selects the root, and a recursive part joins back to the CTE until no new rows appear. Strong candidates add a depth limit or cycle check for messy data.
12. Find users who logged in on five or more consecutive days.
What good looks like: take distinct login dates per user, then subtract ROW_NUMBER() (as days) from each date. Consecutive dates share the same result, so group by user and that value, and keep groups with a count of five or more. This "gaps and islands" approach shows real comfort with window functions.
13. Show paid and refunded revenue per country as columns.
What good looks like: conditional aggregation, such as SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid, grouped by country. PostgreSQL also supports SUM(amount) FILTER (WHERE status = 'refunded'). This works in every major database, unlike vendor-specific PIVOT syntax.
Performance and data changes
14. How does a composite index on (customer_id, created_at) help, and when doesn't it?
What good looks like: it speeds up queries that filter on customer_id, or on customer_id plus a created_at range or sort. It doesn't help much for a filter on created_at alone, because the index is ordered by its leftmost column first. Every index also slows writes and uses storage, so add them for real query patterns.
15. Why is WHERE EXTRACT(YEAR FROM created_at) = 2026 slow, and how do you fix it?
What good looks like: wrapping the column in a function stops the database from using a plain index on it. Rewrite as a range: created_at >= '2026-01-01' AND created_at < '2027-01-01'. The same problem applies to leading wildcards like LIKE '%son'.
16. How do you read a query plan?
What good looks like: use EXPLAIN for the estimated plan and, in PostgreSQL, EXPLAIN ANALYZE to run the query and see real timings. Look for sequential scans on big tables, large gaps between estimated and actual row counts (often stale statistics), and the join method: nested loop, hash join or merge join. Fix the slowest step first.
17. Find and remove duplicate customers with the same email, keeping the oldest record.
What good looks like: number rows with ROW_NUMBER() OVER (PARTITION BY lower(email) ORDER BY id), then delete rows where the number is above 1. Strong candidates run it as a SELECT first, check the count, reassign orders that point at the duplicates, and add a unique constraint so it can't happen again.
18. Insert a row, or update it if it already exists.
What good looks like: in PostgreSQL, INSERT ... ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, which needs a unique constraint on the conflict column. MySQL uses ON DUPLICATE KEY UPDATE. Standard MERGE is available in SQL Server, Oracle and PostgreSQL 15 and later. A separate SELECT then INSERT has a race condition.
Behavioral (STAR)
19. Tell me about a time a number you reported turned out to be wrong.
What good looks like: how they found the error (a duplicate join, a time zone, a filter), how quickly they told the people using the number, the correction, and the check they added afterwards, such as a row-count test or a reconciliation against a source system.
Red flag: they quietly fixed it and hoped no one noticed.
20. Describe a slow report or query you made fast.
What good looks like: the before and after timing, how they found the bottleneck (the plan, not a guess), the change (an index, a rewrite, pre-aggregation), and any trade-off such as slower writes or a refresh delay.
A simple scoring rubric
Running this interview with AI
An AI interviewer can run the conceptual half of a SQL screen. With NTRVSTA, you can customize the interview per role with your own SQL questions, and the interviewer adapts in real time: a candidate who handles NULL logic and window functions moves on to query plans and upserts, while one who struggles gets simpler follow-ups. The hint system shows whether a candidate can recover with a small nudge, which matters for junior analysts.
You get a score breakdown and video snippets of key answers to review before deciding who moves forward. Our explainer on how AI interviews are scored covers what to check in any automated score, and the best AI interview software compares the options. For related roles, see data engineer, data scientist and backend developer interview questions.
Questions
Which SQL dialect should the interview use?
Use the one your team runs, and tell the candidate up front. Accept small syntax differences, like LIMIT versus TOP or DISTINCT ON being PostgreSQL only. What matters is the logic: correct joins, grouping, NULL handling and window functions.
Should candidates write SQL live or on paper?
Live against a small sample database is best, because running the query and checking the result is part of the skill. If that isn't possible, have them talk through the query and the expected output for two or three sample rows.
How do SQL questions differ for analysts and engineers?
Analysts should be strongest on joins, aggregation, window functions and checking their own numbers. Engineers should add indexes, query plans, upserts and transaction behavior. Both need the NULL and duplicate-row questions.
Want a first-round SQL screen that adapts to every candidate? Schedule a 20-minute demo, or try a sample interview yourself before you talk to us.
