Data Analyst Interview Questions and Answers, Round by Round

Pin the metric first: which revenue (gross or net), which week against which baseline, and whether both weeks are complete. Then rule out the data before the business: did the pipeline run, did order counts match the source system, did a tracking tag change. Only then segment along the dimensions the business already uses (city, product, channel, new vs returning, device) and find where the drop concentrates. Turn that into one number, such as 'Pune explains 12 of the 15 points', name the outside events you would check (a paused campaign, a price change, a holiday), and finish with the two things you would verify next. The interviewer is scoring the order of those steps, not your guess.

A metric is any measured quantity: orders per day, page views, average order value. A KPI is the small set of metrics a team has agreed to be judged on because they track the outcome the business wants. Every KPI is a metric; almost no metric is a KPI. In a case question, name one outcome KPI, two or three input metrics the team can move weekly, and one guardrail metric that must not get worse.

Say: 'If the change did nothing at all, this is how often we would still see a result at least this big by luck.' A p-value of 0.03 means three times in a hundred. It is not the probability that the change works, and it is not the size of the effect; a tiny, useless improvement can have a small p-value if the test is large enough. So report three things together: the difference, the confidence interval around it, and whether the test ran its planned length.

A calculated column is evaluated row by row when the data loads and stored in the table, so it takes memory and does not react to slicers. A measure is evaluated at query time over whatever rows the visual has filtered, so it responds to every slicer and row header. 'Order year' is a column; 'total sales' and 'average order value' are measures. Putting a total into a calculated column is the mistake interviewers wait for, because the number stops changing when the user filters.

A line for change over time when the x-axis is ordered (dates). A bar for comparing categories, sorted by value unless the categories have a natural order. A table when the reader needs the exact numbers, or more than two dimensions at once. A pie only for a single part-of-whole with two or three slices, and even then a bar is usually clearer. If the interviewer shows a chart, the expected critique is nearly always: wrong chart type, unsorted bars, a truncated axis, or too many series.

XLOOKUP where the Excel version allows it: exact match by default, looks left or right, and takes an if-not-found value. INDEX-MATCH on older versions, for the same reasons minus the not-found argument. VLOOKUP only when you must, and always with FALSE for exact match; with approximate match on an unsorted list it returns a nearby wrong row without an error, which is how a missing customer id ends up with someone else's revenue.

First find out why it is missing, because the fix depends on it: a broken tag, a field that did not exist before a date, or a value that is legitimately absent (no discount). Then count it and show the count in the deliverable. For the analysis itself: drop rows only when the missing field is the one being measured and the loss is small; fill with a group value (the city's median, not the global median) when the field is a covariate; keep an explicit 'unknown' category for dimensions rather than silently merging it into the largest group. Never let a fillna(0) turn missing revenue into zero revenue.

One fact table of events at a fixed grain (an order line, a session) surrounded by dimension tables that describe them (customer, product, date, city), joined on keys, with relationships flowing one way from dimension to fact. Power BI's engine and DAX are built around it: filters travel from dimensions to the fact table, measures aggregate the fact table, and the model stays fast and small. One wide flat table looks simpler and breaks the moment two facts (orders and returns) need the same customer filter.

State the difference first: 0.4 percentage points, about 8% relative. Check the test ran its planned duration and sample size, and that the split stayed 50/50. Then a two-proportion z-test: pooled p = 0.052, standard error about 0.0022, z about 1.8, p-value about 0.07. It does not clear 0.05. The honest readout is 'a promising direction, not a decision', with the confidence interval (roughly -0.04 to +0.84 points) and the extra sample needed to settle it. Stopping a test the first day it looks significant, or reporting only the relative lift, are the two mistakes the interviewer is listening for.

Group customers by when they started (the January signups, the February signups), then follow each group through the same relative periods (month one, month two) instead of calendar months. Example: January's cohort retained 40% in month one, February's retained 48%; the product change in late January is a candidate cause because it affected February's first month but not January's. Without cohorts, the blended retention number mixes new and old customers and hides the change.

Ask what they expected and where their number comes from, because half of these are two valid definitions (gross vs net, booked vs shipped). Then reconcile from the source up: row counts at each step of the pipeline, the filter each step applied, and a join-multiplication check (a LEFT JOIN that returns more rows than the left table). Recompute their number with their definition in your data; if the two still disagree by a fixed amount or a fixed ratio, the gap usually names the cause (a timezone, a currency, a duplicated key). Publish the reconciliation with the fix, and add the check to the dashboard so the next disagreement is caught by a test instead of a stakeholder.

Ice cream sales and drowning deaths rise together every summer; heat drives both. In a product context: users who enable dark mode retain better, but they are the power users who explored settings, not people improved by dark mode. The follow-up question is how you would test it, and the answer is an experiment or, failing that, a comparison within groups of similar users rather than across everyone.