Data Analyst Interview Questions for Freshers: 20 Questions with Model Answers

WHERE filters rows before grouping; HAVING filters groups after GROUP BY. For example, WHERE status = 'delivered' removes cancelled orders first, and HAVING SUM(amount) > 100000 then keeps only large categories.

An inner join keeps only matching rows in both tables. A left join keeps every row from the left table and fills unmatched columns with NULL. Use a left join for questions like “customers who never ordered”, then filter WHERE o.order_id IS NULL.

A clear answer uses a window function: rank salaries with DENSE_RANK() OVER (ORDER BY salary DESC) in a CTE, then select the row where the rank is 2. Mention why DENSE_RANK: if two people share the top salary, RANK would skip 2.

Group by the columns that should be unique and keep groups with more than one row: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1; Then explain how you would decide which duplicate to keep, for example the latest by updated_at using ROW_NUMBER().

Aggregate revenue by month in a CTE, then use LAG(revenue) OVER (ORDER BY month) to get the previous month and compute (revenue - prev) * 100.0 / prev. Mention multiplying by 100.0 to avoid whole-number division.

Because the relationship was one-to-many: joining orders to order_items repeats each order once per item. Check row counts after each join, and aggregate to the right level before joining. Interviewers ask this to see whether you understand what one row means.

XLOOKUP can look left as well as right, defaults to an exact match, and has a built-in value for “not found” (Microsoft: XLOOKUP). VLOOKUP only looks to the right and needs a column number, which breaks when columns are inserted. Say you use XLOOKUP where available and know VLOOKUP for older files.

Convert the range to a table, insert a pivot table with region in rows, month in columns and sum of sales in values, then add a slicer for product category. Mention grouping dates by month in the pivot and checking the grand total against the raw data.

Remove duplicates, use TRIM and CLEAN for extra spaces, fix data types (text numbers, inconsistent dates), standardise categories such as “Bangalore” and “Bengaluru”, and flag blanks. For repeated monthly cleaning, use Power Query so the steps rerun automatically.

A calculated column is computed row by row and stored in the model; use it for values you slice by, such as an age band. A measure is calculated at query time in the current filter context; use it for totals and ratios (Microsoft Learn: Measures). Prefer measures for aggregations, because they do not bloat the model.

It evaluates an expression in a modified filter context. For example, Delivered Sales = CALCULATE([Total Sales], Orders[Status] = "delivered"). Most advanced DAX, including time intelligence, is built on CALCULATE.

Fact tables hold events (sales lines) and dimension tables describe them (date, product, customer). A star schema keeps relationships simple, makes filters behave predictably and makes DAX easier, which is why Microsoft recommends it for Power BI models (Star schema guidance).

Row-level security: create a role in Power BI Desktop with a DAX filter, for example on the region table, test it with “View as”, publish, and assign users or groups to the role in the Power BI service.

Median, because a few very high salaries pull the mean up and make the typical value look higher than it is. Report the mean as well if you also want to show the effect of the outliers.

First check whether they are errors (a typo, a test order) or real (a bulk corporate order). Remove or fix errors; keep real values but consider reporting them separately, or use median and percentiles. Always say what you did in your report.

Ice cream sales and AC sales both rise in summer, so they are correlated, but one does not cause the other; temperature drives both. In business, say you would test causation with a controlled comparison, such as an A/B test, before claiming one thing causes another.

Structure the answer. First confirm the drop is real: check data completeness and compare with the same month last year for seasonality. Then break revenue into orders × average order value, and split by region, channel, product and customer type to find where the drop is concentrated. Finally, check known events such as price changes, stock-outs or a site outage, and recommend what to monitor.

Ask who uses it and what decision it supports. A typical answer: revenue vs target, orders, average order value, return or cancellation rate, and trend by month, with filters for region and product. Explain why each metric is there and keep the first view to five or six numbers.

Use a fixed structure: the business question, the data source and its size, how you cleaned it, the analysis, the dashboard or output, the key finding, and one thing you would do differently. Keep it to about five minutes and expect follow-up questions on any step.

Give a specific reason linked to something you did, such as a project where a finding changed a decision, rather than “data is the future”. Then connect it to the company’s work, which shows you researched them.