SQL interview questions for data analysts

By ·

Illustrated analyst joining two paper cut-out tables with overlapping circles on a desk

SQL interview questions for data analysts mostly test joins, CTEs and subqueries: INNER versus LEFT JOIN, finding rows with no match, why row counts grow after a join, filters in ON versus WHERE, readable CTE steps, correlated subqueries, and NOT IN versus NOT EXISTS with NULLs. Explaining the grain of your result matters as much as syntax.

Almost every data analyst interview, whether at a service company, a product startup or a remote role for a US or UK team, includes a SQL round. Most of it is not exotic. It is joins, subqueries and CTEs: can you combine tables without losing or duplicating rows, and can you break a business question into clear steps.

Below are 18 questions with short model answers, a worked example of the most common join trap, and a script for explaining your query out loud. We use two tables throughout: customers (customer_id, name, city) and orders (order_id, customer_id, order_date, amount, status). The syntax is standard SQL, with differences between PostgreSQL, MySQL and SQL Server noted where they matter.

What join questions do data analyst interviews ask?

1. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, with NULLs in the right table's columns where there is no match. Use LEFT JOIN when the left table is your full population, such as all customers, and the right table may be missing data.

2. How do you find customers who have never placed an order?

SELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL;

Or with NOT EXISTS: SELECT c.customer_id, c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id); Both are correct. Avoid NOT IN here unless the subquery column can never be NULL. If orders.customer_id contains even one NULL, NOT IN returns no rows at all, because comparing with NULL is never true.

3. Why did my row count go up after a join?

Because the join is one-to-many or many-to-many. If one customer has five orders, that customer appears five times. This is called fan-out, and it silently inflates SUM and COUNT. Before joining, check the grain of each table, meaning what one row represents, and confirm the key is unique on the side you expect. A quick test: SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1;

4. Does it matter whether a condition goes in ON or WHERE?

For an INNER JOIN, no, the results are the same. For a LEFT JOIN, yes, and it is one of the most tested traps in data analyst rounds. The worked example in the next section shows exactly why.

5. What is a self join? Give an example.

A self join joins a table to itself using two aliases. The classic case is employees and managers, where manager_id points to another employee_id: SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id; The LEFT JOIN keeps the CEO, who has no manager.

6. When would you use a FULL OUTER JOIN?

For reconciliation, such as comparing orders in the app database with payments in the finance system. A FULL OUTER JOIN keeps unmatched rows from both sides, so you see orders with no payment and payments with no order. MySQL does not support FULL OUTER JOIN, so there you combine a LEFT JOIN and a RIGHT JOIN with UNION.

7. Is a CROSS JOIN ever useful?

Yes, for building complete grids. To report sales for every store and every month, including months with no sales, cross join the stores table with a months table, LEFT JOIN actual sales onto that grid, and use COALESCE(SUM(amount), 0) so empty months show zero instead of disappearing.

Worked example: why does a WHERE filter break my LEFT JOIN?

Sample data: three customers, Priya (1), Arjun (2) and Sneha (3). Orders: 101 for Priya, delivered; 102 for Priya, cancelled; 103 for Arjun, delivered. Sneha has no orders. The business question is: list every customer with their delivered orders, and keep customers who have none.

Query A puts the status filter in ON: SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'delivered';

Query B puts it in WHERE: SELECT c.name, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'delivered';

name

Query A (filter in ON)

Query B (filter in WHERE)

Priya

101

101

Arjun

103

103

Sneha

NULL

row removed

Query B drops Sneha. The LEFT JOIN first gives her a row with NULL order columns, then WHERE o.status = 'delivered' evaluates to unknown for that NULL and removes the row. Filtering the right table in WHERE turns a LEFT JOIN into an inner join in effect. Put right-table conditions in ON when you want to keep unmatched left rows, and keep left-table conditions in WHERE.

What are CTEs, and when should you use them?

8. What is a CTE?

A common table expression is a named result set defined with WITH at the start of a query, which you can reference like a table in the rest of that statement. It exists only for that one query. Interviewers like CTEs because they turn a long query into readable, testable steps.

9. CTE, subquery or temp table: which do you choose?

A simple CTE and a subquery usually perform the same, because most optimisers treat the CTE like an inline view. Choose a CTE for readability or when you reference the same step more than once. Choose a temp table when you need the result across several statements, want to index it, or want to inspect it while debugging. Behaviour varies: PostgreSQL before version 12 always materialised CTEs, which could make them slower.

10. Find customers whose total spend is above the average customer's spend.

WITH customer_spend AS ( SELECT customer_id, SUM(amount) AS total_spend FROM orders WHERE status = 'delivered' GROUP BY customer_id ) SELECT customer_id, total_spend FROM customer_spend WHERE total_spend > (SELECT AVG(total_spend) FROM customer_spend);

Point out that you average the per-customer totals, not the raw order amounts. Those are different numbers, and mixing them up is a common mistake that interviewers watch for.

11. What is a recursive CTE?

A CTE that references itself, with an anchor query and a recursive part joined by UNION ALL. It is used for hierarchies such as an org chart, or for generating a series of dates. PostgreSQL and MySQL 8 need WITH RECURSIVE. SQL Server uses plain WITH and stops at 100 levels by default unless you set the MAXRECURSION option.

How do subqueries work, and which ones get asked?

12. What is a correlated subquery?

A subquery that refers to a column from the outer query, so it is logically evaluated once per outer row. For example, employees paid above their own department's average: SELECT e.name, e.dept, e.salary FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept = e.dept);

13. Should you use EXISTS or IN?

IN compares a value against the list a subquery returns. EXISTS checks whether the subquery returns any row at all. Modern optimisers often produce the same plan for both. The difference that matters in interviews is NULL handling with NOT IN, covered in question 2, which makes NOT EXISTS the safer default for finding missing matches.

14. Find the second highest salary without window functions.

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); This returns the second highest distinct salary, and NULL if everyone earns the same. Then mention that DENSE_RANK is cleaner when they ask for the Nth highest.

15. Which customers ordered in October but not in November?

SELECT DISTINCT o.customer_id FROM orders o WHERE o.order_date >= '2026-10-01' AND o.order_date < '2026-11-01' AND NOT EXISTS (SELECT 1 FROM orders n WHERE n.customer_id = o.customer_id AND n.order_date >= '2026-11-01' AND n.order_date < '2026-12-01');

Explain the half-open date ranges: using less than the first day of the next month works even when order_date includes a time. PostgreSQL and SQL Server also support EXCEPT, which gives the same result with two simple SELECTs.

Which quick fundamentals still trip people up?

  • 16. WHERE vs HAVING. WHERE filters rows before grouping. HAVING filters groups after aggregation, so HAVING SUM(amount) > 10000 works and WHERE SUM(amount) > 10000 does not.

  • **17. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column).** COUNT(*) counts rows, COUNT(column) skips NULLs, and COUNT(DISTINCT column) counts unique non-NULL values.

  • 18. UNION vs UNION ALL. UNION removes duplicates, which needs an extra sort or hash step. UNION ALL keeps every row and is faster. Use UNION ALL unless you actually need deduplication.

  • Bonus: logical order of execution. FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or TOP. This is why most databases will not let you use a SELECT alias inside WHERE.

How do you explain your SQL query in the interview?

Interviewers score your reasoning as much as your syntax. Before you type anything, say what one row of your output represents and which table is your full population. That one habit prevents most join mistakes.

  1. Restate the question and confirm details: date range, which statuses count, how to treat NULLs.

  2. State the grain of the result and pick your base table.

  3. Build the query in CTE steps and check the row count after each join.

  4. Walk through one or two sample rows to show the output is right.

I want one row per customer, so customers is my base table and I will LEFT JOIN orders. You only want delivered orders but still want customers with none, so the status filter goes in the ON clause, not WHERE. Then I group by customer and use COALESCE so customers without orders show zero. In my last role I found a dashboard overstating revenue because a join to a promotions table duplicated orders, so now I always check row counts after each join.

Rehearse these out loud with timed mock interviews, which score every answer. For more questions by role, see our data analyst interview questions and SQL developer interview questions. When you are ready to apply, Auto-Apply for data analyst jobs prepares applications that each wait for your approval.

If you have just started an analyst role and a query at work is giving odd numbers, SQL job support pairs AI with expert help. And to practise these exact questions with feedback, try InterviewPlan AI free with 5 live minutes and 100 AI credits, no card needed.

Frequently asked questions

What SQL topics are asked in a data analyst interview?

Expect joins of every type, GROUP BY with HAVING, subqueries, CTEs, CASE expressions, date filtering and window functions. Joins and aggregation usually take most of the time. Many companies also give one business question, such as finding inactive customers or monthly revenue, and watch how you break it into steps.

Is a CTE faster than a subquery?

Usually neither is faster. Most modern databases optimise a simple CTE and the equivalent subquery the same way. Choose a CTE for readability and reuse within one query. If you need the result across several statements, or want to index it, a temp table is the better choice.

Why is NOT IN dangerous with NULL values?

If the subquery returns even one NULL, a NOT IN comparison can never be true, so the whole query returns no rows. NOT EXISTS does not have this problem, because it only checks whether a matching row exists. Use NOT EXISTS or a LEFT JOIN with IS NULL for anti-joins.

How many SQL questions are asked in a data analyst interview?

It varies by company and round. A screening round may have two or three queries in 30 minutes, while a dedicated SQL round can have five or more of rising difficulty. Focus on getting each one correct and explaining it clearly rather than rushing through all of them.

Which SQL dialect should I practise for interviews?

Practise standard SQL, which works across PostgreSQL, MySQL, SQL Server and cloud warehouses. Learn the few differences that come up, such as LIMIT versus TOP, WITH RECURSIVE, and missing FULL OUTER JOIN in MySQL. If the job description names a database, practise a few queries in that one.

P.S.

Reading helps. Practising out loud helps more.

Start free with 5 live minutes and 100 AI credits for Resume AI, Job Search, Auto-Apply, mock interviews and Behavioral Coach.