Data Analyst Interview Questions and Answers: 16 With SQL

Last updated: September 2026

Quick Answer

Data analyst interviews test whether you can pull the right data with SQL, reason about it statistically, and turn it into a decision a non-technical person will act on. Expect a SQL screen (joins, aggregation, window functions), a statistics or A/B testing question, a metrics or “why did this number drop” case, questions on Excel and a BI tool, and behavioral rounds on stakeholders and mistakes. Rehearse your SQL and case answers aloud in OphyAI Interview Practice, which runs technical and case mock interviews; Interview Copilot is for live Zoom, Teams or Meet rounds where the employer allows it.

The 16 questions covered below, and what each one tests:

#	Question	What it tests
1	Walk me through your background	Focus and fit for analytics
2	INNER vs LEFT JOIN: find customers with no orders	Join logic, NULL handling
3	WHERE vs HAVING: find duplicate emails	Aggregation order
4	Top 3 salaries per department	Window functions, ties
5	Month-over-month revenue change	CTEs, LAG, date handling
6	Explain a p-value to a non-statistician	Statistical literacy
7	How would you design and read an A/B test?	Experiment design, peeking
8	Signups dropped 10% this week. What do you do?	Structured diagnosis
9	How would you measure success of a new feature?	Metric design
10	How do you handle missing data and outliers?	Data cleaning judgment
11	How do you use Excel, and XLOOKUP vs VLOOKUP?	Spreadsheet fluency
12	How do you design a dashboard people use?	BI tool and audience thinking
13	Explain a finding to a non-technical stakeholder	Communication
14	Tell me about an analysis that changed a decision	Impact
15	Two reports show different numbers. What now?	Data trust, stakeholders
16	Tell me about a mistake in your analysis	Honesty and process

What Hiring Managers Are Really Evaluating

Most managers score four things: can you get correct data (SQL), can you reason about it without fooling yourself (statistics and skepticism), do you pick metrics that matter to the business, and can you communicate the answer. The US Bureau of Labor Statistics does not publish a single “data analyst” occupation, so the closest published benchmark is its Data Scientists profile: a median annual wage of $120,230 in May 2025 and projected employment growth of 35 percent from 2025 to 2035, with about 24,800 openings a year. Data analyst titles usually pay less than data scientist titles, so treat that figure as an upper reference, not an analyst offer.

A note on the sample answers: they use hypothetical situations, with brackets where your real details go. Swap in your own tools, tables and results. Never borrow a result that is not yours, and do not reveal a former employer’s confidential numbers. The SQL below is written in PostgreSQL-style syntax; most of it also runs in MySQL 8, SQL Server, BigQuery and Snowflake with small changes such as date functions.

The Questions, With Sample Answers

1. “Walk me through your background and why data analytics.”

Why they ask: They want a short story that shows you have done analysis, not just coursework, and that you want this role.

Sample answer: “I have [X years] in analytics, most recently as a [business analyst] at a [subscription company]. I work mostly in SQL and [Tableau], and I own the weekly retention report that the growth team uses to decide where to spend. One project I am proud of: I found that [a specific cohort] churned earlier than the average, which led the team to change [onboarding step], and that cohort’s 30-day retention improved by [Y points]. Before that I studied [degree] and taught myself [Python/SQL] on real datasets. I am applying here because [specific reason: the product, the data scale, the team’s experimentation culture], and I want to work closer to product decisions.”

Weak answers: Listing tools with no outcome, or “I love data” with no example. See our guide to answering tell me about yourself.

Prepare for this with OphyAI: Time your version in Interview Practice; keep it under two minutes and end on the role you want.

2. “What is the difference between INNER JOIN and LEFT JOIN? Find customers who have never placed an order.”

Why they ask: Joins are the most common SQL screen topic, and this checks that you understand how unmatched rows and NULLs behave.

Sample answer: “An INNER JOIN returns only rows with a match in both tables. A LEFT JOIN keeps every row from the left table and fills the right side with NULLs when there is no match. To find customers with no orders, I left join orders to customers and keep the rows where the order side is NULL:

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

I would filter on a column that is never NULL in real orders, like the primary key. I would also mention NOT EXISTS as an equivalent pattern that is often clearer. And if I put a condition on the orders table in the WHERE clause instead of the ON clause, I would silently turn the left join into an inner join, which is a bug I check for.”

Weak answers: Saying “LEFT JOIN gets more rows” with no explanation, using = NULL instead of IS NULL, or filtering the right table in WHERE and losing the unmatched rows.

Prepare for this with OphyAI: Practice saying your query aloud while you write it in Interview Practice; interviewers score your reasoning as much as the syntax.

3. “What is the difference between WHERE and HAVING? Find duplicate emails.”

Why they ask: It tests whether you know the order in which SQL evaluates a query and can do basic data quality checks.

Sample answer: “WHERE filters individual rows before grouping, and HAVING filters groups after aggregation. So I cannot use COUNT(*) in a WHERE clause. To find duplicate emails:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

In practice I would also normalize with LOWER(TRIM(email)) before grouping, because ‘Ann@x.com ’ and ‘ann@x.com’ are the same person in the real world. If the goal were to delete the extras, I would first check which row to keep, usually the earliest created_at, and I would test on a copy of the table before deleting anything.”

Weak answers: Confusing the two clauses, forgetting that GROUP BY needs every non-aggregated column, or proposing a delete without a plan to verify it.

Prepare for this with OphyAI: Rehearse the “row filter vs group filter” one-liner until it is automatic.

4. “Write a query for the top 3 salaries in each department.”

Why they ask: This is the classic window function question. It shows whether you can rank within groups and handle ties.

Sample answer: “I would use a window function inside a subquery, since I cannot filter on a window result in the same SELECT:

SELECT department, employee_id, salary
FROM (
  SELECT department, employee_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS rnk
  FROM employees
) ranked
WHERE rnk <= 3;

I chose DENSE_RANK because people with equal salaries share a rank and the next rank is not skipped, so ‘top 3 salaries’ returns three distinct salary levels. ROW_NUMBER would break ties arbitrarily and RANK would leave gaps after ties. I would confirm with the interviewer whether they mean top 3 people or top 3 salary values, because that changes the function.”

Weak answers: Using LIMIT 3 on the whole table, a correlated subquery that does not scale, or not knowing the difference between RANK, DENSE_RANK and ROW_NUMBER.

Prepare for this with OphyAI: Run this as a technical mock in Interview Practice and get feedback on whether you clarified the tie rule before coding.

5. “Calculate month-over-month revenue growth.”

Why they ask: Time-series questions are everyday analyst work. They test CTEs, date truncation, LAG and division by zero.

Sample answer: “First I aggregate to one row per month, then compare each month to the previous one with LAG:

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month,
       revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1
       ) AS pct_change
FROM monthly
ORDER BY month;

The first month has no prior value, so its change is NULL, which is correct. NULLIF avoids a divide-by-zero. I would also check for months with no orders, because a missing month would make LAG compare against the wrong period, and fill them from a calendar table.”

Weak answers: Self-joining on month-minus-one with brittle date math, ignoring the missing-month problem, or mixing partial and complete months.

Prepare for this with OphyAI: Practice explaining each clause in one sentence as you type; narrating is part of the test.

6. “Explain a p-value to someone who is not a statistician.”

Why they ask: Analysts constantly report significance to people who will misread it. They want accuracy without jargon.

Sample answer: “A p-value answers this: if there were truly no difference between the groups, how often would we see a result at least this extreme just by chance? A p-value of 0.03 means that, if nothing were going on, we would see a gap this big about 3 percent of the time. It does not mean there is a 97 percent chance the change worked, and it does not tell us how big or how valuable the effect is. So I always pair it with the effect size and a confidence interval, for example ‘conversion rose by 0.4 points, with a range of 0.1 to 0.7’, and I ask whether a change that size matters to the business.”

Weak answers: Saying “the probability the hypothesis is true”, treating 0.05 as a magic line, or reporting significance without effect size.

Prepare for this with OphyAI: Practice the plain-English version with a friend or a mock session; if you need jargon, the explanation is not ready.

7. “How would you design and analyze an A/B test?”

Why they ask: Experimentation is central at product companies. They look for planning before launch and discipline afterward.

Sample answer: “Before launch I define one primary metric, for example checkout conversion, plus guardrail metrics like refund rate and page load time. I choose the unit of randomization, usually the user, and estimate the sample size from the baseline rate, the smallest effect worth detecting, my significance level and power, then fix the run time up front, covering at least one full weekly cycle. During the test I check for sample ratio mismatch, meaning the split differs from the plan. I do not stop early because the result looks good, since peeking inflates false positives. At the end I report effect size, a confidence interval and guardrails, and I recommend ship, iterate or stop, noting any segment results as exploratory.”

Weak answers: Choosing several primary metrics after seeing results, stopping when p dips below 0.05, ignoring novelty effects or sample ratio mismatch, or having no sample size plan.

Prepare for this with OphyAI: Run a case-format mock in Interview Practice and have the follow-up questions push on power and peeking.

8. “Signups dropped 10% week over week. How do you investigate?”

Why they ask: This is the most common metrics case. They want a structured diagnosis, not a guess.

Sample answer: “First I confirm the drop is real: is the data pipeline complete, is tracking intact, and how does 10% compare to normal week-to-week variation and seasonality, including holidays? Then I segment to localize it: by platform, country, acquisition channel, new versus returning users, and funnel step, to see whether the drop is in traffic or in conversion. Next I check what changed: a release, a pricing or form change, a marketing campaign ending, an outage, or a competitor event. I would list hypotheses in order of likelihood and cost to check. Finally I quantify impact and report what is known, what is still uncertain, and the next step, such as a rollback or a follow-up test.”

Weak answers: Jumping straight to a cause, never checking whether the data is broken, or listing ideas with no segmentation logic.

Prepare for this with OphyAI: Practice this as a case interview in Interview Practice; interviewers reward you for thinking aloud in a fixed order.

9. “How would you measure the success of a new feature?”

Why they ask: They want to see whether you tie metrics to a goal and anticipate gaming or side effects.

Sample answer: “I start with the goal of the feature. Say it is a saved-search alert meant to bring users back. The primary metric would be the share of users who return within 7 days through an alert, compared with a similar group that did not get alerts, ideally through a holdout. Supporting metrics: adoption rate, alert click-through, and repeat use at 30 days. Guardrails: unsubscribe rate, notification opt-outs and overall session quality, since more alerts can annoy users. I would define the time window and the comparison group before launch, and I would say how I would separate the feature’s effect from natural growth.”

Weak answers: Listing vanity metrics like total clicks, no comparison group, or no guardrails.

Prepare for this with OphyAI: Prepare two or three product examples you know well and practice defining goal, primary metric and guardrails in one minute.

10. “How do you handle missing data and outliers?”

Why they ask: Real data is messy, and your choices can quietly change results. They want judgment, not a default recipe.

Sample answer: “First I ask why the data is missing. Randomly missing values might be safe to drop or impute, but if the missingness is related to the outcome, for example high-value users skipping a field, dropping them biases the result. I measure how much is missing, compare the rows with and without values, and choose between dropping, imputing with the median or a model, or adding a ‘missing’ category. For outliers I check whether they are errors, such as a negative age, or real extreme customers. I would not delete real values just to make a chart look tidy; I would report results with and without them and use the median where the mean is distorted. Whatever I choose, I document it.”

Weak answers: Always deleting rows with nulls, removing outliers without investigating, or never mentioning documentation.

Prepare for this with OphyAI: Have one real cleaning story ready, with the before-and-after effect on the result.

11. “How do you use Excel in your analysis? XLOOKUP versus VLOOKUP?”

Why they ask: Many teams still live in spreadsheets, and an Excel exercise is common for entry-level and finance-adjacent analyst roles.

Sample answer: “I use Excel for quick exploration, ad hoc reconciliations and sharing results with people who do not use BI tools. My core tools are pivot tables, SUMIFS and COUNTIFS, INDEX/MATCH or XLOOKUP, and Power Query for repeatable cleaning. VLOOKUP looks only to the right of the lookup column and breaks when columns are inserted, because it uses a column number. XLOOKUP, available in Microsoft 365 and Excel 2021 and later, can look in any direction, defaults to exact match and lets me set a value for not found. For anything repeated weekly I move the logic to SQL or Power Query so it does not depend on manual steps.”

Weak answers: Only knowing VLOOKUP, hard-coding numbers into formulas, or having no answer for repeatability and errors.

Prepare for this with OphyAI: If an Excel test is likely, ask the recruiter about the format and practice pivots and lookups on a sample dataset with a timer.

12. “How do you design a dashboard that people actually use?”

Why they ask: They are testing audience thinking and BI tool judgment (Tableau, Power BI, Looker), not chart trivia.

Sample answer: “I start by asking who uses it and which decision it supports, because a dashboard without a decision becomes a wall of charts. I put the few headline metrics at the top with a comparison to target or prior period, then supporting breakdowns below. I agree on metric definitions with stakeholders and put them in the dashboard, so ‘active user’ means one thing. I limit filters, choose simple chart types and check the load time. After launch I check who uses it and retire the parts nobody opens. I would rather have one trusted dashboard for the team than five that disagree.”

Weak answers: Cramming every metric on one page, no defined audience, or no answer about definitions and trust.

Prepare for this with OphyAI: Bring a screenshot or description of one dashboard you built, and explain one design choice you changed after user feedback.

13. “Explain a technical finding to a non-technical stakeholder.”

Why they ask: Analysis that no one understands does not change anything. They want a headline, not a methods lecture.

Sample answer: “I lead with the answer and the action, then the evidence. For example: ‘Customers who use the mobile app in their first week are far more likely to still be with us at 90 days, so I recommend we prompt the app install during signup.’ Then one chart, one number to remember, and one caveat in plain words: ‘This shows a link, not proof that the app causes retention, so I suggest a small test first.’ I skip model details unless asked and keep an appendix ready. I check understanding by asking what decision they would make based on what I showed.”

Weak answers: Starting with methodology, burying the recommendation, or overselling correlation as cause.

Prepare for this with OphyAI: Practice a 60-second version of your best project with no jargon, then a 5-minute version.

14. “Tell me about an analysis that changed a decision.”

Why they ask: This is the impact question. They want proof your work reaches decisions. Use the STAR structure (see STAR method examples).

Sample answer: “Situation: our team planned to raise the free-shipping threshold to protect margin. Task: I was asked to estimate the effect on orders. Action: I analyzed [12 months] of orders, found that [a large share] of orders landed within [a small band] just above the current threshold, and built a simple scenario model with a conservative assumption for lost orders. I showed that margin would likely fall, not rise, under those assumptions, and stated the uncertainty. Result: leadership chose a smaller increase and tested it on [a share of traffic] first, and the test confirmed [the direction]. I would fill in the real numbers here and make sure I can defend how I got them.”

Weak answers: Describing the analysis without the decision, claiming credit for a team result, or numbers you cannot explain.

Prepare for this with OphyAI: Rehearse the story in Interview Practice, where follow-up questions will probe your assumptions the way a real interviewer would.

15. “Two reports show different numbers for the same metric. What do you do?”

Why they ask: Conflicting numbers destroy trust in a data team. They want to see calm, systematic reconciliation.

Sample answer: “I do not pick a winner first. I compare definitions: date ranges and time zones, filters, whether test or internal accounts are excluded, and whether refunds or cancellations are counted. Then I check the sources and refresh times, and whether the two reports use different tables or transformations. I reproduce both numbers from raw data and find the first step where they diverge, often in a join that duplicates rows or a filter applied in one place only. Then I tell both report owners what I found, agree on one definition, document it, and fix or retire the wrong report. I also add a check so it does not silently drift again.”

Weak answers: Blaming a tool, choosing the number that looks better, or fixing it quietly without telling stakeholders.

Prepare for this with OphyAI: Prepare a real story of metric reconciliation; it shows up in behavioral and case rounds.

16. “Tell me about a mistake in your analysis.”

Why they ask: Every analyst ships errors eventually. They want honesty, quick correction and a process improvement.

Sample answer: “In a [weekly revenue report], I joined orders to a table that had multiple rows per order, which double-counted revenue for some customers. I noticed when the total did not match finance’s figure. I told my manager the same day, sent a corrected report with a short note on what changed and by how much, and confirmed that no decision had been made on the wrong number. Then I added a row-count check after every join and a reconciliation to the finance total to my process. The lesson: I now validate totals against an independent source before I share. I would use a real example and not exaggerate its size.”

Weak answers: Claiming you have never made a mistake, blaming the data or a colleague, or a fake mistake that costs nothing.

Prepare for this with OphyAI: Practice the timeline: how you found it, who you told, and what changed afterward. See also our guide to common behavioral interview questions.

Entry-Level, Mid-Level and Senior: What Changes

Level	What gets emphasized
Entry-level	SQL fundamentals, Excel, clean explanations, coachability, a portfolio project
Mid-level	Window functions, experiment analysis, metric design, ownership of a domain
Senior	Framing ambiguous questions, influencing roadmaps, mentoring, data quality strategy

Entry-level candidates should bring one end-to-end project: a question, a public or personal dataset, the SQL or Python used, and a recommendation. Senior candidates should expect more open-ended cases and fewer syntax checks.

How to Practice These

Analyst answers improve when you say them aloud, especially SQL reasoning and case structure. Run a technical and a case mock in OphyAI Interview Practice (voice or text, with follow-up questions, a transcript and per-answer feedback), starting with the metric-drop case and your stakeholder stories. Interview Copilot supports live Zoom, Microsoft Teams and Google Meet interviews with real-time suggestions and a live transcript, but it does not cover in-person panels, phone screens or one-way recorded interviews, so check the employer’s rules first. For live coding-style rounds, Coding Interview is a Premium product where you screenshot the problem and get a streamed approach and code. Tailor your resume to the posting with the OphyAI Resume Builder before you apply.

Start practicing →

Frequently Asked Questions

What are the most common data analyst interview questions?

The most common ones are SQL joins, aggregation and window functions, explaining a p-value or A/B test, a “why did this metric drop” case, how you would define success for a feature, and behavioral questions about stakeholders and mistakes. Excel or a BI tool comes up in most rounds, so prepare one example for each area with a real result.

How hard is the SQL in a data analyst interview?

Usually it is intermediate: joins, GROUP BY with HAVING, subqueries or CTEs, and one window function such as ranking or LAG. Entry-level screens may stay with joins and aggregation. The interviewer also watches how you clarify ambiguity, handle NULLs and ties, and check your result, so talk through your approach rather than typing silently.

Do I need Python or R for a data analyst job?

Not always. Many analyst roles rely mainly on SQL, Excel and a BI tool such as Tableau or Power BI, while others list Python or R for cleaning, statistics and automation. Read the posting, and if it says Python, expect a short data-manipulation exercise. If it does not, SQL and clear communication matter more.

What is the difference between a data analyst and a data scientist interview?

Analyst interviews weigh SQL, metrics, dashboards and communication most. Data scientist interviews add more statistics, machine learning and coding depth, and often a modeling case. Titles vary by company, so read the job description. The US Bureau of Labor Statistics profiles data scientists as its closest published category, but individual analyst roles differ widely.

How do I answer data analyst questions with no experience?

Use a project. Pick a public dataset, ask a specific question, clean the data, write the SQL or Python, and finish with a recommendation you can explain in one minute. Coursework, volunteer work, or analysis in a non-analyst job also count. Be honest about scope, and be ready to explain each step you took and why.

Will there be a take-home or live SQL test?

Often yes. Common formats are a live SQL screen of 30 to 60 minutes, a timed online assessment, or a take-home case with a dataset and a short presentation. Formats vary by employer and are not standardized, so ask the recruiter what to expect, and whether you may use your own tools or search documentation.

Can I use an AI interview copilot in a data analyst interview?

Only on live Zoom, Microsoft Teams or Google Meet rounds, and only where the employer allows it. Many technical screens and assessments have their own rules, so ask the recruiter. See our tracker of companies that allow AI in interviews, and use Interview Practice so the reasoning and SQL are your own.

What questions should I ask the interviewer?

Ask what data the team works with and how trusted it is, how metric definitions are agreed, how analysis becomes decisions, what a strong first six months looks like, and how the team handles experiments. Good questions show you care about data quality and impact. See our list of questions to ask the interviewer.

Related Guides

• Business analyst interview questions
• Behavioral interview questions and answers
• Amazon interview guide
• Google interview guide
• Meta interview guide
• Microsoft interview guide
• Capital One interview guide
• Airbnb interview guide
• Uber interview guide

Sources and verification notes

Checked September 2026. Direct page fetching was blocked in this environment (the network proxy denied bls.gov and postgresql.org), so facts below come from search result summaries of the linked pages; verify current figures on the source pages.

• US Bureau of Labor Statistics, Occupational Outlook Handbook: Data Scientists: median annual wage $120,230 in May 2025; projected growth of 35 percent from 2025 to 2035; about 24,800 openings per year. BLS does not list “data analyst” as its own occupation, so this is the closest published benchmark and not an analyst wage.
• US Bureau of Labor Statistics, Occupational Employment and Wage Statistics tables, May 2025: the source for wage data by occupation if you want to check related analyst occupations.
• PostgreSQL documentation: Window Functions: describes ranking functions, peers and LAG behavior used in the sample queries.

The SQL examples are standard patterns written in PostgreSQL-style syntax and were reviewed by hand, not run against a database this session; date functions such as DATE_TRUNC differ in MySQL and SQL Server. Statistical and Excel points (p-value interpretation, sample ratio mismatch, XLOOKUP availability) are widely documented general knowledge and were not separately fetched; confirm against your tool’s documentation. Sample answers are hypothetical templates with placeholder figures, not real cases. Interview formats vary by employer, so confirm with the recruiter.