50 Data Analyst Interview Questions (With Answers)

Data analyst interviews test SQL fluency, statistical reasoning, Python/Excel skills, and the ability to turn raw data into business decisions. This guide covers 50 of the most common questions — with clear answers and practical examples.

Quick reference

SQL

1. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters after aggregation.

-- WHERE: filter raw rows
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'
GROUP BY department;

-- HAVING: filter aggregated results
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

2. Explain all types of SQL JOINs.

3. What are window functions and when do you use them?

Window functions compute a value across a "window" of related rows without collapsing them into a group. Unlike GROUP BY, all original rows remain in the result.

-- Running total, rank, and lag in one query
SELECT
  order_date,
  revenue,
  SUM(revenue)  OVER (ORDER BY order_date)           AS running_total,
  RANK()        OVER (ORDER BY revenue DESC)          AS revenue_rank,
  LAG(revenue)  OVER (ORDER BY order_date)            AS prev_day_revenue
FROM daily_sales;

Common window functions:

4. How do you find duplicate rows in SQL?

-- Find email values that appear more than once
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keeping the lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id)
  FROM users
  GROUP BY email
);

5. What is the difference between UNION and UNION ALL?

• UNION removes duplicate rows (slower — requires a distinct sort).
• UNION ALL keeps all rows including duplicates (faster — no deduplication).

Use UNION ALL unless you specifically need deduplication.

6. Explain subqueries vs CTEs.

-- CTE: readable multi-step analysis
WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
ranked AS (
  SELECT *, RANK() OVER (ORDER BY revenue DESC) AS rnk
  FROM monthly_revenue
)
SELECT * FROM ranked WHERE rnk <= 3;

7. How do you calculate a rolling 7-day average in SQL?

SELECT
  sale_date,
  daily_revenue,
  AVG(daily_revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM daily_sales;

8. What is a self-join and when is it useful?

A self-join joins a table to itself. Common use cases: employee-manager relationships, comparing rows within the same table.

-- Find employees whose salary is above their department average
SELECT e.name, e.salary, e.department
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY department
) dept ON e.department = dept.department
WHERE e.salary > dept.avg_sal;

Statistics

9. What is the difference between mean, median, and mode?

10. What is standard deviation? What does a high SD mean?

Standard deviation (SD) measures how spread out values are around the mean. A high SD means data points are widely dispersed; a low SD means they are clustered close to the mean.

SD = sqrt( Σ(xi − μ)² / N )

11. Explain p-value in plain English.

A p-value is the probability of observing your results (or more extreme ones) if the null hypothesis were true.

• p < 0.05: Result is statistically significant at the 5% level — unlikely due to chance alone.
• p > 0.05: Insufficient evidence to reject the null hypothesis.
• p-value does NOT tell you effect size or practical importance.

12. What is A/B testing? Walk through the process.

A/B testing compares two variants (A = control, B = treatment) to determine which performs better on a metric.

1. Define hypothesis — e.g., "Changing CTA colour from grey to orange increases click-through rate."
2. Choose metric — CTR, conversion rate, revenue per user.
3. Calculate sample size — based on desired power (0.80), significance level (0.05), and minimum detectable effect.
4. Randomise — split users randomly into groups.
5. Run test — collect data for a predetermined duration.
6. Analyse — apply a statistical test (t-test, chi-squared, z-test for proportions).
7. Decide — if p < 0.05 and effect is practically meaningful, roll out variant B.

Common mistakes: stopping early (peeking), multiple comparisons without correction, ignoring novelty effects.

13. What is the difference between correlation and causation?

Correlation means two variables move together. Causation means one variable directly causes the other.

Example: Ice cream sales and drowning rates are correlated — both rise in summer. But ice cream does not cause drowning; the confounder is hot weather.

To establish causation: randomised controlled experiments, instrumental variables, difference-in-differences, regression discontinuity.

14. What is a normal distribution? Why does it matter?

A normal (Gaussian) distribution is symmetric and bell-shaped, defined by mean (μ) and standard deviation (σ). It matters because:

• Many natural phenomena are approximately normal.
• Many statistical tests assume normality.
• The 68-95-99.7 rule: 68% of data falls within ±1 SD, 95% within ±2 SD, 99.7% within ±3 SD.

15. What is the Central Limit Theorem?

The CLT states that the distribution of sample means approaches a normal distribution as sample size increases, regardless of the population distribution. This underpins most hypothesis tests — even when the underlying data is not normal, the sampling distribution of the mean is approximately normal for n ≥ 30.

16. Explain Type I and Type II errors.

• α (significance level) controls Type I error rate (typically 0.05).
• β controls Type II error rate; power = 1 − β (typically 0.80).

Python and pandas

17. How do you read a CSV and inspect a DataFrame?

import pandas as pd

df = pd.read_csv("sales.csv")
df.head()          # first 5 rows
df.info()          # dtypes, non-null counts
df.describe()      # summary statistics
df.shape           # (rows, columns)
df.dtypes          # column types
df.isnull().sum()  # missing value counts per column

18. How do you handle missing values in pandas?

# Drop rows with any NaN
df.dropna()

# Fill with a constant
df.fillna(0)

# Fill with column mean
df['revenue'].fillna(df['revenue'].mean(), inplace=True)

# Forward-fill (useful for time series)
df.fillna(method='ffill')

# Check which columns have missing data
df.isnull().sum()[df.isnull().sum() > 0]

19. How do you group and aggregate data in pandas?

# Total and average revenue by region
summary = df.groupby('region').agg(
    total_revenue=('revenue', 'sum'),
    avg_revenue=('revenue', 'mean'),
    order_count=('order_id', 'count')
).reset_index()

# Multiple groups
df.groupby(['region', 'product'])['revenue'].sum()

20. How do you merge two DataFrames?

# Inner join (rows in both)
merged = pd.merge(orders, customers, on='customer_id', how='inner')

# Left join (all orders, matched customer info)
merged = pd.merge(orders, customers, on='customer_id', how='left')

# Join on different column names
merged = pd.merge(orders, products,
                  left_on='product_id', right_on='id', how='left')

pd.merge() types: inner, left, right, outer — same logic as SQL JOINs.

21. How do you detect and remove outliers?

import numpy as np

# Z-score method (flag if |z| > 3)
from scipy import stats
z_scores = np.abs(stats.zscore(df['revenue']))
df_clean = df[z_scores < 3]

# IQR method
Q1 = df['revenue'].quantile(0.25)
Q3 = df['revenue'].quantile(0.75)
IQR = Q3 - Q1
df_clean = df[(df['revenue'] >= Q1 - 1.5 * IQR) &
              (df['revenue'] <= Q3 + 1.5 * IQR)]

22. What is the difference between apply, map, and applymap?

Excel

23. What is the difference between VLOOKUP and XLOOKUP?

=VLOOKUP(A2, products!$A:$C, 3, 0)       -- exact match, 3rd column
=XLOOKUP(A2, products!$A:$A, products!$C:$C, "Not found")

24. How do you create a pivot table in Excel?

1. Click any cell in your data range.
2. Insert → PivotTable → choose New Worksheet.
3. Drag fields: rows (categories), columns, values (SUM/COUNT/AVG), filters.
4. Right-click value field → Value Field Settings to change aggregation.

Pivot table best practices: keep source data in a table (Ctrl+T for auto-expand), name tables descriptively, use slicers for interactivity.

25. What is SUMIFS and how is it different from SUMIF?

• SUMIF(range, criteria, sum_range) — single condition.
• SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...) — multiple conditions.

=SUMIFS(C2:C100, A2:A100, "North", B2:B100, ">2024-01-01")
-- Sum of column C where region=North AND date after 2024-01-01

26. How do you handle dates in Excel?

Excel stores dates as integers (days since 1 Jan 1900). Key functions:

Data cleaning

27. What steps do you take when you receive a new dataset?

1. Understand the schema — column names, types, row count.
2. Check for missing values — df.isnull().sum() / conditional formatting.
3. Examine distributions — df.describe(), histograms for numeric; value counts for categorical.
4. Identify duplicates — df.duplicated().sum().
5. Check data types — dates stored as strings? numbers as text?
6. Look for outliers — box plots, z-score, IQR method.
7. Check referential integrity — foreign keys that don't match.
8. Understand business rules — what values are valid? Is negative revenue possible?

28. How do you deal with duplicate records?

# Count duplicates
df.duplicated().sum()

# View duplicate rows
df[df.duplicated(keep=False)]

# Drop duplicates (keep first occurrence)
df.drop_duplicates(inplace=True)

# Deduplicate on specific columns
df.drop_duplicates(subset=['customer_id', 'order_date'], keep='first')

In SQL:

-- Keep lowest id per email
DELETE FROM users
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);

29. How do you handle inconsistent categorical data?

# View unique values
df['status'].value_counts()
# Output: active, Active, ACTIVE, actve -- all mean the same thing

# Normalise
df['status'] = df['status'].str.lower().str.strip()

# Map typos
df['status'] = df['status'].replace({'actve': 'active', 'inactiv': 'inactive'})

Data visualisation

30. Which chart type do you use for each situation?

31. What makes a good dashboard?

• Single source of truth — consistent, verified data.
• Audience-first — what decision does this inform?
• Key metrics prominent — KPIs above the fold.
• Context provided — comparisons to target, prior period, or benchmark.
• Minimal clutter — remove gridlines, legends that repeat axis labels, 3D effects.
• Consistent colours — one colour for one category throughout.
• Filters and drill-down — interactivity for exploration.

32. How do you choose between a bar chart and a line chart?

• Bar chart — comparing discrete categories (regions, products, departments).
• Line chart — showing continuous trends over time where the rate of change matters.

If the x-axis is time but you only have a few data points and want to compare magnitude, bars may be clearer. If the x-axis is continuous time and you want to show trajectory, lines win.

Business acumen and metrics

33. What KPIs would you track for an e-commerce company?

34. What is churn rate and how do you calculate it?

Monthly churn rate = (Customers lost in month) / (Customers at start of month) × 100

Example: 500 customers at start of month, 25 leave → churn = 5%.

High churn is expensive because CAC >> retention cost. A company with 10% monthly churn loses ~70% of customers per year.

35. Explain customer lifetime value (LTV/CLV).

LTV = Average order value × Purchase frequency × Customer lifespan

Or with margin:
LTV = (Average revenue per user × Gross margin) / Churn rate

LTV:CAC ratio > 3 is generally healthy. If LTV < CAC, the business is losing money on every customer acquired.

36. How would you measure the success of a new feature?

1. Define success metric before launch (e.g., 7-day retention, feature adoption rate).
2. Set a baseline using historical data.
3. Run an A/B test if possible — compare feature users to a control group.
4. Track leading indicators (engagement with the feature) and lagging indicators (retention, revenue).
5. Check for segment differences — does success vary by user cohort, geography, device?
6. Establish duration — run long enough to overcome novelty effects.

Probability

37. What is Bayes' theorem? Give a practical example.

P(A|B) = P(B|A) × P(A) / P(B)

Example — spam filter: Given that the word "free" appears, what is the probability the email is spam?

• P(spam) = 0.3 (30% of emails are spam)
• P("free" | spam) = 0.6
• P("free" | not spam) = 0.1
• P("free") = 0.6×0.3 + 0.1×0.7 = 0.25

P(spam | "free") = (0.6 × 0.3) / 0.25 = 0.72

So 72% chance the email is spam if it contains "free".

38. What is the expected value?

Expected value (EV) = Σ (outcome × probability of outcome).

Example — should you play a game where you win $10 with probability 0.4 and lose $5 with probability 0.6?

EV = (10 × 0.4) + (–5 × 0.6) = 4 – 3 = $1

Positive EV → play the game long-term.

Scenario and behavioural questions

39. Your data shows a sudden 40% drop in conversions. How do you investigate?

1. Confirm the drop is real — check for tracking errors, code changes, data pipeline issues.
2. Narrow time window — when exactly did it start?
3. Segment — is it affecting all channels, devices, regions, or just one?
4. Check external factors — site outages, competitor promotions, seasonality, ad budget changes.
5. Funnel analysis — where in the funnel are users dropping off?
6. Correlate with deployments — was a release pushed around that time?
7. Communicate findings — escalate to engineering / marketing with evidence before drawing conclusions.

40. How do you present data to a non-technical audience?

• Lead with the business question and answer, not the methodology.
• Use plain language — say "customers who bought twice are 3× more likely to return" not "the repeat-purchase coefficient is 3.1".
• Show one chart per message — don't cram everything into one visual.
• Use annotations on charts to explain key events or anomalies.
• Anticipate questions — have supporting details ready but don't lead with them.
• Use storytelling structure: situation → complication → resolution.

41. How would you decide whether a 2% increase in conversion rate is significant?

Run a two-proportion z-test (or chi-squared test):

1. Define H₀: conversion rates are equal. H₁: B > A.
2. Calculate observed conversion rates: pA and pB.
3. Compute the pooled proportion and z-statistic.
4. Check if z > 1.96 (for α = 0.05, two-tailed) or > 1.645 (one-tailed).
5. Also check practical significance — does 2% justify the engineering cost?

A 2% lift on 1,000 conversions/month is 20 extra conversions; on 1,000,000/month it is 20,000. Effect size matters.

42. You are given a dataset with 30% missing values. What do you do?

1. Understand why the values are missing: MCAR (missing completely at random), MAR (missing at random, depends on other columns), MNAR (missing not at random — the value itself influences whether it's recorded).
2. For MCAR/MAR: impute with mean, median, mode, forward-fill, or a model (KNN imputer, iterative imputer).
3. For MNAR: consider collecting the data, or model the missingness explicitly.
4. Create a missing indicator column — sometimes missingness itself is informative.
5. If > 50% missing and not critical, consider dropping the column.

Tools and technical

43. What tools have you used for data analysis and visualisation?

Common stack per level:

44. What is the difference between data analyst, data engineer, and data scientist?

In practice, roles overlap — many analysts write pipelines, many scientists do analysis.

45. What is ETL? How does it relate to a data analyst's work?

ETL = Extract, Transform, Load

1. Extract — pull data from source systems (APIs, databases, files).
2. Transform — clean, deduplicate, join, aggregate.
3. Load — write into a data warehouse or analytical store.

Data analysts typically work downstream of ETL — querying the clean data in the warehouse. However, analysts often write transformation logic in SQL using dbt or write Python scripts to supplement pipelines.

46. Explain the difference between OLTP and OLAP databases.

Analysts typically work with OLAP systems (data warehouses).

Advanced

47. What is cohort analysis and when do you use it?

Cohort analysis groups users by a shared characteristic at a point in time (usually signup date) and tracks their behaviour over time. It reveals retention patterns that aggregate metrics hide.

Example: January cohort has 60% 30-day retention; February cohort has 45%. Something changed in February — investigate product changes, marketing channels, or onboarding.

SELECT
  DATE_TRUNC('month', signup_date) AS cohort_month,
  DATE_TRUNC('month', activity_date) AS activity_month,
  COUNT(DISTINCT user_id) AS active_users
FROM user_activity
GROUP BY 1, 2
ORDER BY 1, 2;

48. What is a funnel analysis?

A funnel analysis tracks how users progress through a sequence of steps (e.g., visit → sign up → add to cart → purchase). It reveals where the biggest drop-offs occur.

SELECT
  COUNT(DISTINCT CASE WHEN step = 'visit'    THEN user_id END) AS visited,
  COUNT(DISTINCT CASE WHEN step = 'signup'   THEN user_id END) AS signed_up,
  COUNT(DISTINCT CASE WHEN step = 'add_cart' THEN user_id END) AS added_cart,
  COUNT(DISTINCT CASE WHEN step = 'purchase' THEN user_id END) AS purchased
FROM funnel_events;

49. Explain retention metrics: Day 1, Day 7, Day 30.

Calculated as:

SELECT
  DATE(first_seen) AS signup_date,
  COUNT(DISTINCT user_id) AS new_users,
  COUNT(DISTINCT CASE
    WHEN DATE(activity_date) = DATE(first_seen) + INTERVAL '7 days'
    THEN user_id END) AS d7_retained
FROM user_sessions
GROUP BY 1;

50. What is the difference between supervised and unsupervised learning? Give analyst-relevant examples.

Data analysts typically use supervised models via scikit-learn for scoring (churn probability, lead score) and unsupervised clustering for segmentation — then hand off more complex modelling to data scientists.

Common mistakes

Data analyst vs related roles

FAQ

Q: Do I need to know Python to be a data analyst? SQL is the non-negotiable skill. Python (especially pandas) is expected at most companies and greatly expands what you can automate. Start with SQL, add Python once you're comfortable.

Q: How important is statistics for a data analyst? Highly important. Mean/median, distributions, A/B testing, and regression are used weekly. Deep knowledge of ML is not required, but understanding p-values, confidence intervals, and correlation vs causation separates good analysts from great ones.

Q: What is the best way to prepare for a SQL round? Practice on real datasets using platforms like LeetCode (Easy–Medium), Mode Analytics, or StrataScratch. Focus on GROUP BY, window functions, CTEs, and multi-table JOINs.

Q: What is the most commonly asked business case question? "How would you measure the success of X?" Practice a structured framework: define the goal → choose a north-star metric → identify guardrail metrics → design an experiment or observational study → interpret results.

Q: Excel or Python — which should I focus on? Both. Excel is expected for quick ad-hoc analysis and sharing with non-technical stakeholders. Python is essential for larger datasets, automation, and more complex analysis. Learning both gives you flexibility.

Q: What should I include in a data analyst portfolio? Include 2–3 projects that show end-to-end work: data collection or extraction, cleaning, analysis, and a clear business insight. Use real datasets (Kaggle, public APIs, your own usage data). Publish Jupyter notebooks on GitHub and create a simple write-up explaining your findings.