Data analyst interviews test a specific combination of skills that most candidates do not balance correctly in their preparation. SQL is the most universally tested technical skill across every data analyst interview. Statistics and probability questions appear more often than most candidates expect. Python or Excel data manipulation questions depend on the role. And business case and communication rounds are the ones most candidates are least prepared for despite being the rounds where analysts are actually differentiated.
If you want to practice these questions in a real one-on-one mock interview with an experienced data analyst or engineer, book a mock interview on Intervue.io. The rest of this guide gives you the questions, the answers, and what interviewers are evaluating at each stage.
What a Data Analyst Interview Covers
Data analyst interviews across FAANG, product companies, and IT services firms typically run 4 to 5 rounds.
The SQL technical round is the most universally present round. You write queries live, either in a shared coding environment or on a whiteboard, to answer specific business questions from a given schema. This round appears in every data analyst interview regardless of company size or type.
The Python or Excel round tests data manipulation skills. At product companies and FAANG, Python with pandas is the expectation. At IT services firms and traditional enterprises, Excel proficiency is tested alongside Python.
The statistics and probability round tests foundational analytical thinking: A/B testing, distributions, hypothesis testing, and basic probability. This round is more common at FAANG and data-driven product companies than at IT services firms.
The business case and communication round asks you to analyse a business problem using data, walk through your approach, and present your findings in business language. This is where most candidates lose points because they over-index on technical preparation and underinvest in communication.
The HR and culture round covers career goals, teamwork scenarios, and salary expectations.
SQL Questions
SQL is not just tested on syntax. Interviewers evaluate whether you can think about data and answer business questions with queries. The questions that separate strong candidates are the ones requiring window functions, CTEs, and multi-table joins with business logic applied.
Find the second highest salary in a table
This is one of the most frequently asked SQL interview questions across all levels. It appears in IT services interviews and FAANG interviews alike.
sql
-- Using a subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Using DENSE_RANK (handles ties correctly)
SELECT salary AS second_highest
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;
The subquery approach is simpler but breaks if there are multiple employees with the highest salary. The DENSE_RANK approach handles this correctly. At senior level, interviewers expect the window function approach.
Find users who made purchases on consecutive days
This is a common intermediate SQL interview question at product companies and FAANG. It tests whether you understand self-joins and date arithmetic.
sql
-- Given a table: purchases(user_id, purchase_date)
SELECT DISTINCT p1.user_id
FROM purchases p1
JOIN purchases p2
ON p1.user_id = p2.user_id
AND p2.purchase_date = p1.purchase_date + INTERVAL '1 day';
The self-join pairs each purchase with any purchase the same user made the following day. DISTINCT removes duplicates if a user made multiple consecutive-day pairs.
A follow-up interviewers often ask: "Find users who purchased on at least 3 consecutive days." This requires a different approach using window functions or gaps-and-islands technique:
sql
WITH numbered AS (
SELECT
user_id,
purchase_date,
purchase_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY purchase_date
) * INTERVAL '1 day' AS grp
FROM (
SELECT DISTINCT user_id, purchase_date FROM purchases
) deduped
)
SELECT user_id
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;
The gaps-and-islands trick: subtracting the row number (as an interval) from the date produces the same value for all dates in a consecutive sequence. Group by this value and count.
Calculate a rolling 7-day average of daily sales
Window functions are tested in almost every mid-level and senior data analyst interview.
sql
SELECT
sale_date,
daily_revenue,
AVG(daily_revenue) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS rolling_7day_avg
FROM daily_sales
ORDER BY sale_date;
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes the current row and the 6 rows before it in the window, giving exactly 7 rows for a full rolling average.
Common follow-up: "What if you want only days that have a full 7 days of prior data?" Add a WHERE or HAVING clause filtering on the row number or date range.
Write a query to find the retention rate
Retention rate questions appear at product companies and FAANG data analyst interviews. They require understanding how to track cohorts across time.
sql
-- Given: user_activity(user_id, activity_date)
-- Find the percentage of users from week 1 who were also active in week 2
WITH week1_users AS (
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_date BETWEEN '2024-01-01' AND '2024-01-07'
),
week2_users AS (
SELECT DISTINCT user_id
FROM user_activity
WHERE activity_date BETWEEN '2024-01-08' AND '2024-01-14'
)
SELECT
COUNT(w2.user_id) * 100.0 / COUNT(w1.user_id) AS retention_rate
FROM week1_users w1
LEFT JOIN week2_users w2 ON w1.user_id = w2.user_id;
The LEFT JOIN keeps all week 1 users in the denominator, including those who did not return in week 2. COUNT(w2.user_id) counts only users who appear in both weeks because COUNT ignores NULLs.
Python and Pandas Questions
How do you handle missing values in a pandas DataFrame?
Missing values in pandas are represented as NaN (Not a Number). Before deciding how to handle them, always investigate: how many are missing, is the missingness random or systematic, and what does the downstream analysis need.
python
import pandas as pd
import numpy as np
df = pd.DataFrame({
'age': [25, np.nan, 30, np.nan, 35],
'salary': [50000, 60000, np.nan, 80000, 90000]
})
# Check missing values
print(df.isnull().sum())
# Drop rows where any column has missing values
df_dropped = df.dropna()
# Drop rows only if all columns are missing
df_dropped_all = df.dropna(how='all')
# Fill with a specific value
df_filled = df.fillna(0)
# Fill with the column mean (common for numerical columns)
df['age'] = df['age'].fillna(df['age'].mean())
# Forward fill: use the previous valid value
df_ffill = df.fillna(method='ffill')
In an interview, always explain why you chose a particular strategy. Filling with the mean is appropriate for numerical data with random missingness. Dropping rows is appropriate when the percentage of missing data is small. Forward fill is appropriate for time series data where missing values represent "no change."
How do you merge two DataFrames and what are the different join types?
python
import pandas as pd
orders = pd.DataFrame({
'order_id': [1, 2, 3],
'customer_id': [101, 102, 101],
'amount': [50, 30, 70]
})
customers = pd.DataFrame({
'customer_id': [101, 103],
'name': ['Alice', 'Charlie']
})
# Inner join: only matching rows from both DataFrames
inner = pd.merge(orders, customers, on='customer_id', how='inner')
# Result: orders for customer 101 only (customer 102 has no match)
# Left join: all rows from orders, matching rows from customers
left = pd.merge(orders, customers, on='customer_id', how='left')
# Result: all orders; customer 102 has NaN for name
# Outer join: all rows from both, NaN where no match
outer = pd.merge(orders, customers, on='customer_id', how='outer')
This maps directly to SQL joins: inner join, left join, right join, and full outer join. Knowing both the pandas and SQL syntax for the same concept is a strong signal in data analyst interviews.
Statistics and Probability Questions
What is the difference between Type I and Type II errors?
A Type I error (false positive) occurs when you reject a null hypothesis that is actually true. You concluded there is an effect when there is none. In an A/B test, a Type I error means declaring a new feature as a winner when it actually makes no difference.
A Type II error (false negative) occurs when you fail to reject a null hypothesis that is actually false. You concluded there is no effect when there actually is one. In an A/B test, a Type II error means failing to detect a genuinely better feature.
The significance level (alpha) controls the Type I error rate: if you set alpha to 0.05, you accept a 5% chance of a false positive. Statistical power controls the Type II error rate: higher power means lower probability of missing a real effect.
The tradeoff: reducing alpha (accepting fewer false positives) increases the risk of false negatives unless you also increase sample size. This is why data analysts at product companies need to plan experiment sample sizes before running A/B tests.
How would you design an A/B test for a new feature?
This is one of the most commonly asked business-analytical questions in data analyst interviews at product companies and FAANG.
Define the hypothesis. What is the null hypothesis (the feature has no effect on the metric) and the alternative hypothesis (the feature increases the metric)?
Choose the primary metric. For a checkout flow change, the primary metric might be conversion rate. Choose one primary metric and stick with it to avoid p-hacking from testing multiple metrics.
Determine sample size. Use a power analysis to determine how many users you need in each group to detect a meaningful effect. You need to specify the minimum detectable effect (the smallest improvement that would be business-significant), the significance level (typically 0.05), and the desired power (typically 0.80).
Randomise correctly. Assign users to control or treatment randomly. Ensure there is no leakage (users in both groups) and no novelty effect (users behaving differently just because something is new).
Run for a full business cycle. At minimum, run for one full week to account for day-of-week effects. Stopping early when results look significant (peeking) inflates the false positive rate.
Analyse results. Compare the primary metric between groups using the appropriate statistical test (typically a two-proportion z-test for conversion rates or a t-test for continuous metrics). Report the confidence interval around the effect size, not just the p-value.
What is the central limit theorem and why does it matter for data analysis?
The central limit theorem states that the sampling distribution of the mean of any population approaches a normal distribution as the sample size increases, regardless of the shape of the original population distribution.
Why it matters: most statistical tests (t-tests, z-tests, confidence intervals) assume normally distributed data or sampling distributions. The central limit theorem is the justification for applying these tests even when the underlying data is not normally distributed, as long as the sample size is large enough (typically n > 30 is the common rule of thumb).
In A/B testing: even if individual user conversion events are Bernoulli (0 or 1), the mean conversion rate across many users is approximately normally distributed, which is why we can use z-tests to compare conversion rates.
The Business Case Round: What Most Candidates Miss
The business case round is the most differentiating round in data analyst interviews and the one most candidates are least prepared for.
The interviewer describes a business problem and asks how you would approach it using data. The trap most candidates fall into is jumping straight to the technical approach: "I would write a SQL query to pull X and then calculate Y." This misses the point.
What interviewers are evaluating is whether you think like a business analyst, not just a technical analyst. The right approach:
First, clarify the business objective. What decision is this analysis meant to inform? What does success look like for the business?
Second, identify the metrics that measure that objective. If the goal is to improve user retention, what is the specific metric: 7-day retention, 30-day retention, monthly active users?
Third, identify the data needed and its limitations. What data exists? What might be missing? What biases could affect the analysis?
Fourth, describe the analysis approach. Now you go technical: the queries, the cohort analysis, the statistical test.
Fifth, describe how you would present the findings. Who is the audience? What format communicates the insight most clearly? What action do you want them to take?
Candidates who only do step four receive feedback that they are technically competent but not business-oriented. At data analyst roles, business orientation is the primary differentiator at mid-level and above.
What Interviewers Score in Data Analyst Interviews
In SQL rounds, they score whether you can translate a business question into a query without being given the schema of the query. The question "find our most valuable customers" requires you to first ask what valuable means before writing a single line of SQL.
In statistics rounds, they score conceptual clarity. Can you explain p-value and confidence interval in plain English without using statistical jargon? Can you identify when an A/B test result is misleading due to a short runtime or biased randomisation?
In business case rounds, they score structured thinking. Do you start from the business objective or from the data? Do you identify the limitations of your analysis before presenting the results?
At senior level, they score communication. Can you translate your analysis into a recommendation that a non-technical stakeholder could act on? Analysts who produce correct analysis but cannot communicate it clearly to decision-makers rarely advance past mid-level.
FAQs
Is SQL the most important skill for data analyst interviews? Yes, across every type of company. SQL is tested in every data analyst interview from IT services to FAANG. Window functions, CTEs, and complex joins are tested from mid-level upward. If you are preparing for a data analyst interview and have limited time, SQL is where to start.
Do data analyst interviews at FAANG require Python? Yes. FAANG data analyst interviews expect Python with pandas for data manipulation. At IT services companies, Excel proficiency is often sufficient for entry-level roles. At product startups, Python is expected from the start.
How much statistics knowledge is required? For entry-level roles at IT services companies, basic descriptive statistics (mean, median, standard deviation) is sufficient. For product companies and FAANG, A/B testing design, hypothesis testing, Type I and Type II errors, and the central limit theorem are all expected.
What is the most common SQL mistake in data analyst interviews? Using COUNT() when the question requires COUNT(DISTINCT column). If asked "how many customers made a purchase last month," COUNT() counts purchases, not customers. COUNT(DISTINCT customer_id) counts unique customers. This distinction comes up in almost every SQL round.
How do I prepare for the business case round? Practice reading business scenarios and writing out the five steps: objective, metric, data, analysis, presentation, before touching SQL or Python. The business case round evaluates structured thinking, not just technical skill.
Summary
Data analyst interviews test SQL depth, Python or Excel data manipulation, statistics and probability fundamentals, and the ability to translate business problems into data questions and back into business recommendations. SQL is the most universally tested skill. The business case round is the most differentiating round and the one most candidates underinvest in preparing for.
Book a data analyst mock interview on Intervue.io to practice with an engineer who knows what the bar looks like at the company you are targeting and will give you specific feedback on where your answers need more depth.
Visit intervue.io
