Top 10 data analyst interview questions

Use a clear process and tie it to a decision:

1. Understand the question and the decision it supports; agree on the metric and its definition.
2. Collect the data: identify sources (database tables, exports, APIs), and check access and freshness.
3. Clean and validate: types, missing values, duplicates, inconsistent labels, outliers; reconcile totals against a known number.
4. Explore: distributions, trends, segments; form hypotheses.
5. Analyse: compare groups, funnels, cohorts, statistical tests if needed.
6. Communicate: the answer first, a few clear charts, the impact and a recommendation.
7. Follow up: automate or schedule it if it'll be needed again; document definitions.

Then give a short real example from a project or internship using these steps.

First understand why it's missing (not collected, not applicable, a system error, a join mismatch), because that determines the fix.

• Remove rows when the missing field is essential and few rows are affected.
• Impute: median for skewed numbers, mode for categories, forward-fill for time series, or a model-based estimate; flag imputed values with an indicator column.
• Keep as its own category ("Unknown") when missingness carries meaning.
• Fix at the source if it's a pipeline bug.

Dirty data: trim spaces, standardise case and spellings ("Bangalore" → "Bengaluru"), convert types, remove duplicates by a business key, validate ranges (age 0–100), and check outliers before removing them. Always record what you changed so results are reproducible.

• VLOOKUP searches the first column of a range and returns a value from a column to the right: =VLOOKUP(A2, Jobs!A:D, 3, FALSE). Limitations: it can't look left, a hard-coded column number breaks when columns are inserted, and you must remember FALSE for exact matches.
• INDEX-MATCH: =INDEX(Jobs!C:C, MATCH(A2, Jobs!A:A, 0)). It looks in any direction, doesn't break when columns move, and is efficient on big sheets.
• XLOOKUP (Excel 365/2021): =XLOOKUP(A2, Jobs!A:A, Jobs!C:C, "Not found"). Any direction, exact match by default, a built-in "not found" value, and it can search from the bottom. Use it when available.

Also mention pivot tables, SUMIFS/COUNTIFS, conditional formatting and removing duplicates as daily Excel tools.

SELECT c.customer_id, c.name, SUM(o.amount) AS total_spent, COUNT(*) AS orders
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days'
  AND o.status = 'completed'
GROUP BY c.customer_id, c.name
ORDER BY total_spent DESC
LIMIT 5;

Talk through the choices: filter completed orders only (ask whether refunds count), group by the id (names may repeat), and use DENSE_RANK instead of LIMIT if ties should be included. Interviewers care that you ask about definitions.

The mean is the arithmetic average; the median is the middle value when sorted.

The mean is pulled by extreme values; the median isn't. For skewed data (salaries, house prices, order values, time on site), the median better represents a "typical" value. Example: salaries of 20k, 22k, 25k, 26k and 3 lakh give a mean of about 79k but a median of 25k.

Use the mean for roughly symmetric data and when totals matter (average revenue per user × users = revenue). Report both when the gap between them is informative, and show the distribution (histogram or box plot).

An A/B test randomly splits users into a control group (current version) and a treatment group (one change), then compares a pre-defined primary metric.

Steps:

1. Hypothesis: "Showing salary on job cards increases the apply rate."
2. Choose the primary metric (apply rate) and guardrail metrics (application quality, bounce rate).
3. Calculate the sample size needed for the minimum detectable effect; fix the duration in advance (cover full weeks).
4. Randomise properly and check that the groups are balanced.
5. Analyse: the difference, its confidence interval and the p-value (e.g. below 0.05).
6. Decide: statistically and practically significant, guardrails OK → ship.

Pitfalls: peeking and stopping early, testing many metrics and picking the winner, novelty effects, and sample ratio mismatch (groups not split as planned).

Structure it:

1. Is it real? Check tracking: deployments, broken events, dashboard filters, time zones, a data pipeline delay.
2. When exactly did it start, and is it sudden or gradual? Align with releases, campaigns, outages, holidays and competitor launches.
3. Segment: platform (Android, iOS, web), app version, country/city, new vs returning, acquisition channel. A drop concentrated in one segment points to the cause.
4. Funnel: is the drop at app open, login, or a specific screen?
5. External factors: seasonality (exams, festivals), app store issues, a marketing campaign ending.
6. Conclude and recommend: the most likely cause with evidence, the impact, a fix and how to monitor it.

This "metric investigation" case is extremely common; practise saying it in two minutes.

A report answers a specific question at a point in time, often with narrative and recommendations. A dashboard is an ongoing, usually interactive, view of key metrics for monitoring.

A good dashboard:

• Serves one audience and purpose, and fits on one screen.
• Leads with 3–5 KPIs, each with context (vs target, vs last period).
• Flows from summary to detail: KPIs → trends → breakdowns → tables.
• Uses the right charts, consistent colours and clear labels.
• Has documented metric definitions and a visible "last refreshed" time.
• Loads fast and has useful filters (date, region, product).
• Leads to action: if nobody would do anything differently after seeing it, it's decoration.

BI tools work best with a star schema: a central fact table with measurable events (orders, applications) holding numeric columns and foreign keys, surrounded by dimension tables describing them (date, customer, product, city).

• Relationships usually run one-to-many from a dimension to the fact table.
• Filter direction flows from dimensions to facts.
• A dedicated date table enables time intelligence (month-to-date, year-over-year).
• Avoid many-to-many relationships and wide flat tables when you can; they cause wrong totals and slow reports.

In Power BI, calculations are measures written in DAX (Hire Rate = DIVIDE([Hired], [Applications])), evaluated in the current filter context; CALCULATE changes that context. In Tableau, the equivalents are calculated fields and LOD expressions ({FIXED [City] : SUM([Sales])}).

Use the STAR format (Situation, Task, Action, Result) with numbers:

• Situation: "In my internship at an ed-tech startup, the team planned to spend more on Instagram ads because they brought the most sign-ups."
• Task: "I was asked to report on channel performance."
• Action: "I joined sign-up data with payment data in SQL and built a cohort view by channel. Instagram users signed up most but converted to paid at 1.2%, versus 4.8% for referrals."
• Result: "The team moved 30% of the ad budget to a referral programme; paid conversions rose 22% the next month."

If you lack work experience, use a college or portfolio project, but be just as specific. Interviewers look for curiosity, rigour and impact.