Data Analyst Interview Questions and Answers

Data analyst interview questions come in rounds, and each round tests something different: whether you can get data out of a database, work with it in a spreadsheet or Python, judge whether a difference is real, show it clearly, and turn it into a recommendation. This guide walks through the usual rounds with representative questions and the answers interviewers listen for, then works a realistic take-home task on real data, because that is where most candidates win or lose.

The rounds, and what each one tests

SQL. Nearly every analyst process has a SQL round: joins, aggregation, window functions and business-style problems. Expect "find customers who bought last quarter but not this one" or "rank products within each category". A strong answer states assumptions, handles NULLs and duplicates, and checks row counts. Practise with SQL interview questions: top 50 with answers and the business-problem set in scenario-based SQL interview questions.

Spreadsheets. Many teams still live in Excel or Google Sheets. Expect lookups, pivot tables, conditional aggregation and date handling, and sometimes a live task. The details are in Excel interview questions for data analysts.

Python or R. Increasingly common for analyst roles. Expect pandas cleaning, grouping and merging, with traps such as missing values in disguise and merges that multiply rows; see pandas interview questions.

Statistics. Usually practical rather than theoretical: is this difference real, what does this p-value mean, why the median here? See statistics interview questions.

Dashboards and communication. Some processes include a BI tool such as Power BI or Tableau, or ask you to critique a chart. The questions test chart choice, filters and calculated fields, and whether the dashboard answers a decision; see Tableau interview questions.

Take-home or case study. A dataset and an open question, such as "where should marketing spend more?", followed by a presentation. This round decides most offers, so the rest of this guide works one.

Behavioural. Stories about past work: a disagreement, a mistake, an analysis that changed a decision. Prepare three or four concrete examples with numbers.

A take-home task, worked on real data

The task: an online shop wants to know which traffic sources to invest in. The data is the UCI Online Shoppers set, 12,330 sessions with a traffic source code and whether each session ended in a purchase (UCI: Online Shoppers). The sources are numbered, not named, as is common in anonymised take-homes.

import pandas as pd
from statsmodels.stats.proportion import proportion_confint

s = pd.read_csv("online_shoppers_intention.csv")               # UCI Online Shoppers Purchasing Intention
print(f"Task: which traffic sources should marketing focus on? ({len(s):,} sessions, {s['Revenue'].mean():.1%} bought)")

by_source = s.groupby("TrafficType")["Revenue"].agg(sessions="size", buyers="sum")
by_source["rate"] = by_source["buyers"] / by_source["sessions"]
low, high = proportion_confint(by_source["buyers"], by_source["sessions"], method="wilson")
by_source["low"], by_source["high"] = low, high

print("\n1. Ranked by conversion rate alone (top 5)")
print(by_source.sort_values("rate", ascending=False).head(5)[["sessions", "buyers", "rate"]].round(3).to_string())

print("\n2. The same ranking with 95% intervals, sources with at least 200 sessions")
big = by_source[by_source["sessions"] >= 200].sort_values("rate", ascending=False)
print(big[["sessions", "rate", "low", "high"]].round(3).to_string())

print("\n3. Where the buyers actually come from")
share = (by_source["buyers"] / by_source["buyers"].sum()).sort_values(ascending=False)
print("   top 3 sources by share of all buyers:", {int(k): f"{v:.1%}" for k, v in share.head(3).items()})

print("\n4. Is source 2's lead the same in every month?")
months = s[s["TrafficType"].isin([1, 2, 3])].pivot_table(index="Month", columns="TrafficType", values="Revenue", aggfunc="mean")
months = months.loc[["Mar", "May", "Nov", "Dec"]]
print(months.round(3).to_string())

Task: which traffic sources should marketing focus on? (12,330 sessions, 15.5% bought)

1. Ranked by conversion rate alone (top 5)
             sessions  buyers   rate
TrafficType                         
16                  3       1  0.333
7                  40      12  0.300
8                 343      95  0.277
20                198      50  0.253
2                3913     847  0.216

2. The same ranking with 95% intervals, sources with at least 200 sessions
             sessions   rate    low   high
TrafficType                               
8                 343  0.277  0.232  0.327
2                3913  0.216  0.204  0.230
5                 260  0.215  0.170  0.269
10                450  0.200  0.166  0.239
11                247  0.190  0.146  0.244
4                1069  0.154  0.134  0.177
6                 444  0.119  0.092  0.153
1                2451  0.107  0.095  0.120
3                2052  0.088  0.076  0.101
13                738  0.058  0.044  0.078

3. Where the buyers actually come from
   top 3 sources by share of all buyers: {2: '44.4%', 1: '13.7%', 3: '9.4%'}

4. Is source 2's lead the same in every month?
TrafficType      1      2      3
Month                           
Mar          0.050  0.171  0.045
May          0.074  0.165  0.067
Nov          0.200  0.301  0.186
Dec          0.086  0.155  0.043

What a strong submission says

Do not lead with the top of the raw ranking. Ranked by conversion rate alone, source 16 came first at 33.3%, from 3 sessions and 1 buyer. Source 7 came second, from 40 sessions. Small groups produce extreme rates by chance, and a candidate who recommends source 16 has failed the task.

Show uncertainty. Wilson intervals, which behave well for small samples and rates near the edges (statsmodels: proportion_confint; Wikipedia: binomial proportion intervals), change the picture. Among sources with at least 200 sessions, source 8 converts best at 27.7%, but its interval runs from 23.2% to 32.7%. Source 2 converts at 21.6%, with a much tighter interval of 20.4% to 23.0%, because it has 3,913 sessions.

Weigh rate against volume. Source 2 brings 44.4% of all buyers. Sources 1 and 3 together bring another 23%, at much lower rates. A recommendation that says "protect source 2, test more spend on source 8, and investigate why sources 1 and 3 convert at about 10%" uses both numbers.

Check that the pattern holds. Source 2 led sources 1 and 3 in each of the four busiest months: in March, 17.1% against 5.0% and 4.5%, and in November 30.1% against 20.0% and 18.6%. A lead that holds month after month is far more convincing than a single annual figure, and it rules out a seasonal mix as the explanation.

Say what you cannot conclude. These are observational data. Source 2's visitors may convert well because of who they are, not because of the channel, so more spend there may bring less-qualified visitors. The honest recommendation ends with an experiment: shift a controlled share of budget and measure.

A four-week preparation plan

1. Week 1: SQL daily, on a real dataset, writing each query from a business question rather than a topic list.
2. Week 2: spreadsheets and pandas, repeating the same tasks in both so you can choose either in the room.
3. Week 3: statistics and charts, focusing on rates, intervals, distributions and choosing the right chart.
4. Week 4: two full take-homes, each presented out loud in ten minutes to someone who will ask awkward questions.

Mistakes that cost offers

• Answering before asking what decision the analysis supports.
• Ranking rates without sample sizes.
• Presenting every chart you made instead of the three that support the recommendation.
• Hiding data problems rather than stating how you handled them.
• Overclaiming causation from observational data.

The skills behind every round are taught in the Data Analytics course.

Sources

• Online Shoppers Purchasing Intention dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository
• statsmodels proportion_confint (statsmodels documentation) — statsmodels
• Binomial proportion confidence interval (Wikipedia) — Wikipedia