Data Analyst Interview Questions and Answers
Last updated 2026-11-19
A data analyst interview usually has four parts: SQL on a few tables, a spreadsheet or Python data-cleaning task, a statistics or metrics discussion, and a case where you explain a number going up or down. The questions below follow that pattern and each answer is worked through with real numbers or runnable code.
Read each question, write your own answer first, then compare. Interviewers care less about the final query than about how you check the data, state assumptions and explain the result to a non-technical person. For the full process and rounds, see how to crack the data analyst interview.
| Typical rounds | SQL test, case or metrics round, tools round, managerial or HR |
|---|---|
| Core skills | SQL, Excel, pandas, statistics, visualisation |
| Questions here | 14 worked questions |
What gets tested most
- SQL: joins, GROUP BY with HAVING, window functions, date handling and NULL behaviour.
- Data cleaning: duplicates, missing values, wrong types and inconsistent categories.
- Descriptive statistics: mean versus median, spread, outliers, correlation and sampling.
- Metrics thinking: define a metric, break it into parts, find why it moved.
- Communication: one clear recommendation with the evidence behind it.
How to answer a metrics case
Start by confirming the definition of the metric and the time window. Then split it: a revenue number is visits times conversion times average order value, and each part can be cut by channel, device, region or product. Check data quality and recent releases before you propose business causes. End with what you would do next and what would change your mind.
Data Analyst Interview Questions and Answers: questions with worked answers
1 What is the difference between WHERE and HAVING?
easyWHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates. The query below keeps only orders above 500 (row filter), then keeps only customers whose total is above 5,000 (group filter). Putting a row-level condition in WHERE is also faster, because fewer rows reach the aggregation.
sqlSELECT customer_id, SUM(amount) AS total FROM orders WHERE amount > 500 GROUP BY customer_id HAVING SUM(amount) > 5000;2 What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?
easyCOUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. For a column holding 10, 10, NULL, 20 you get 4, 3 and 2 respectively. Mixing these up is a classic cause of wrong customer counts after a join.
3 INNER JOIN versus LEFT JOIN: how would you find customers who never placed an order?
easyINNER JOIN keeps only matching rows. LEFT JOIN keeps every row from the left table and fills the right side with NULL where nothing matches. To find customers with no orders, left join and keep rows where the right key is NULL. This is called an anti-join. NOT EXISTS gives the same result and is safe when the right column can itself contain NULLs.
sqlSELECT c.customer_id, c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL;4 Write a query for the second highest salary in each department.
mediumUse DENSE_RANK over a partition by department, ordered by salary descending, then keep rank 2. DENSE_RANK is the right choice because two people on the top salary should not push the second distinct salary to rank 3, which is what RANK would do. ROW_NUMBER would wrongly pick one of two tied people. If a department has only one distinct salary it returns no row, which you should mention.
sqlSELECT dept, name, salary FROM ( SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk FROM employees ) t WHERE rk = 2;5 How do you compute month-over-month revenue growth in SQL?
mediumAggregate revenue to one row per month, then use LAG to bring in the previous month and compute (current minus previous) divided by previous. If revenue goes from 200 to 230 the growth is 30 / 200 = 15 percent. The first month has no previous value, so LAG returns NULL and the growth is NULL, which is correct. Multiply by 100.0 to avoid integer division.
sqlSELECT month, revenue, ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month), 1) AS mom_pct FROM monthly_revenue;6 Mean or median: which would you report for salaries, and why?
easyMedian, when the data is skewed. Take salaries of 30, 32, 35, 38 and 300 thousand. The sum is 435 so the mean is 87, a figure that describes nobody in the group. The median is the middle value, 35. One extreme value drags the mean but not the median. Report the mean when the distribution is roughly symmetric and you need totals, and show both when they differ a lot, because the gap itself says the data is skewed.
7 How do you detect and treat outliers?
mediumFirst check whether the value is an error or a real extreme. A common rule is the IQR fence. If Q1 is 10 and Q3 is 18, then IQR = 8, the lower fence is 10 - 1.5 x 8 = -2 and the upper fence is 18 + 1.5 x 8 = 30, so any value above 30 is flagged. Then decide: fix a data-entry error, cap (winsorise) the value, analyse with and without it, or keep it if it is genuine, for example a real bulk order. Never delete silently; record what you did.
pythonq1, q3 = df['amount'].quantile([0.25, 0.75]) iqr = q3 - q1 mask = (df['amount'] < q1 - 1.5 * iqr) | (df['amount'] > q3 + 1.5 * iqr) outliers = df[mask]8 Walk through cleaning a messy dataset in pandas.
mediumInspect first: shape, dtypes, null counts and duplicates. Then fix types, standardise text, handle nulls with a stated rule and drop exact duplicates. Decide on nulls by column: drop rows if few and random, fill a numeric column with the median if it is skewed, or fill a category with "Unknown". Validate at the end with simple assertions, such as no negative prices and unique order ids.
pythonimport pandas as pd df = pd.read_csv('orders.csv') print(df.info(), df.isna().sum(), df.duplicated().sum()) df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce') df['city'] = df['city'].str.strip().str.title() df['amount'] = df['amount'].fillna(df['amount'].median()) df = df.drop_duplicates() assert df['order_id'].is_unique and (df['amount'] >= 0).all()9 Explain the difference between correlation and causation with an example.
easyCorrelation measures how two variables move together, from -1 to +1. Causation means a change in one produces a change in the other. Ice-cream sales and drowning incidents are positively correlated because both rise in summer: temperature is a confounder. To argue causation you need a randomised experiment, or a careful design that controls for confounders. In analysis reports, say "is associated with" unless you have run an experiment.
10 A conversion funnel has 10,000 visits, 800 sign-ups and 200 paid users. Compute the rates and say where you would look.
easyVisit to sign-up is 800 / 10,000 = 8 percent. Sign-up to paid is 200 / 800 = 25 percent. Overall visit to paid is 200 / 10,000 = 2 percent, which equals 0.08 x 0.25. The biggest relative drop is at the first step, where 92 percent leave, so test landing page and traffic quality there, but a one-point gain at the second step may be cheaper. Segment by channel before concluding, since a low-intent channel can drag the first step down.
11 Calculate a 7-day moving average of daily sales in SQL and in pandas.
mediumIn SQL use a window frame of the current row and the six rows before it. In pandas use rolling with a window of 7. For both, the first six days use fewer than seven values, so state whether you want partial windows or NULL. Make sure there is exactly one row per day first, otherwise a row-based window will not equal seven calendar days.
sqlSELECT sale_date, sales, AVG(sales) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7 FROM daily_sales;pythondaily = daily.set_index('sale_date').asfreq('D') daily['ma7'] = daily['sales'].rolling(7, min_periods=7).mean()12 Orders dropped 20 percent last week. How would you investigate?
hardConfirm it is real: check the data pipeline, a tracking change or a late-loading table. Then decompose orders = visits x conversion x orders per buyer, and see which part moved. Cut the drop by channel, device, city and product to find where it is concentrated, and compare with the same week last year to remove seasonality. Check releases, price changes, stock-outs, payment failures and competitor or marketing changes. Report the likely cause, your confidence and the next check, not just a list of ideas.
13 How do you choose a chart for a given question?
easyMatch the chart to the comparison. Use a line for change over time, bars for comparing categories, a histogram for one distribution, a scatter plot for the relationship of two numeric variables and a box plot to compare spread across groups. Avoid pie charts beyond three or four slices, avoid truncated bar axes, and label units. Every chart should have a title that states the takeaway, not just the metric name.
14 What is a pivot table and how do you build the same thing in pandas?
easyA pivot table summarises data by putting one field on rows, another on columns and an aggregate in the cells. In pandas use pivot_table. The example gives total sales for each region and product with a grand total row and column, which is what a spreadsheet pivot shows by default.
pythonpt = df.pivot_table(index='region', columns='product', values='sales', aggfunc='sum', fill_value=0, margins=True)
Frequently asked questions
How much SQL is needed for a data analyst interview?
Expect joins, GROUP BY with HAVING, subqueries, CTEs and window functions such as ROW_NUMBER, RANK and LAG. Practise on the SQL interview guide and the window functions page.
Do data analyst interviews ask for Python?
Many do, mostly pandas for cleaning, grouping and merging, and basic plotting. Some roles use only SQL and Excel or a BI tool, so read the job description.
How should I present a case answer?
State the metric definition, break it into drivers, say what data you would pull, and finish with a recommendation and the risk in it. Keep the structure visible so the interviewer can follow.
What statistics topics come up?
Mean, median, standard deviation, percentiles, correlation, sampling bias and basic hypothesis tests. Deeper material is on the statistics interview page.
Related guides
- SQL Window Functions Interview Questions
- Pandas Interview Questions and Answers
- Statistics for Data Science Interview Questions and Answers
- Excel Interview Questions and Answers
- Power BI Interview Questions and Answers
- Business Analyst Interview Questions and Answers
- Product Analyst Interview Questions and Answers
More in Role-wise Interview Questions by Job and Tech Stack
- Data Engineer Interview Questions and Answers
- Data Scientist Interview Questions and Answers
- Deep Learning Interview Questions and Answers
- DevOps Interview Questions and Answers
- Django Interview Questions and Answers (Django 5)
- Docker Interview Questions and Answers
- Ethical Hacking Interview Questions and Answers
- ETL and Data Warehousing Interview Questions and Answers
- Excel Interview Questions and Answers
- Express.js Interview Questions and Answers (Express 5)
- FastAPI Interview Questions and Answers
- Flask Interview Questions and Answers