SQL Interview Questions for Data Analyst Roles (With Answers)

Twenty-five SQL interview questions asked in data analyst and data science interviews, with clear answers — joins, aggregation, window functions, NULL behaviour, query reasoning and the live query round.

  SAP Training   Oct 1, 2026   73  Add to Reading List
Quick Answer: SQL is usually the first technical filter in data analyst and data science interviews, and it goes well past SELECT statements. Expect joins and their edge cases, aggregation with NULLs, window functions, CTEs, and scenario questions where a query returns the wrong number of rows. Below are twenty-five questions with answers, plus how the live query round actually works.

Candidates consistently underprepare for SQL because courses treat it as a preliminary topic and spend their time on machine learning instead. Interviewers do the opposite: SQL is where they start, because it is the tool the job actually uses every day and because it is very hard to fake. Someone who has only read about joins is identified within two questions.

Fundamentals and Joins

1. What is the order in which a SQL query is executed?

Not the order you write it. The engine resolves FROM and joins first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY and finally LIMIT. This explains several things beginners find confusing, including why you cannot use a column alias defined in SELECT inside a WHERE clause.

2. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. So you filter on a raw column with WHERE and on an aggregate such as a count or sum with HAVING. Using both in one query is common and perfectly correct.

3. Explain the different types of JOIN.

INNER JOIN returns only rows matching in both tables. LEFT JOIN returns everything from the left table plus matches from the right, with NULLs where there is no match. RIGHT JOIN is the mirror. FULL OUTER JOIN returns everything from both sides. CROSS JOIN returns every combination, which is occasionally useful and more often an accident.

4. What is a self join and when would you use one?

A table joined to itself, using aliases to distinguish the two copies. The classic use is a hierarchy — an employee table where each row holds a manager id that points at another row in the same table — so you join employees to managers to show both names in one result.

5. What is the difference between UNION and UNION ALL?

UNION combines two result sets and removes duplicates, which requires a sort and is therefore slower. UNION ALL keeps everything including duplicates. If you know there are no duplicates, UNION ALL is the better choice on large data.

6. What does COUNT(*) do differently from COUNT(column)?

COUNT(*) counts rows, including rows where every column is NULL. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values. Interviewers use this to check whether you understand NULL handling.

7. How do NULLs behave in comparisons and aggregates?

NULL means unknown, so any comparison with it returns unknown rather than true — which is why you test with IS NULL rather than = NULL. Aggregate functions such as SUM and AVG ignore NULLs, so an average over a column with missing values is computed only over the rows that have values, which is sometimes exactly what you want and sometimes a bug.

8. What is the difference between DISTINCT and GROUP BY?

Both can return unique values. DISTINCT simply deduplicates the selected columns, while GROUP BY creates groups you can aggregate over. If you only need unique values, DISTINCT is clearer; if you need counts or sums per group, GROUP BY is the right tool.

9. How do you find duplicate rows in a table?

Group by the columns that define a duplicate and keep groups with more than one row — GROUP BY col1, col2 HAVING COUNT(*) > 1. To remove duplicates while keeping one copy, number the rows within each group using ROW_NUMBER and delete the rows where the number is greater than one.

10. How do you find the second highest value in a column?

The clean modern answer uses a window function: rank the rows with DENSE_RANK() ordered descending and filter where the rank equals two. The older approach uses a subquery selecting the maximum value below the overall maximum. Mention both, and say why DENSE_RANK handles ties the way you want.

Window Functions and Query Structure

11. What are window functions?

Functions that compute a value across a set of rows related to the current row, without collapsing the result into one row per group the way GROUP BY does. You keep every row and gain an extra calculated column — a running total, a rank within a category, a comparison with the previous row.

12. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER always gives unique consecutive numbers, breaking ties arbitrarily. RANK gives tied rows the same rank and then skips numbers, so two rows ranked one are followed by rank three. DENSE_RANK gives tied rows the same rank without skipping. This is one of the most frequently asked questions in analyst interviews.

13. What do PARTITION BY and ORDER BY do inside a window function?

PARTITION BY splits the rows into groups within which the function is calculated independently — rank within each region, for example. ORDER BY inside the OVER clause defines the order used for ranking or for cumulative calculations. Without PARTITION BY, the whole result set is treated as one partition.

14. How would you calculate a running total?

Use a cumulative window — SUM(amount) OVER (ORDER BY date) — optionally partitioned by a grouping column such as customer. Be ready for the follow-up about frame clauses, since by default an ordered window sums from the start of the partition to the current row.

15. What do LAG and LEAD do?

They read a value from a previous or following row within the window. LAG is how you compute month-on-month change or the time between a customer's consecutive orders without writing a self join. These show up constantly in analyst work.

16. What is a CTE and when is it better than a subquery?

A common table expression is a named temporary result defined with WITH at the top of a query. It is better than a nested subquery when the logic has several steps, because each step gets a name and the query reads top to bottom. For a single simple filter, a subquery is fine. Readability is a legitimate reason to prefer CTEs, and interviewers notice.

17. What is a correlated subquery?

A subquery that references a column from the outer query, so it is evaluated once per outer row rather than once in total. It is useful but can be slow on large tables, and rewriting one as a join or a window function is a common optimisation question.

Database Concepts and Real-World Scenarios

18. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows, can be filtered with WHERE, and is logged per row so it can be rolled back. TRUNCATE removes all rows quickly with minimal logging and cannot be filtered. DROP removes the table structure itself. A good answer mentions that TRUNCATE usually resets identity counters while DELETE does not.

19. What is the difference between a primary key and a unique key?

Both enforce uniqueness. A table has one primary key, it cannot contain NULLs, and it usually defines the clustered storage order. A table can have several unique constraints, and depending on the database they typically permit one NULL. Foreign keys reference the primary key.

20. What is an index and what is the trade-off?

An index is a data structure that speeds up lookups on particular columns, much like an index in a book. The trade-off is that every insert, update and delete must also maintain the index, and indexes consume storage. So you index columns used in filters and joins, not every column.

21. Why might a query be slow, and how would you investigate?

Start with the execution plan to see whether the database is scanning a whole table where it could use an index. Common causes are missing indexes, functions applied to a column in the WHERE clause which prevent index use, SELECT * pulling unnecessary columns, joins on non-indexed columns, and correlated subqueries. Mentioning that you would check the plan first rather than guessing is itself the answer they want.

22. How do you pivot rows into columns without a PIVOT clause?

Conditional aggregation — SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS jan and one such expression per column. This works in every database and is the answer expected in most analyst interviews.

23. What do COALESCE and NULLIF do?

COALESCE returns the first non-NULL value from its arguments, which is how you supply a default for missing data. NULLIF returns NULL when two expressions are equal, most commonly used to turn a zero denominator into NULL and avoid a divide-by-zero error.

24. A report shows fewer rows after you added a join. Why?

Almost certainly because you used an INNER JOIN where a LEFT JOIN was needed, so rows without a match were dropped. The other classic cause is a filter on the right-hand table placed in the WHERE clause, which silently converts a LEFT JOIN into an inner one — the filter belongs in the ON clause instead. This is a favourite scenario question because it happens constantly in real work.

25. A join returned more rows than the left table had. What happened?

The join key is not unique on the right-hand side, so each left row matched several right rows and the result multiplied. This is the single most common cause of inflated metrics in reports. The fix is to aggregate the right table to one row per key before joining, or to join on a genuinely unique combination.

What Each Level of Interview Tests

Level What they expect
Fresher Joins, GROUP BY, filtering, basic subqueries, NULL awareness
1–3 years Window functions, CTEs, duplicate handling, reading someone else's query
3+ years Optimisation, execution plans, data modelling, debugging wrong numbers

The Live Query Round

Most data interviews now include writing queries in a shared editor or on a whiteboard, against a small schema the interviewer describes. Two or three tables, a handful of columns, and questions like "find the top three products by revenue in each region" or "find customers who ordered in January but not in February".

Three habits improve your performance more than extra memorisation. Ask about the data before writing — can this column be NULL, is this key unique, are there duplicate rows. Those questions are not stalling; they are exactly what a good analyst does, and interviewers score them. Second, think aloud, so a wrong turn can be corrected before you have written twenty lines. Third, state your assumptions explicitly, because half these questions are deliberately underspecified to see whether you notice.

Practise without autocomplete at least a few times. A shared editor gives you far less help than the tools you normally use, and candidates who have only ever written SQL with suggestions appearing find the round harder than expected.

How to Practise Properly

Solving puzzle questions on practice platforms is useful but incomplete, because real analyst SQL is messier than a clean puzzle. Get a realistic dataset — a public dataset with several related tables — load it into any free database, and answer business questions rather than exercises. How did monthly revenue change by category? Which customers churned after one purchase? What percentage of orders contained more than one item?

Then deliberately verify your answers. Check row counts before and after joins, confirm totals against a simpler query, and look for the inflated-rows problem described in the last question above. Developing the habit of distrusting your own first result is what separates an analyst from someone who writes queries.

Common Mistakes in SQL Interviews

Writing a query immediately without asking anything about the data. Using SELECT * in an answer, which signals habit rather than thought. Not knowing how NULLs behave, which is tested in several of the questions above and surprises people who have only used clean datasets. Memorising the second-highest-salary query without being able to explain how it works, which collapses on the first follow-up. Claiming advanced SQL on a CV and then struggling with window functions. And treating a scenario question as a trick when it is simply describing something that happens on real data every week.

How Structured Training Helps

SQL is easy to learn badly — you can produce queries that run and return wrong numbers for months without anyone telling you. What training adds is review of your actual queries, realistic datasets rather than tidy exercises, and mock interviews including the live query round. You can start with ZebLearn India's Data Science course, which covers SQL alongside the analysis and modelling work it feeds.

People Also Ask / FAQ

How much SQL do I need for a data analyst job?

Comfortable joins, aggregation, subqueries, CTEs and window functions, plus the judgement to check whether a result is right. That covers the large majority of interview questions and daily work.

Which SQL dialect should I learn?

Any mainstream one. The core is the same across PostgreSQL, MySQL, SQL Server and cloud warehouses; only some functions and syntax details differ, and interviewers rarely penalise dialect differences.

Are window functions really asked at fresher level?

Increasingly yes, at least the ranking functions. They are the clearest way to separate candidates who practised beyond basics from those who stopped at joins.

How long does it take to get interview-ready in SQL?

A few focused weeks of daily practice on realistic data for most people, assuming you write queries rather than only reading about them.

Can I use AI tools to write SQL at work?

Most teams expect it. The requirement is that you can read, verify and correct what it produces — a plausible-looking query returning wrong numbers is worse than no query at all.

Is SQL still relevant with modern data tools?

Yes. Warehouses, BI tools and transformation frameworks are all SQL-based underneath, which is why it remains the most-used skill on data teams.

Build Interview-Grade SQL with ZebLearn India

Live classes online and in select cities across India Data Science Course Details