Part 1: SQL fundamentals (1-10)

1. What is the difference between INNER, LEFT, RIGHT and FULL OUTER JOIN?

INNER JOIN keeps only rows with a match on both sides.
LEFT JOIN keeps every row from the left table; unmatched right-side columns come back NULL.
RIGHT JOIN is the mirror image. Most teams never write it - flip the table order and use LEFT so queries read the same way.
FULL OUTER JOIN keeps every row from both sides, NULL-filling whichever side did not match.

The follow-up is almost always: "what happens to your row count?" A join can multiply rows when the join key is not unique on one side. If orders joins payments and an order has two payments, that order now appears twice, and any SUM(order_total) after the join is wrong. Say out loud what the grain of each table is (one row per what?) before you join. That single habit prevents most analyst SQL bugs.

2. Why does putting a filter in the WHERE clause turn a LEFT JOIN into an INNER JOIN?

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';

For customers with no orders, o.status is NULL, and NULL = 'shipped' is not true, so WHERE drops them. You asked for "all customers" and silently got "customers with a shipped order". Move the condition into the join to keep unmatched customers:

LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'shipped'

Interviewers like this one because it produces a plausible-looking wrong answer rather than an error.

3. What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation; HAVING filters groups after aggregation. You cannot reference COUNT(*) in WHERE because the count does not exist yet.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'      -- row filter
GROUP BY department_id
HAVING AVG(salary) > 100000;         -- group filter

The logical order of evaluation is FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. That order explains why most engines will not let you use a SELECT alias in WHERE (it is not computed yet), though some, such as PostgreSQL, allow aliases in ORDER BY.

4. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows, including rows where every column is NULL.
COUNT(column) counts rows where that column is not NULL.
COUNT(DISTINCT column) counts unique non-NULL values.

This matters more than it looks. "How many users signed up?" on a table with one row per signup event and a nullable user_id gives three different answers depending on which COUNT you wrote. The follow-up: AVG(column) also ignores NULLs, so the average of (10, NULL, 20) is 15, not 10. If NULL means "zero" in your business logic, write AVG(COALESCE(column, 0)) and say why.

5. How do NULLs behave in comparisons and why is NOT IN dangerous?

NULL means "unknown", so any comparison with it (=, <>, >) returns unknown, which WHERE treats as false. Use IS NULL / IS NOT NULL.

The classic trap:

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM churned);

If churned.customer_id contains even one NULL, this returns zero rows, because x NOT IN (1, 2, NULL) expands to x <> 1 AND x <> 2 AND x <> NULL, and the last term is unknown. Use NOT EXISTS instead, which handles NULLs the way you expect. The NULL handling topic has exercises built around exactly this.

6. What does COALESCE do, and when is NULLIF useful?

COALESCE(a, b, c) returns the first non-NULL argument. Use it for display defaults and for treating missing values as zero when that is genuinely correct.

NULLIF(a, b) returns NULL when a = b, otherwise a. Its main analyst use is avoiding divide-by-zero:

SELECT campaign_id,
       clicks * 1.0 / NULLIF(impressions, 0) AS ctr
FROM campaign_stats;

Note the * 1.0: in engines such as PostgreSQL and SQL Server, integer divided by integer is integer division, so 3 / 4 is 0. That bug ships to dashboards more often than anyone admits.

7. How do you write a CASE WHEN, and how do you use it inside an aggregate?

CASE is SQL's if/else. Its most useful form for analysts is conditional aggregation, which pivots rows into columns in one pass:

SELECT order_month,
       COUNT(*) AS orders,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunds,
       SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) * 1.0
         / COUNT(*) AS refund_rate
FROM orders
GROUP BY order_month;

Gotcha: CASE returns NULL when no branch matches and there is no ELSE. Inside SUM that is harmless; inside COUNT(CASE WHEN ... THEN 1 END) it is actually the idiom. PostgreSQL also supports COUNT(*) FILTER (WHERE status = 'refunded'), which reads more cleanly. Practise it in the CASE WHEN topic.

8. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows across the combined result, which costs a sort or hash. UNION ALL keeps everything and is faster. Default to UNION ALL and only use UNION when you have a reason to deduplicate - and if you are relying on UNION to deduplicate, ask why duplicates exist in the first place.

Both require the same number of columns with compatible types, matched by position, not name. Swapping two columns in one branch produces valid SQL and garbage data.

9. What is a subquery vs a CTE, and when would you choose each?

A subquery is a query nested inside another. A CTE (WITH name AS (...)) names a subquery and lets you reference it below, which makes multi-step logic readable top to bottom:

WITH first_orders AS (
  SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT DATE_TRUNC('month', first_order_date) AS cohort_month,
       COUNT(*) AS new_customers
FROM first_orders
GROUP BY 1;

In modern engines a CTE is usually inlined and optimised like a subquery, so choose for readability. PostgreSQL before version 12 always materialised CTEs, which could hurt performance; since 12 it inlines them unless you write MATERIALIZED. CTEs can also be recursive, which is how you walk hierarchies such as org charts.

10. How do you find duplicate rows?

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

The follow-up is "what counts as a duplicate?" Pat@x.com and pat@x.com (trailing space) are the same person to the business and different strings to SQL. Normalise first with LOWER(TRIM(email)), and say that you would ask whether duplicates are a data bug to fix upstream or a modelling fact to handle.

Part 2: Intermediate and advanced SQL (11-20)

11. What are window functions, and how are they different from GROUP BY?

GROUP BY collapses rows into one row per group. A window function computes across a set of related rows but keeps every row:

SELECT employee_id, department_id, salary,
       AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;

PARTITION BY defines the group, ORDER BY inside OVER defines sequence, and an optional frame (ROWS BETWEEN ...) defines which rows count. Window functions cannot appear in WHERE because they are computed after it; wrap the query in a CTE and filter outside. Snowflake, BigQuery, Databricks and DuckDB offer QUALIFY to filter on a window result directly; PostgreSQL and MySQL do not.

12. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?

For salaries 100, 90, 90, 80:

Function	Result
`ROW_NUMBER()`	1, 2, 3, 4 (ties broken arbitrarily)
`RANK()`	1, 2, 2, 4 (gap after tie)
`DENSE_RANK()`	1, 2, 2, 3 (no gap)

"Second-highest salary" depends on which you pick: DENSE_RANK = 2 returns 90; ROW_NUMBER = 2 returns one of the 90s at random. Ask the interviewer how ties should be treated - that question is half the point. And make ROW_NUMBER deterministic by adding a tie-breaker to the ORDER BY (ORDER BY salary DESC, employee_id).

13. How do you get the top N rows per group?

WITH ranked AS (
  SELECT product_id, category, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY category ORDER BY revenue DESC
         ) AS rn
  FROM product_revenue
)
SELECT product_id, category, revenue
FROM ranked
WHERE rn <= 3;

This is probably the single most common analyst SQL question. Swap ROW_NUMBER for DENSE_RANK if ties at the cutoff should all be included, and say which you chose.

14. How do you deduplicate a table, keeping the latest record per key?

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY updated_at DESC, ingested_at DESC
         ) AS rn
  FROM customer_snapshots
)
SELECT * FROM ranked WHERE rn = 1;

PostgreSQL has a shorthand, SELECT DISTINCT ON (customer_id) * ... ORDER BY customer_id, updated_at DESC. The follow-up: what if two rows share the same updated_at? Add a second sort key so the result is reproducible run to run. A dedup that picks a different row each night makes a dashboard flicker and erodes trust fast.

15. How do you compute a running total and a 7-day moving average?

SELECT order_date, daily_revenue,
       SUM(daily_revenue) OVER (ORDER BY order_date) AS running_total,
       AVG(daily_revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS ma_7d
FROM daily_sales;

Gotcha: ROWS BETWEEN 6 PRECEDING means "the previous six rows", not "the previous six days". If a day has no sales and therefore no row, the window silently spans more than seven days. Fix it by joining to a calendar table (or generate_series in PostgreSQL) so every date exists. The first six rows also average fewer than seven days; say whether you would show or suppress them.

16. How do you compare a value to the previous period with LAG?

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month)) * 1.0
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_growth
FROM monthly_revenue;

LEAD looks forward. Both take an offset (LAG(revenue, 12) for year over year on monthly data) and a default for the first row. The same caveat as Q15 applies: LAG uses the previous row, so a missing month makes "month over month" compare against two months ago.

17. Write a query for monthly cohort retention.

WITH firsts AS (
  SELECT user_id,
         DATE_TRUNC('month', MIN(activity_date)) AS cohort_month
  FROM activity
  GROUP BY user_id
),
activity_months AS (
  SELECT DISTINCT a.user_id, f.cohort_month,
         DATE_TRUNC('month', a.activity_date) AS active_month
  FROM activity a
  JOIN firsts f ON f.user_id = a.user_id
)
SELECT cohort_month,
       (EXTRACT(YEAR FROM active_month) - EXTRACT(YEAR FROM cohort_month)) * 12
     + (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
         AS month_number,
       COUNT(DISTINCT user_id) AS active_users
FROM activity_months
GROUP BY 1, 2
ORDER BY 1, 2;

Divide each row by the month-0 count for the cohort (a window FIRST_VALUE or a join back) to get retention percentages. Date functions vary by dialect; say which one you are writing. The real follow-ups: what counts as "active", and are the most recent cohorts incomplete because they have not had time to reach month 3?

18. How do you calculate a funnel conversion rate in SQL?

SELECT
  COUNT(DISTINCT CASE WHEN event = 'view_product' THEN user_id END) AS viewed,
  COUNT(DISTINCT CASE WHEN event = 'add_to_cart'  THEN user_id END) AS carted,
  COUNT(DISTINCT CASE WHEN event = 'purchase'     THEN user_id END) AS purchased
FROM events
WHERE event_date >= DATE '2026-09-01';

This counts users who did each step at all, not users who did them in order. For a strict funnel, find each user's first timestamp per step and require cart_time > view_time, and usually a window ("purchase within 7 days of view"). State which definition you are using - the strict one gives lower numbers, and stakeholders will compare them to a tool that uses the other.

The "gaps and islands" trick: subtract a row number from the date. Consecutive dates produce the same difference.

WITH d AS (
  SELECT DISTINCT user_id, login_date FROM logins
),
grp AS (
  SELECT user_id, login_date,
         login_date - (ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ))::int AS streak_key
  FROM d
)
SELECT user_id, MIN(login_date) AS start_date,
       MAX(login_date) AS end_date, COUNT(*) AS streak_len
FROM grp
GROUP BY user_id, streak_key;

That date-minus-integer syntax is PostgreSQL; other dialects use DATEADD or DATE_SUB. The DISTINCT first matters: two logins on one day would break the arithmetic.

20. A query is slow. What do you check first?

Read the plan (EXPLAIN / EXPLAIN ANALYZE in PostgreSQL, the query profile in a warehouse). Look for full scans of huge tables and row-count explosions after joins.
Filter early on partition or cluster columns. In a warehouse, a date filter on the partition column can cut the scanned data by orders of magnitude, and on scan-priced engines it cuts the bill too.
Select only the columns you need. Columnar warehouses read per column, so SELECT * is genuinely more expensive.
Check for accidental fan-out - a many-to-many join that multiplies rows before a GROUP BY.
Avoid functions on indexed or partitioned columns in WHERE (WHERE DATE(created_at) = ...); rewrite as a range.

The SQL query tuning cheat sheet has the full checklist.

Part 3: Excel and Google Sheets (21-26)

21. What is the difference between VLOOKUP, INDEX/MATCH and XLOOKUP?

	VLOOKUP	INDEX/MATCH	XLOOKUP
Look left of the key	No	Yes	Yes
Breaks when columns are inserted	Yes (hard-coded column number)	No	No
Default match	Approximate (unless last arg is FALSE)	Depends on MATCH type	Exact
Built-in "not found" value	No (wrap in IFERROR)	No	Yes
Availability	Everywhere	Everywhere	Excel 2021 / Microsoft 365, Google Sheets

The gotcha everyone gets asked about: VLOOKUP's fourth argument defaults to approximate match, which on unsorted data returns a wrong value instead of an error. Always pass FALSE (or 0). INDEX/MATCH is still worth knowing because older Excel versions and many inherited workbooks use it.

22. When would you use a pivot table, and what are its limits?

A pivot table is the fastest way to answer "sum or count this by that" on a flat table: drag dimensions to rows and columns, a measure to values, filters to the filter area. Use it for exploration and quick summaries.

Limits worth naming: it summarises only what is in the source range (new rows need the range extended or a table/named range as the source), calculated fields compute on summed values rather than row by row (so a ratio field gives the ratio of sums, which is usually what you want, but not always), and refreshes are manual by default in Excel. Once the same pivot is rebuilt every week, it belongs in SQL or a BI tool.

23. What is the difference between relative, absolute and mixed cell references?

A1 is relative: copy it one row down and it becomes A2. $A$1 is absolute: it never moves. $A1 locks the column, A$1 locks the row. Mixed references are what make a multiplication table or a "rate times quantity" grid work from a single formula copied across.

The follow-up is usually a broken spreadsheet where someone forgot the $ on a tax-rate cell and every row below the first refers to an empty cell. Being able to spot that in a screen-share is the actual skill.

24. How would you do conditional counting and summing in a spreadsheet?

COUNTIF(range, criteria) and SUMIF(range, criteria, sum_range) handle one condition; COUNTIFS and SUMIFS handle several:

=SUMIFS(D:D, B:B, "West", C:C, ">=2026-01-01")

SUMPRODUCT handles logic the IFS functions cannot, such as OR conditions or conditions on computed values. In current Excel and in Sheets, FILTER, UNIQUE and SORT return dynamic arrays and replace many helper columns. Sheets also has QUERY, which runs a SQL-like language over a range - handy, but its syntax is not standard SQL, so do not assume your SQL transfers one to one.

25. How do you clean a messy column in Excel or Sheets?

Typical tools: TRIM for stray spaces (including the non-breaking spaces from web copy-paste, which need SUBSTITUTE(A1, CHAR(160), " ") first), PROPER/UPPER/LOWER for case, VALUE or DATEVALUE to turn text that looks like numbers or dates into real ones, Text to Columns or SPLIT to break fields apart, and Remove Duplicates.

Mention Power Query (Get & Transform) in Excel: it records cleaning steps so next month's file goes through the same pipeline with one refresh instead of a repeat of manual edits. The meta-answer interviewers want: never clean the raw data in place. Keep the original, clean into a new sheet or query, and make the steps repeatable.

26. When should an analysis move out of a spreadsheet?

When any of these is true: the data no longer fits comfortably (Excel's hard limit is 1,048,576 rows per sheet, and performance degrades well before that), several people need the same number and are getting different ones from their own copies, the analysis is rerun on a schedule, or the logic needs version control and review. At that point the source of truth should be a SQL query or a modelled table, with the spreadsheet as a consumer, not the system of record.

Saying this well signals you have watched a "final_v3_REAL.xlsx" cause a bad decision.

Part 4: Statistics (27-34)

27. When would you report the median instead of the mean?

When the distribution is skewed or has outliers. Revenue per customer, order value, session length, salaries and house prices are all right-skewed: a few huge values pull the mean up, so "the average customer spends $120" may describe almost nobody. The median is the middle value and is robust to outliers.

Report both when they differ a lot - the gap itself tells the reader the distribution is skewed. In SQL, PostgreSQL uses PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY order_value); warehouses have MEDIAN() or APPROX_QUANTILES. The follow-up: "the mean went up but the median did not - what happened?" Likely a few large customers, not broad growth.

28. What is standard deviation, and what is standard error?

Standard deviation measures how spread out individual values are. Standard error measures how precisely you have estimated a statistic, usually a mean: SE = SD / sqrt(n). As the sample grows, the standard deviation of the data stays roughly the same, but the standard error shrinks.

That distinction is the whole basis of confidence intervals and A/B tests. A metric with a high standard deviation (revenue per user) needs a much bigger sample to detect the same change than a low-variance one (did the user click, yes or no).

29. Name some common distributions and where an analyst meets them.

Normal - symmetric bell curve; sample means of many independent observations are approximately normal (central limit theorem), which is why t-tests work on non-normal data at decent sample sizes.
Binomial - number of successes in n yes/no trials; conversions out of visitors.
Poisson - counts of events in a fixed interval when events occur independently at a constant rate; support tickets per hour.
Log-normal / long-tailed - revenue, session duration, file sizes. Often analysed after a log transform.
Uniform - every value equally likely; good random assignment in an experiment should look like this across buckets.

The point is not reciting formulas. It is recognising that "average revenue per user" will not behave like a bell curve and choosing tools accordingly.

30. Explain a p-value in plain words.

A p-value is the probability of seeing a result at least as extreme as the one you observed, assuming there is actually no effect. A p-value of 0.03 means: if the change truly did nothing, data this lopsided would show up about 3% of the time.

What it is not: it is not the probability that the null hypothesis is true, not the probability your result is a fluke in some general sense, and not a measure of how big or important the effect is. A tiny, useless effect can have a very small p-value with enough data. The interviewer is checking that you will not tell a VP "there is a 97% chance the new button works". Practise the wording in the statistics question bank.

31. What is a confidence interval, and how do you explain it to a stakeholder?

A 95% confidence interval is a range produced by a method that, over many repeated samples, captures the true value 95% of the time. For a stakeholder: "Our best estimate is a 4% lift, and the data is consistent with anything from about 1% to 7%."

The interval is more useful than a p-value because it shows effect size and uncertainty together. If the interval for a lift is +0.1% to +0.3%, the result is "significant" but might not be worth shipping. If it is -2% to +10%, the test was too small to say much. Width shrinks with roughly the square root of sample size, so halving the width takes about four times the data.

32. What is the difference between correlation and causation? Give an example.

Correlation means two variables move together. Causation means changing one changes the other. Correlation can come from a confounder (a third variable driving both), reverse causation, or chance.

Analyst-flavoured example: users who use the mobile app have higher retention. Did the app cause retention, or do already-engaged users install the app? Pushing everyone to install it may change nothing. Ways to get closer to causation: a randomised experiment (best), or quasi-experimental methods such as difference-in-differences, regression discontinuity or matching when you cannot randomise. Name at least one and its assumption.

33. What is Simpson's paradox?

A trend that appears in every subgroup reverses (or disappears) when the groups are combined, because the groups are different sizes and have different baselines. The textbook case is the 1973 UC Berkeley graduate admissions data: overall, men were admitted at a higher rate than women, but within most individual departments the gap disappeared or favoured women, because women applied disproportionately to more competitive departments.

Analyst version: conversion improves on both mobile and desktop after a release, yet total conversion drops - because the traffic mix shifted toward mobile, which converts lower. The lesson: before reporting a blended metric, check whether the mix of segments changed. "Mix shift" is the phrase interviewers are listening for.

34. What is the difference between Type I and Type II errors, and what is statistical power?

Type I error (false positive) - concluding there is an effect when there is none. Controlled by alpha, commonly 0.05.
Type II error (false negative) - missing a real effect. Its probability is beta.
Power = 1 - beta - the probability of detecting an effect of a given size if it exists. 80% is a common target.

Power depends on sample size, the variance of the metric, alpha, and the size of the effect you care about. The practical takeaway: an underpowered test that comes back "not significant" does not prove the change did nothing. It mostly proves the test was too small.

Part 5: A/B testing and experimentation (35-39)

35. Walk me through how you would design an A/B test.

Hypothesis: "Showing shipping cost on the product page will raise checkout completion, because surprise costs cause abandonment."
Primary metric chosen before launch, plus guardrail metrics (revenue per visitor, refund rate, page load time) that must not get worse.
Randomisation unit: usually the user, not the session or pageview, so one person sees a consistent experience.
Minimum detectable effect and sample size from a power calculation (Q36), which sets the duration.
Run for whole weeks to cover weekday/weekend cycles, and do not stop early because it looks good.
Analyse: check sample ratio first (Q37), then the primary metric with its confidence interval, then guardrails and pre-planned segments.

The A/B testing question bank drills each step.

36. How do you decide sample size and test duration?

You need the baseline rate (or mean and variance) of the metric, the minimum effect worth detecting, alpha (often 0.05) and power (often 80%). A useful rule of thumb for those defaults is roughly n ≈ 16σ² / δ² per group, where σ² is the metric's variance and δ the absolute difference you want to detect. For a conversion rate p, σ² = p(1 - p).

Example: baseline conversion 5%, want to detect a move to 5.5%. σ² = 0.05 × 0.95 = 0.0475, δ = 0.005, so n ≈ 16 × 0.0475 / 0.000025 ≈ 30,400 users per group. Divide by daily eligible traffic for duration, then round up to whole weeks. The follow-up: halving the detectable effect quadruples the sample size, which is why "can we detect a 0.1% change?" is often answered "not with our traffic".

37. What is a sample ratio mismatch and why check it first?

If you split traffic 50/50 but get 50,000 users in control and 48,500 in treatment, something is wrong with assignment or logging. A chi-square goodness-of-fit test on the counts tells you whether the imbalance is plausibly chance. With large samples, even a small imbalance can be a highly significant red flag.

Common causes: the treatment crashes or redirects before the logging event fires, bots filtered differently between arms, or a bug in the assignment hash. When SRM is present, the metric results are not trustworthy, however good they look - fix the pipeline and rerun. Interviewers ask this to see whether you validate data before celebrating.

38. What is peeking, and what about testing many metrics at once?

Peeking is checking significance repeatedly and stopping the first time p < 0.05. Because you get many chances to cross the threshold by luck, the real false-positive rate ends up well above 5%. Fixes: commit to a sample size up front, or use a method built for continuous monitoring (sequential testing or always-valid inference, which many experimentation platforms offer).

Multiple comparisons: test 20 independent metrics at alpha 0.05 and you expect about one false positive even if nothing changed. Designate one primary metric, treat the rest as exploratory, and use a correction (Bonferroni, or Benjamini-Hochberg for controlling the false discovery rate) when several metrics or segments genuinely drive the decision.

39. The test is significant on the primary metric but a guardrail got worse. What do you do?

Do not average the two into a feeling. Quantify both: "Checkout completion +2% (CI +0.8% to +3.2%), refunds +0.5 points (CI +0.1 to +0.9)." Then translate to the unit the business decides in, usually money or users: does the extra revenue from completion exceed the cost of the extra refunds? Check whether the guardrail move is concentrated in one segment that could be excluded or fixed. Then make a recommendation and say what you would want to see in a follow-up test.

The worst answers are "it is significant, ship it" and "results are mixed, more research needed" with no number attached. For a worked version, try the ambiguous A/B test results case.

Part 6: Data cleaning, metrics and BI (40-45)

40. You get a new dataset. What do you check before analysing it?

Grain: what is one row? Is the supposed primary key actually unique?
Row counts over time: sudden drops or spikes often mean a pipeline change, not a business change.
NULLs and defaults per column, including placeholders like 0, -1, 'N/A' or 1970-01-01.
Ranges and types: negative quantities, future dates, prices stored as text.
Timezones: is created_at UTC or local? A day boundary in the wrong timezone shifts daily metrics.
Reconciliation: does the total roughly match a number the business already trusts, such as finance's revenue?

The interviewer wants to hear that you assume the data is wrong until it proves otherwise.

41. How do you handle missing values and outliers?

Missing values: first ask why they are missing. Missing at random (a logging outage) is different from missing for a reason (users who skip the income question differ from those who answer). Options are dropping rows, imputing (median, a group median, or a model), or keeping "missing" as its own category - and the choice should be stated, because it changes results.

Outliers: verify before removing. A $90,000 order may be a data error or your biggest customer. Common treatments are capping (winsorizing) at a percentile, analysing on a log scale, or using medians. Never quietly delete outliers to make a result look cleaner; report the analysis with and without them if they matter.

42. How would you define a metric like "active user"?

A good metric definition answers: which action counts (any login, or a meaningful action such as creating a document), over what window (daily, weekly, 28-day), for which population (exclude internal accounts, test accounts, bots), and at what timestamp and timezone. Then document it in one place so every dashboard uses the same definition.

Expect the follow-up: "logins went up 20% but nothing else did - is that good?" If the definition counts logins, a forced re-authentication change could inflate it. Prefer actions tied to the value the product delivers.

43. What is the difference between a fact table and a dimension table, and what is a star schema?

A fact table records events or measurements at a stated grain, with numeric measures and foreign keys: one row per order line, with quantity and price. A dimension table describes the entities those keys point to: customers, products, dates, with attributes you filter and group by.

A star schema is one fact table joined directly to its dimensions. BI tools are built around it: dimensions become filters and axes, facts become measures. Analysts get asked this because a dashboard built on a wide table where every join was pre-done in different ways tends to double-count. Knowing the grain of the fact table tells you which measures are safe to sum.

44. What separates a good dashboard from a bad one?

A good dashboard answers a specific set of questions for a specific audience. Concretely:

The headline metrics are at the top, each with a comparison (vs last period, vs target), because a number without context cannot be judged.
Definitions are visible or one click away.
Chart types fit the data: lines for trends over time, bars for comparing categories, tables when people need exact values. Avoid pie charts with many slices and 3D anything.
Filters are limited to ones the audience actually uses.
Data freshness is shown ("updated 06:00 UTC").

A bad dashboard is 30 charts nobody asked for. A good follow-up from you: "Who looks at this, and what decision does it change?"

45. Explain measures vs calculated columns (or the equivalent) in a BI tool.

The concept that transfers across tools is row-level versus aggregate-level calculation:

In Power BI, a calculated column is computed per row and stored in the model; a measure is a DAX expression evaluated at query time in the current filter context. Ratios like margin % should be measures, so they re-aggregate correctly when someone slices by region.
In Tableau, row-level calculated fields vs aggregate calculations, plus level-of-detail (LOD) expressions such as FIXED to compute at a grain different from the view.
In Looker, dimensions vs measures defined in LookML, so the definition lives in one modelled place.

The classic bug: computing a ratio per row and then averaging it (average of ratios) when the business wants the ratio of totals.

Part 7: Case scenarios and stakeholders (46-50)

These are what live rounds and take-homes actually use. There is no single right answer; the score comes from structure, sensible numbers and a clear recommendation.

46. Daily active users dropped 15% yesterday. How do you investigate?

Is it real? Check the data pipeline first: did logging change, did a job fail, is yesterday's data complete yet? Compare against a second source (server logs, revenue).
Is it unusual? Compare with the same weekday in previous weeks and with holidays.
Where is it? Segment by platform, app version, country, acquisition channel, new vs returning. A drop concentrated in one Android version points to a release; a drop everywhere points to something external or upstream.
What changed? Releases, experiments ramped, marketing campaigns ending, outages, competitor events.
Communicate what you know, what you do not, and when you will update.

Narrate the order out loud - data quality first is what separates analysts from guessers. Practise the full case on metric drop diagnosis.

47. A stakeholder asks for "a dashboard of all our customer data". What do you do?

Do not build it. Ask what decision they are trying to make, how often, and what they would do differently depending on the answer. "All customer data" usually turns into two or three questions, such as "which accounts are at risk of churning this quarter?" That becomes a short list ranked by a churn signal, refreshed weekly - far more useful than 40 charts.

Also agree on definitions (what is a "customer", what is "churn") and on a first version date. Interviewers ask this to see whether you can push back politely and turn a vague request into a scoped deliverable.

48. Your analysis contradicts what a senior leader believes. How do you present it?

Lead with the question and the answer, then the evidence, then the caveats. Check your work first - ideally have a peer review the query - because being wrong in this situation is costly. Present the finding privately to the leader before any group meeting, so they are not surprised in front of their team. Show the data plainly, acknowledge what the analysis cannot rule out, and offer a way to test the disagreement (an experiment, a holdout, a follow-up cut).

The answer interviewers want is neither "I would defer" nor "data wins". It is that you protect the decision quality and the relationship at the same time.

Start with questions, not charts. Write down three or four business questions the data can answer: what drives revenue, who repeats, where do customers drop off.
Profile the data (Q40) and document any cleaning decisions in the submission.
Answer each question with one clear chart or table and one sentence of interpretation.
Lead the write-up with an executive summary: three findings, each with a recommended action and the expected impact in numbers.
State limitations: time range, missing fields, what you would need to be more confident.
Keep code reproducible: a SQL file or notebook that runs top to bottom.

Reviewers consistently prefer a short, correct, prioritised submission to a long one. Fifteen charts with no recommendation reads as "did not know what mattered".

50. How would you measure the impact of a feature that was launched without an A/B test?

Options, from strongest to weakest:

Difference-in-differences: compare the change in users who got the feature with the change in a similar group who did not (a region rolled out later, an older app version), over the same period. Assumes both groups would have trended the same without the feature.
Staggered rollout dates across segments give natural comparison groups.
Interrupted time series: model the pre-launch trend and seasonality, then compare post-launch actuals with the forecast.
Matching on adopters vs non-adopters - but adopters self-select, so this overstates impact.

Always say what each method assumes and how you would check it (for difference-in-differences, plot both groups' pre-period trends). The best final sentence: "and for the next launch, let's hold out 5% so we do not have to do this again".

How to practice for a data analyst interview

Write SQL every day against real tables, not in your head. Work through the topics on the SQL practice track in order - joins, aggregation, CASE WHEN, NULL handling, window functions, CTEs - and say the grain of each table before writing the join.
Memorise the four or five patterns that cover most screens: top N per group, dedup latest row, running total, period-over-period with LAG, and cohort retention. Type them from scratch until they take under five minutes each.
Practise explaining a p-value and a confidence interval to a non-technical friend. If they can repeat it back correctly, you are ready for the stats round.
Do one case out loud per week using the structure in Part 7: check the data, segment, hypothesise, recommend. Timebox it to 20 minutes.
Build one small end-to-end project: a public dataset, a cleaned table, three charts, a one-page write-up with a recommendation. It doubles as take-home practice and a portfolio piece.
Rehearse the stakeholder questions with real stories. Every analyst has a "the number was wrong" story and a "they asked for X but needed Y" story; have yours ready.

Keep going

Follow a structured path: the data analyst prep track sequences SQL, statistics and case practice, and if the role leans toward modelling, the data scientist prep track is the next step.
Drill under time pressure: the statistics, A/B testing and databases question banks, plus the free daily challenge with no account needed.
Keep the references open: the SQL essentials cheat sheet and the SQL query tuning cheat sheet.
Go deeper on SQL: Top 45 SQL interview questions and Top 50 database interview questions. If the role sits close to pipelines, see Top 50 data engineering interview questions.
Rehearse the case round: the chat-based AI mock interview pushes back with the "is the data even right?" follow-ups that decide case rounds.
Related guides: Top 50 Machine Learning Interview Questions 2026, Top 50 Product Manager Interview Questions 2026 (With Answers).

#dataanalyst #sql #statistics #abtesting #analytics #interviews #career

Top 50 Data Analyst Interview Questions 2026 (SQL, Excel, Stats, BI)