SQL Interview Questions for Data Analysts, Answered
· 7 min read
Most SQL interview questions for data analyst roles test the same eight ideas: filtering with WHERE and HAVING, inner vs left joins, counting correctly, ranking within groups, running totals, dates, NULLs, and spotting a query that runs but returns the wrong answer. Here is a typical one, answered:
-- Which regions have more than 400 in completed revenue?
SELECT c.region, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY c.region
HAVING SUM(o.amount) > 400;Below are eight common questions with short answers, each run on the same two small tables so you can check every result yourself. Interviewers care about the reasoning as much as the query, so each answer says what to say out loud.
The tables used in every answer
| customer_id | customer_name | region |
|---|---|---|
| 1 | Acme Dental | North |
| 2 | Birch Cafe | South |
| 3 | Cobalt Labs | North |
| 4 | Dune Studio | West |
| order_id | customer_id | order_date | status | amount |
|---|---|---|---|---|
| 101 | 1 | 2025-01-05 | completed | 300 |
| 102 | 1 | 2025-01-19 | completed | 150 |
| 103 | 2 | 2025-01-22 | cancelled | 500 |
| 104 | 2 | 2025-02-03 | completed | 250 |
| 105 | 3 | 2025-02-14 | completed | 300 |
| 106 | 3 | 2025-02-20 | NULL | 90 |
| 107 | 1 | 2025-03-01 | completed | 400 |
| 108 | 2 | 2025-03-09 | completed | 120 |
SQL query interview questions
1. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after it, so only HAVING can test SUM or COUNT. The query at the top uses both and returns:
| region | revenue |
|---|---|
| North | 1150 |
South's completed orders add up to 370, so it fails the HAVING test. The full explanation is in HAVING vs WHERE.
2. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
SELECT COUNT(*) AS all_orders,
COUNT(status) AS orders_with_status,
COUNT(DISTINCT customer_id) AS customers_who_ordered
FROM orders;| all_orders | orders_with_status | customers_who_ordered |
|---|---|---|
| 8 | 7 | 3 |
COUNT(*) counts rows, COUNT(status) skips the NULL on order 106, and COUNT(DISTINCT ...) counts each customer once. More in COUNT DISTINCT.
3. How do you find the second highest value?
Interviewers like this one because there are several answers. The cleanest handles ties:
SELECT amount
FROM (
SELECT amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
FROM orders
) AS ranked
WHERE rnk = 2
LIMIT 1;| amount |
|---|
| 400 |
The classic alternative without window functions also returns 400:
SELECT MAX(amount) AS second_highest
FROM orders
WHERE amount < (SELECT MAX(amount) FROM orders);Say why you used DENSE_RANK and not ROW_NUMBER: if two orders tied for the highest amount, ROW_NUMBER would return the tied value as "second".
4. How do you find duplicates?
Group by the column that should be unique and keep groups with more than one row. Here, customers with more than one order:
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY customer_id;| customer_id | orders |
|---|---|
| 1 | 3 |
| 2 | 3 |
| 3 | 2 |
SQL join interview questions
5. Which customers have never placed an order?
This tests whether you know a left join keeps unmatched rows as NULL:
SELECT c.customer_name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;| customer_name |
|---|
| Dune Studio |
A common follow-up: "now only count completed orders". The status condition then has to go in the ON clause, not in WHERE. SQL LEFT JOIN walks through why.
6. What is the biggest completed order for each customer?
This is the "top N per group" question, and it needs a window function with PARTITION BY:
SELECT customer_name, order_id, amount
FROM (
SELECT c.customer_name, o.order_id, o.amount,
RANK() OVER (PARTITION BY o.customer_id ORDER BY o.amount DESC) AS rnk
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
) AS ranked
WHERE rnk = 1
ORDER BY customer_name;| customer_name | order_id | amount |
|---|---|---|
| Acme Dental | 107 | 400 |
| Birch Cafe | 104 | 250 |
| Cobalt Labs | 105 | 300 |
Mention that RANK keeps ties and ROW_NUMBER would pick one. See RANK OVER PARTITION BY.
7. Show completed revenue by month with a running total
SELECT CAST(DATE_TRUNC('month', order_date) AS DATE) AS month_start,
SUM(amount) AS revenue,
SUM(SUM(amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS running_total
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;| month_start | revenue | running_total |
|---|---|---|
| 2025-01-01 | 450 | 450 |
| 2025-02-01 | 550 | 1000 |
| 2025-03-01 | 520 | 1520 |
Grouping by DATE_TRUNC keeps the year attached to each month. DATE_TRUNC exists in Postgres, DuckDB and Snowflake; in MySQL or SQL Server, say which equivalent you would use. Details in SQL running total.
8. How many orders are not cancelled?
The trap is the NULL status on order 106:
SELECT COUNT(*) AS not_cancelled
FROM orders
WHERE status <> 'cancelled';| not_cancelled |
|---|
| 6 |
NULL <> 'cancelled' is not true, it is unknown, so order 106 is dropped. If an order with no status counts as "not cancelled", write WHERE status <> 'cancelled' OR status IS NULL, which returns 7. In Postgres and DuckDB, status IS DISTINCT FROM 'cancelled' does the same. The best answer asks the interviewer what a missing status means.
Spot the bug: the questions that separate candidates
More and more interviews hand you a query, often one an AI assistant wrote, and ask what is wrong with it. These are the hardest SQL practice questions, because the query runs. Here is a typical one: "This should return regions with over 400 in completed revenue."
SELECT c.region, SUM(o.amount) AS revenue
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
GROUP BY c.region
HAVING SUM(o.amount) > 400
ORDER BY c.region;| region | revenue |
|---|---|
| North | 1240 |
| South | 870 |
The status filter is missing, so cancelled and unlabelled orders are included. North is inflated by 90 and South by 500, which is enough to push South over the threshold. Nothing about the output looks broken, and that is the point.
A method that works on any query, yours or an AI's:
- Read the question for hidden filters. "Completed", "2025", "active" all need a
WHERE. - Check every join key. Does
a.x = b.ylink the same thing on both sides? - Ask what one row means before each
SUM,COUNTorAVG: an order, an order line, a customer? - Check filters on aggregates are in
HAVING, and filters on a left-joined table are inON. - Check window functions have the right
PARTITION BYand sort direction. - Sanity-check the result: row counts, a total you can work out by hand, a group that should exist.
If you want to rehearse exactly this, every audit exercise on Practisheet hands you an AI-written query with one such mistake to find and fix.
Common mistakes
- Writing before asking. Clarify ties,
NULLs and what "revenue" means first. Interviewers reward the questions. - Silent
SELECT *. Name your columns; it shows you know what the output should look like. - Trusting the first result. Always say how you would check it: "North should be 1,150, let me verify one row by hand."
- Over-trusting AI help in take-home tests. AI tools write plausible SQL quickly, but the bugs above (a missing filter, a missing partition, a row-level condition that should be in
HAVING) are exactly the ones they make. Run the six checks before you submit.
FAQ
What SQL topics are asked in a data analyst interview?
Joins (inner and left), GROUP BY with HAVING, aggregate functions, CASE WHEN, window functions (RANK, ROW_NUMBER, running totals), date functions and NULL handling. Database design and stored procedures come up far less often for analyst roles.
How should I practise SQL for an interview?
Practise on one realistic dataset rather than disconnected puzzles, so you get used to joins between real tables. Time yourself, and for every answer write down how you would check it.
Do I need to know a specific SQL dialect?
Usually not. Standard SQL covers most questions. Know that date functions differ (DATE_TRUNC vs DATEPART and friends) and say which database you are assuming.
Are SQL interview questions hard for beginners?
The concepts are learnable in a few weeks. What trips people up is not syntax but correctness: a query that runs and returns the wrong number. Practise checking results, and the rest follows.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Revenue that includes refundsAudit
- Customers with no completed ordersAudit
- The AI found no big spendersAudit
- Best seller in each categoryAudit