SQL and Data Analyst Interview Questions for Freshers (with Answers)
Nearly every data analyst interview has a live SQL round. After that come Excel or BI tool questions, basic statistics, and a short case where you explain how you would investigate a business question.
SQL concepts
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can, for example HAVING COUNT(*) > 5.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows with a match in both tables. LEFT JOIN keeps every row of the left table and fills the right side with NULL where there is no match, which is how you find customers with no orders.
UNION versus UNION ALL?
Both stack the results of two queries. UNION removes duplicate rows, which costs a sort or hash; UNION ALL keeps everything and is faster.
RANK, DENSE_RANK and ROW_NUMBER: what is the difference?
All number rows within a window. ROW_NUMBER gives every row a distinct number. RANK gives ties the same rank and then skips (1, 1, 3). DENSE_RANK gives ties the same rank without gaps (1, 1, 2).
How do you find the second highest salary?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use DENSE_RANK() OVER (ORDER BY salary DESC) and pick rank 2, which also generalises to the n-th highest.
How do you find duplicate rows?
Group by the columns that define a duplicate and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1.
What is a CTE?
A common table expression is a named temporary result defined with WITH at the start of a query. It makes long queries readable by breaking them into steps and can be recursive.
How are NULLs handled?
NULL means unknown. Any comparison with NULL is unknown, so you test with IS NULL, not = NULL. Aggregates such as SUM and AVG ignore NULLs, and COUNT(column) skips them while COUNT(*) does not.
DELETE, TRUNCATE and DROP: what is the difference?
DELETE removes chosen rows and can be rolled back. TRUNCATE removes all rows quickly and resets the table. DROP removes the table itself, structure and data.
Excel, statistics and BI
VLOOKUP or INDEX-MATCH (or XLOOKUP)?
VLOOKUP only looks to the right of the key column and breaks when columns are inserted. INDEX-MATCH and XLOOKUP look in any direction and are more robust. XLOOKUP also handles not-found values directly.
What is a pivot table?
A tool that summarises a table by grouping rows and columns and aggregating a value, for example total sales by region and month, without writing formulas.
Mean or median: which do you report?
The mean is pulled by extreme values; the median is the middle value and is not. For skewed data such as salaries or order values, report the median, or both.
What is the difference between correlation and causation?
Correlation means two variables move together. Causation means one changes the other. Correlated variables may both be driven by a third factor, so you need an experiment such as an A/B test to claim cause.
What is a p-value?
The probability of seeing a result at least as extreme as yours if there were really no effect. A small p-value (commonly below 0.05) is evidence against "no effect"; it is not the probability that your hypothesis is true.
How do you handle missing data?
First find out why it is missing. Then drop the rows if they are few and random, fill with a sensible value such as the median or a group average, or keep a "missing" flag. Say what you did, because it changes the result.
Case question: "Sales dropped 10% last week. How would you investigate?"
Interviewers want structure, not a lucky guess. Confirm the number is real first: check the data pipeline, the date range and whether a holiday or a tracking change explains it. Then break the metric down by one dimension at a time (region, product, channel, new versus returning customers, device) to see whether the drop is everywhere or concentrated. Split sales into its parts — visitors, conversion rate and average order value — to see which moved. Finally check what changed that week: price, a campaign ending, a release, a competitor, stock-outs. Finish by saying what you would recommend and what you would monitor.