Fresher Interview Questions for Data Analyst Roles: 40 Q&A with Real SQL Answers

Fresher data analyst interviews in India follow a predictable shape: a SQL or Excel test, a technical conversation about queries and statistics, a short business case, and an HR or managerial round. The questions below are the ones that come up again and again at IT services firms, analytics consultancies, KPOs, startups and captive centres, grouped so you can prepare one block at a time.

Every SQL answer is a real query you can run. The tables used throughout are:

• employees(emp_id, name, dept_id, salary, hire_date)
• departments(dept_id, dept_name)
• customers(customer_id, name, email, city)
• orders(order_id, customer_id, order_date, amount)

Answers are written the way a strong fresher would say them, not as textbook definitions. Where a question has a follow-up that interviewers commonly ask, it is included.

SQL questions (1–12)

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation. If the condition uses an aggregate, it has to be in HAVING.

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01'
GROUP BY dept_id
HAVING AVG(salary) > 50000;

Follow-up: "Can you use an alias in HAVING?" In most databases, no; repeat the aggregate.

2. Find the second highest salary.

Two common ways. The subquery version works everywhere; the window version handles ties cleanly.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- with a window function
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

3. Find duplicate email addresses in the customers table.

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Follow-up: "How would you delete the duplicates but keep one?" Use ROW_NUMBER partitioned by email and delete rows where the row number is greater than 1.

4. Explain INNER JOIN vs LEFT JOIN, and find customers who have never ordered.

INNER JOIN returns rows that match in both tables. LEFT JOIN returns every row from the left table and NULLs where the right side has no match. Customers with no orders are exactly the NULL side of a LEFT JOIN.

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;

5. Total sales per month for 2025.

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales
FROM orders
WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

In MySQL replace DATE_TRUNC with DATE_FORMAT(order_date, '%Y-%m'). Mention that you avoid wrapping the column in a function inside WHERE so the index can still be used.

6. Top 3 customers by revenue.

SELECT c.name, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY revenue DESC
LIMIT 3;

Follow-up: "What if two customers tie at third place?" LIMIT cuts arbitrarily; use DENSE_RANK if ties must be kept.

7. Compute a running total of daily sales.

SELECT order_date,
       SUM(amount) AS daily_sales,
       SUM(SUM(amount)) OVER (ORDER BY order_date) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date;

The inner SUM aggregates per day; the outer SUM with OVER accumulates across days.

8. Difference between ROW_NUMBER, RANK and DENSE_RANK.

All three number rows within an ordered window. ROW_NUMBER gives unique sequential numbers even for ties. RANK gives tied rows the same number and then skips (1, 2, 2, 4). DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3). Use ROW_NUMBER to pick one row per group, DENSE_RANK for "Nth highest" questions.

9. Which department has the highest average salary?

SELECT d.dept_name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY avg_salary DESC
LIMIT 1;

10. Employees earning more than their department's average.

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, a.avg_salary
FROM employees e
JOIN dept_avg a ON a.dept_id = e.dept_id
WHERE e.salary > a.avg_salary;

Say that a CTE is easier to read than a correlated subquery and usually performs the same.

11. UNION vs UNION ALL.

UNION removes duplicate rows and therefore sorts or hashes the result, which costs time. UNION ALL keeps every row. If you know the sets do not overlap, or duplicates are fine, use UNION ALL.

12. Why does COUNT(column) differ from COUNT(*)? How do you handle NULLs?

COUNT(*) counts rows; COUNT(column) ignores NULLs in that column. NULL is not equal to anything, including NULL, so filter with IS NULL. Use COALESCE to substitute a default: COALESCE(city, 'Unknown'). In averages, NULLs are ignored, which silently changes the denominator; say that out loud in interviews, it shows you think about data quality.

Excel questions (13–20)

13. VLOOKUP vs XLOOKUP vs INDEX/MATCH.

VLOOKUP searches the first column of a range and returns a value to the right; it breaks if columns are inserted and cannot look left. INDEX/MATCH looks in any direction and is robust to column changes. XLOOKUP replaces both: it looks any direction, has a built-in not-found value, and supports exact match by default. Example: =XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found").

14. What is a pivot table and when do you use one?

A pivot table summarises a flat table by dragging fields into rows, columns and values, so you can get sales by region by month without writing formulas. Use it for quick aggregation and exploration; use formulas or Power Query when the output must be reproducible or feed another sheet.

15. How do you clean messy data in Excel?

Trim spaces with TRIM, remove non-printing characters with CLEAN, standardise case with PROPER or UPPER, split combined fields with Text to Columns, and remove exact duplicates with Data > Remove Duplicates. For anything repeatable, load the sheet into Power Query so the steps replay when the source changes.

16. Give an example of SUMIFS and COUNTIFS.

Sales in Mumbai in March: =SUMIFS(Sales!D:D, Sales!B:B, "Mumbai", Sales!A:A, ">=2025-03-01", Sales!A:A, "<2025-04-01"). Number of orders above 5,000: =COUNTIFS(Sales!D:D, ">5000"). The pattern is sum-range first, then pairs of criteria range and criterion.

17. Absolute vs relative references.

A relative reference like A2 shifts when copied. An absolute reference like $A$2 stays fixed. Mixed references ($A2, A$2) fix one axis. The classic use is a tax rate in one cell referenced by every row.

18. How do you use conditional formatting in analysis?

To surface patterns before charting: colour scales on a KPI table, data bars for relative size, and rules such as "highlight cells below target." It is a first-look tool, not the final visual.

19. What is Power Query and why would you use it over formulas?

Power Query is Excel's built-in ETL tool. You connect to a file, database or folder, apply steps (filter, merge, unpivot, change types), and refresh with one click. It handles larger data than formulas, keeps a visible step history, and the same skill transfers to Power BI.

20. What does #N/A mean and how do you handle it?

A lookup did not find the key. Wrap it: =IFERROR(XLOOKUP(...), "Missing") or =IFNA(...). Before hiding the error, check whether the key has trailing spaces or a number stored as text; those are the usual causes.

Statistics questions (21–28)

21. Mean, median and mode: when do you use the median?

Mean is the average, sensitive to extreme values. Median is the middle value, robust to outliers. Mode is the most frequent value. Use the median for skewed data such as salaries or delivery times, where a few very large values pull the mean up.

22. What are variance and standard deviation?

Both measure spread around the mean. Variance is the average squared distance; standard deviation is its square root, so it is in the same unit as the data. A low standard deviation means values cluster near the mean.

23. Correlation vs causation.

Correlation says two variables move together; causation says one drives the other. Ice cream sales and drowning both rise in summer; temperature causes both. Before claiming causation you need a controlled experiment or at least a plausible mechanism and a check for confounders.

24. Explain a p-value in plain words.

If there were really no effect, the p-value is the probability of seeing a result at least this extreme by chance. A small p-value (commonly below 0.05) means the result would be unusual under "no effect," so we treat it as evidence of an effect. It is not the probability that the hypothesis is true.

25. What is a normal distribution and why does it matter?

A symmetric bell-shaped distribution described by mean and standard deviation. Many measurement errors and averages of large samples are approximately normal, which is why so many tests assume it. Check the assumption with a histogram before relying on it.

26. How do you detect and handle outliers?

Detect with a box plot, the IQR rule (values beyond 1.5 times the interquartile range from the quartiles) or z-scores. Then find out why they exist: a data entry error gets fixed, a real extreme value stays. Never delete outliers just because they are inconvenient; report what you did.

27. What is sampling bias?

When the sample does not represent the population, for example surveying only app users about app satisfaction. The fix is a sampling method that gives every member a known chance of selection, and honest reporting of who was left out.

28. Explain an A/B test.

Randomly split users into two groups, show each a different version, measure the same metric, and test whether the difference is larger than chance. Decide the metric, sample size and duration before starting, and do not stop early because one side looks ahead.

Business case questions (29–34)

29. Sales dropped 15% last month. How do you investigate?

Confirm the number is real first (data pipeline, missing days, currency). Then break it down: by region, product, channel, customer segment and week. Find where the drop concentrates. Compare with the same month last year for seasonality. Check external causes such as a price change, a competitor launch or a stock-out. Present the two or three segments that explain most of the drop, with a recommended next step.

30. What KPIs would you track for a food delivery app?

Orders per day, average order value, delivery time, cancellation rate, repeat rate, customer acquisition cost, and contribution margin per order. Pick one north-star metric (completed orders or gross order value) and treat the rest as diagnostics.

31. A dataset has many missing values. What do you do?

Measure how much is missing per column and whether it is random. Drop columns that are mostly empty and irrelevant. For the rest, impute with median or a category such as "Unknown," or model the missingness if it carries meaning. Document every choice so the analysis can be reproduced.

32. How do you present findings to a non-technical manager?

Lead with the answer in one sentence, show one chart that supports it, state the recommendation and the risk, and keep the method in an appendix. Avoid jargon; say "customers who ordered twice in the first month stay three times longer," not "cohort retention curves diverge."

33. Estimate how many cabs operate in Bengaluru.

Interviewers want the structure, not the number. Start from population, estimate the share that takes cabs on a given day, estimate trips per person and trips a cab completes per day, divide. State each assumption, sanity-check the answer against something known, and say what data you would pull to replace the guesses.

34. Which chart for which data?

Trend over time: line. Comparison across categories: bar. Part of a whole with few categories: stacked bar (pie only if there are two or three slices). Relationship between two numeric variables: scatter. Distribution: histogram or box plot. Never use a 3D chart.

Behavioural questions (35–40)

35. Tell me about yourself.

Thirty seconds: degree, the one project or internship that used data, the tools you actually used (SQL, Excel, maybe Python or Power BI), and why this role. Example: "I am a 2026 B.Com graduate with an analytics minor. My final-year project analysed two years of a retail store's billing data in SQL and Excel to find which categories drove repeat visits, and I built a Power BI dashboard the owner still uses. I am applying here because the role is about exactly that kind of customer analysis."

36. Why data analytics?

Give a concrete moment, not a slogan. "During my internship I noticed the sales team argued for an hour about which region was underperforming. I pulled the numbers in twenty minutes and the argument ended. I want to be the person who does that."

37. Walk me through a data project you did.

Use problem, data, method, result, learning. Name the data size, the messiest part, one decision you made and why, and the outcome in numbers. Prepare follow-ups on every tool you mention; see the guide on explaining your final-year project.

38. Tell me about a mistake you made in an analysis.

Pick a real, small one: a join that duplicated rows and inflated revenue, caught before presenting. Say how you found it, what you changed (row-count checks after every join), and that you now do it by default.

39. How do you prioritise when three people want reports today?

Ask each what decision the report feeds and when that decision is made. Do the one that blocks a decision first, negotiate the others, and tell everyone the order so nobody is surprised.

40. What is your salary expectation, and where do you see yourself in five years?

Quote a range based on what fresher analysts earn in that city and company type, and say you are flexible for the right learning. For five years, say senior analyst or analytics lead with ownership of a business area, and mention one skill you plan to add each year (Python, then statistics, then stakeholder management).

How to prepare in the last week

1. Set up a free PostgreSQL or MySQL sandbox and run every query above against your own sample tables. Change them: top 5, per quarter, per city.
2. Rebuild one Excel exercise end to end: raw export, cleaning in Power Query, pivot, one chart.
3. Explain questions 21 to 28 aloud to a friend who is not technical; if they understand, the interviewer will.
4. Write your answers to 35 to 40 in your own words and time them; nothing over ninety seconds.
5. Run a MockMate Practice session with your resume and the job description attached and choose a data analyst round. The interviewer persona follows up on your project and your SQL, and the report shows response timing and recurring weaknesses. Eligible accounts get three free ten-minute Practice starts. Use Live assistance only where the organisation, interviewer or applicable rules permit assistance and disclosure; use Practice when permission is unclear.

For the HR round that usually follows, see HR round questions for freshers and behavioural questions for freshers.