📢
Admissions Open for August 2026 Batch | Free Career Counselling | Limited Scholarships
Register Now →

Interview Preparation

SQL Interview Questions for Data Analysts (With Answers)

By Aniruddha Abhay Kalbande 12 min read

SQL interviews for data analyst roles usually test five things: joins, aggregation with GROUP BY and HAVING, subqueries and CTEs, window functions, and how you reason about a query you have never seen. Most rounds are live, so explaining your thinking aloud matters as much as producing correct syntax.

How SQL rounds actually run

Expect a shared screen, a schema you have not seen, and two or three problems of increasing difficulty. The interviewer is watching how you approach an unfamiliar table structure at least as closely as whether your query runs first time.

Fundamentals

What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use an aggregate function such as SUM or COUNT in WHERE, because the aggregate does not exist yet at that stage of execution.

Explain the order of execution in a SQL query.

Written order is SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Execution order is FROM, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY. This is why you cannot reference a SELECT alias in WHERE but can in ORDER BY.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows and can be filtered with WHERE, and it is logged row by row so it can be rolled back. TRUNCATE removes all rows quickly with minimal logging. DROP removes the table structure entirely.

What is the difference between UNION and UNION ALL?

UNION combines two result sets and removes duplicates, which requires a sort and costs performance. UNION ALL keeps everything including duplicates and is faster. If you know duplicates are impossible, UNION ALL is the correct choice.

How do NULLs behave in SQL?

NULL means unknown rather than zero or empty. Any comparison with NULL using = returns NULL rather than true, so you must use IS NULL. Aggregate functions such as SUM and AVG ignore NULLs, but COUNT(*) counts the row while COUNT(column) does not count NULLs in that column.

Joins, where most candidates lose marks

Explain INNER, LEFT, RIGHT and FULL OUTER JOIN.

INNER returns only matching rows from both tables. LEFT returns all rows from the left table plus matches from the right, with NULLs where there is no match. RIGHT is the mirror of LEFT. FULL OUTER returns everything from both sides with NULLs where a match is missing.

Your LEFT JOIN returned more rows than the left table had. Why?

Because the join key is not unique on the right side, so each left row matched multiple right rows. This is called a fan-out, and it is the most common cause of silently inflated totals. Check the row count before and after every join.

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

A table joined to itself, used for hierarchical or comparative data within one table. The standard example is an employees table where each row has a manager_id pointing at another row in the same table.

What is a CROSS JOIN?

Every row of the first table combined with every row of the second, producing a Cartesian product. Deliberately useful for generating date and category combinations, and accidentally produced when you forget the join condition.

How would you find rows in table A that have no match in table B?

A LEFT JOIN from A to B with a WHERE clause of B.key IS NULL. A NOT EXISTS subquery is often clearer and can perform better on large data.

Aggregation and grouping

Find the second highest salary from an employees table.

The most readable approach uses a window function: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) t WHERE r = 2. Interviewers often follow up by asking what happens with ties, which is exactly why DENSE_RANK is a better answer than LIMIT with OFFSET.

How would you find duplicate rows in a table?

GROUP BY the columns that define a duplicate and use HAVING COUNT(*) > 1. To see the full duplicate rows rather than just the keys, join that result back to the original table.

What is the difference between COUNT(*), COUNT(1) and COUNT(column)?

COUNT(*) and COUNT(1) both count rows and perform identically in modern databases. COUNT(column) counts only rows where that column is not NULL, which is a meaningful and frequently overlooked difference.

Calculate a running total of sales by date.

SUM(sales) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Being able to state the window frame explicitly signals genuine understanding rather than memorisation.

Window functions, which separate juniors from seniors

What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

ROW_NUMBER assigns unique sequential numbers with no ties. RANK gives tied rows the same rank then skips numbers, so two first places are followed by third. DENSE_RANK gives tied rows the same rank without skipping, so two first places are followed by second.

How do you get the top N rows per group?

Use ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) in a subquery or CTE, then filter to the row numbers you want in the outer query. This is one of the most frequently asked analyst questions in India.

What do LAG and LEAD do?

They access a value from a previous or following row in the same result set without a self join. They are the standard tool for month-on-month or year-on-year comparisons.

What is the difference between PARTITION BY and GROUP BY?

GROUP BY collapses rows into one row per group. PARTITION BY keeps every row and computes the window calculation within each partition. If you need detail rows alongside a group-level number, you need a window function.

Practical and scenario questions

A report showed sales dropped 30% last month. How would you investigate in SQL?

Confirm the drop is real before explaining it: check whether the data loaded completely, whether the date filter is correct, and whether any source system changed. Then break the number down by region, product, channel and customer segment to find whether the drop is broad or concentrated. Interviewers are testing scepticism, not query syntax.

How would you optimise a slow query?

Look at what is actually expensive: examine the execution plan, check for missing indexes on join and filter columns, avoid SELECT *, filter as early as possible, and be suspicious of functions applied to indexed columns in the WHERE clause because they prevent index use.

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

A common table expression is a named temporary result defined with WITH. It is better when the same intermediate result is used more than once, or when nesting subqueries would make the query hard to read. Readability matters in interviews because someone has to maintain your query.

How to prepare in the two weeks before an interview

  1. Practise on a database with realistic size and messiness rather than five-row examples.
  2. Write out loud. Record yourself explaining a query and listen back.
  3. Drill the top-N-per-group pattern until it is automatic, because it appears constantly.
  4. Prepare one project you can discuss in depth, including what the data could not tell you.
  5. Practise saying 'I would check X first' rather than guessing when you are unsure.

SQL is taught from fundamentals through window functions in the AI-Powered Data Analytics certification and the Post Graduate Program in Data Science & Analytics, both of which include mock interview rounds with recorded feedback.

Watch

Struggling in SQL Interviews? Must-Know Answers

FAQ

Frequently Asked Questions

What SQL topics are most asked in data analyst interviews?

Joins, GROUP BY with HAVING, subqueries and CTEs, and window functions, particularly ROW_NUMBER, RANK and DENSE_RANK. The top-N-per-group pattern using PARTITION BY appears very frequently in Indian analyst interviews.

How much SQL do I need for a fresher data analyst role?

Enough to write a query with two or more joins, aggregation and a window function against a schema you have not seen before, without looking up syntax. That is a realistic bar for a first analyst job.

Are SQL interviews live or take-home?

For analyst roles in India they are usually live, on a shared screen. This is why explaining your reasoning as you work matters as much as the final query.

What is the most common mistake in SQL interviews?

Writing code before understanding the schema. The second most common is not noticing that a join has multiplied rows, which silently inflates every total that follows.

Want This Mapped to Your Own Background?

A free counselling session will tell you which path fits, and will tell you honestly if none of ours does.

Book Free Career Counselling

Keep Reading

Related Articles