Data Analyst Interview Questions: What to Expect and How to Answer (SQL, Excel, Statistics, Case Studies)

What Interviewers Are Really Testing

Behind every question sits one of five checks:

Formats vary by company, but a common path is a recruiter screen, a technical round (often SQL), a take-home task or case study, and a final conversation with the hiring team.

SQL Interview Questions for Data Analysts

SQL is the most common technical screen, so spend the most time here. The examples use two small tables, customers and orders, with eight order rows, one of them (order 107) accidentally duplicated. I ran every query below in SQLite 3.53.1. Syntax for dates and strings differs across MySQL, PostgreSQL and SQL Server, so adapt it to the dialect the company uses.

Q1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation.

sql

SELECT customer_id, SUM(amount) AS total

FROM orders

WHERE order_date >= '2026-02-01'

GROUP BY customer_id

HAVING SUM(amount) > 1000;

Result: customer 3 with 1,700. A strong candidate notices something else: customer 3's real total is 1,300, because order 107 appears twice and inflates the sum. Saying "I would check for duplicates before trusting this" is exactly the habit interviewers want.

Q2. How do you find customers who have never ordered?

Use a LEFT JOIN and keep rows where the right side is NULL.

sql

SELECT c.name

FROM customers c

LEFT JOIN orders o ON o.customer_id = c.customer_id

WHERE o.order_id IS NULL;

Result: Dev. Mention that NOT EXISTS is an alternative, and that NOT IN misbehaves when the subquery returns NULLs.

Q3. How do you find duplicate records?

Group by the key and keep groups with a count above one: GROUP BY order_id HAVING COUNT(*) > 1. It returns order 107 with a count of 2. Then explain what you would do next: ask whether duplicates are a pipeline bug, and remove them with SELECT DISTINCT or a window function.

Q4. How do you find the second-highest value?

Rank the values and filter the rank. Use DENSE_RANK when ties should not leave gaps.

sql

WITH d AS (SELECT DISTINCT order_id, amount FROM orders),

r AS (SELECT order_id, amount,

             DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk

      FROM d)

SELECT order_id, amount FROM r WHERE rnk = 2;

Result: order 102 with 700, because two orders tie at 900 for first place. Know the three ranking functions: ROW_NUMBER gives unique numbers, RANK leaves gaps after ties, and DENSE_RANK does not.

Q5. How do you get the top row per group?

Use ROW_NUMBER with PARTITION BY and keep row 1. On my sample data, the top order is 105 (900) for Books and 104 (900) for Toys. If two rows tie, ROW_NUMBER picks one arbitrarily, so add a second sort column to make the result repeatable.

Q6. How do you calculate month-over-month change?

Aggregate by month, then compare each month with the previous one using LAG.

sql

WITH d AS (SELECT DISTINCT order_id, order_date, amount FROM orders),

m AS (SELECT substr(order_date,1,7) AS month, SUM(amount) AS revenue

      FROM d GROUP BY 1)

SELECT month, revenue,

       revenue - LAG(revenue) OVER (ORDER BY month) AS change

FROM m;

Result: January 800 (change NULL), February 2,500 (+1,700), March 600 (-1,900). Note that the first month has no previous value, so the change is NULL, and say how you would present it.

Q7. What is a window function, and how is it different from GROUP BY?

The PostgreSQL documentation defines it well: "A window function performs a calculation across a set of table rows that are somehow related to the current row." Unlike a grouped aggregate, window functions "do not cause rows to become grouped into a single output row," so you keep one row per input row. In my sample, SUM(amount) OVER (PARTITION BY customer_id) shows each order next to its customer's total (for customer 1, 1,400).

Quick-fire SQL checks

• INNER vs LEFT JOIN: INNER keeps matches only; LEFT keeps every left row.
• UNION vs UNION ALL: UNION removes duplicates; UNION ALL keeps them and is usually faster.
• NULL handling: NULL = NULL is not true, so use IS NULL. COUNT(*) counts rows; COUNT(column) skips NULLs.

For database-choice questions, our guide to SQL vs NoSQL covers the trade-offs.

Excel and BI Questions

Expect practical questions rather than trivia.

• When would you use a pivot table? To summarise and slice data quickly, such as revenue by region and month, without writing formulas.
• What are the limits of VLOOKUP? It looks to the right of the lookup column only and breaks if columns are inserted in some setups. INDEX-MATCH or XLOOKUP (where available) are more flexible.
• How do you choose a chart? Line for trends over time, bar for comparing categories, scatter for relationships. Avoid pie charts with many slices.
• How would you design a dashboard for executives? Start with the decisions they make, show a few KPIs with targets, allow drill-down, and remove anything that does not change a decision.

Tool questions often come up too, so know your options from data analysis tools and the trade-offs in Power BI vs Tableau. For design principles, see data visualization and business intelligence.

Statistics Questions

You rarely need advanced theory, but you must avoid classic mistakes.

What is a p-value? The American Statistical Association describes it as the probability, under a specified statistical model, that a statistical summary of the data would be equal to or more extreme than what was observed. Its statement adds that a p-value does not measure the probability that the hypothesis is true, and that it "does not measure the size of an effect or the importance of a result." In the room: "It tells me how surprising my data would be if there were no real effect, not how likely my idea is to be right."

Type I vs Type II error? Type I is a false positive: you act on an effect that is not real. Type II is a false negative: you miss a real effect.

How would you set up an A/B test? Define the success metric and the minimum change that matters, randomise users, decide the sample size and duration in advance, and avoid stopping early when the result looks good. Check that groups are comparable before reading the result.

Mean or median? Use the median when outliers skew the data, such as order values or incomes.

Correlation or causation? Two metrics can move together because of a third factor or by chance. Randomised experiments are the cleanest way to establish cause.

Python and pandas Questions

Many roles include a short coding task. The usual themes are cleaning, grouping and merging. Here is a compact example, run in pandas 3.0.5, that removes duplicates, fills a missing city and summarises revenue:

python

clean = (orders.drop_duplicates()

               .assign(city=lambda d: d["city"].fillna("Unknown")))

summary = clean.groupby("city", as_index=False)["amount"].agg(orders="count", revenue="sum")

On my sample, that gave Delhi 2 orders and 1,200, Pune 4 and 2,500, and Unknown 1 and 200, after the duplicate row was dropped. Be ready to explain why you filled rather than deleted the missing city, and what you would ask the data owner. Our guide on how to crack a data science interview covers the adjacent Python and ML topics.

Case Study and Business Questions

Interviewers use cases to see how you think. A reliable structure:

1. Clarify. Which metric, which period, compared with what?
2. Check the data. Could tracking, a pipeline or a definition have changed?
3. Segment. Split by time, region, channel, product and new versus returning customers.
4. Find the driver. Is it volume, price or mix? Where in the funnel does it drop?
5. Consider outside causes. Seasonality, promotions, competitors.
6. Recommend. State what you would do next and what would change your mind.

Practice case: "Weekly revenue fell 10%. What do you do?"

These are invented numbers for practice. First confirm the drop is real: compare against the same week last year and check that data loads completed. Then segment. Suppose orders are flat but average order value fell, and the fall is concentrated in one category after a discount ended. Your answer: the decline is a pricing effect, not a demand problem; recommend testing a smaller discount rather than reinstating the old one. Notice that you reached a recommendation and a next test, not just a diagnosis.

Behavioural Questions

Use the STAR method: situation, task, action, result. Prepare three stories you can adapt:

• A time you found an error in data or analysis and what you did.
• A time you disagreed with a stakeholder and reached a decision.
• A time you simplified a complex finding for a non-technical audience.

Keep each story under two minutes and end with the result. If you are new to the field, use college projects or a capstone project, and be specific about your own contribution.

A Four-Week Preparation Plan

Practise on messy data with duplicates and NULLs, not only clean tables, because that is what you will meet at work.

Common Mistakes Candidates Make

• Reporting a number without sanity-checking it. Check duplicates, NULLs and date ranges first.
• Memorising answers. Interviewers change the data, so understand the logic.
• Ignoring the business. A correct query is not an answer until you say what it means.
• Overusing jargon. Explain as if the listener is a manager.
• Not asking clarifying questions. Vague prompts are deliberate.
• Skipping the role fit. Read the job description; some roles lean towards BI, others towards product analytics. Our AI data analyst role and scope guide shows how the job is changing, and the business analyst interview questions will help if you are applying to both.

When you are ready to discuss offers, see data analytics salary in India for context.

TL;DR:

‍ Data analyst interviews test five things: SQL, spreadsheet and BI skills, basic statistics, business reasoning and communication. SQL usually carries the most weight, so practise joins, grouping and window functions on real tables. This guide gives the questions that come up most, model answers, SQL examples tested in SQLite, a worked case, and a four-week plan.

‍