Data Interview Questions

Note: Lead with the question, not the tools. "I used Python and SQL" describes a toolkit; "we could not tell which customers were about to churn" describes a problem worth solving.

Structure it as:

• The business question and who was asking it. Someone had to be waiting on the answer — say who, and what decision depended on it.
• The data. Where it came from, how much, and what was wrong with it. Realistically most of the work was here, and saying so is credible rather than a weakness.
• Your approach, briefly. The analysis or model, and — importantly — why that approach rather than a simpler one.
• The finding and what changed. An analysis nobody acted on is not a result. If it led nowhere, be honest about why, because that is a common and instructive experience.

If you found the data was unreliable and that was the real finding, tell that story. It happens constantly and few people report it.

Show a checking habit, because a confident wrong number is worse than no number.

• Sanity-check the totals against something independent. If your query says 4,200 orders last month, does the finance report agree? Reconciling against a source people already trust catches most errors immediately.
• Check the row count after every join. A join that silently multiplies rows is the single most common cause of inflated numbers in SQL analysis, and it produces results that look plausible.
• Look at the raw data, not just the aggregate. Averages hide duplicates, nulls, test records, and outliers. Spot-check individual rows.
• Question a surprising result before celebrating it. An unexpectedly strong finding is more often a bug than a discovery.
• Have someone else review the logic, particularly the filters and date ranges, which is where assumptions hide.
• Document your assumptions — which date field, which definition of "active", what was excluded — so a reviewer can challenge them.

Note: Being willing to say "I checked and my first number was wrong" is what earns trust over time. Analysts who never report an error are usually not checking.

This is a test of independence, so choose a story where you held your position.

• The finding and why it was unwelcome. A campaign that did not work, a product feature nobody used, a process assumed efficient that was not, or growth that came entirely from one source rather than the strategy everyone credited.
• How you verified it before presenting. This matters most. Being challenged and having already checked the tracking, the definitions, and alternative explanations is what makes the finding survive.
• How you presented it. Without drama, focused on what to do next rather than on who was wrong. Framing a negative finding as an opportunity — "we can stop spending here and redirect it" — makes it far easier to accept.
• The outcome, including if it was ignored. That is a legitimate ending, and how you handled it is the interesting part.

Note: Separating what the data shows from what you think it means, and being clear which is which, is what makes an unwelcome finding defensible. Overstating a conclusion gives people a reason to dismiss the whole thing.

Show that you interrogate the request rather than guessing and producing something unusable.

• Ask what decision it will inform. This single question resolves most vagueness. If the answer is "nothing specific", the request may not be worth doing, and saying so politely saves everyone time.
• Ask what they expect to see, and what they would do if the answer were the opposite. It reveals the real question, which is often narrower than what was asked.
• Agree definitions explicitly. "Active user", "revenue", and "customer" mean different things to different teams, and a disagreement discovered after presenting destroys the work.
• Confirm scope and timeframe — which period, which segment, which markets.
• Show something rough early. People cannot specify an analysis in the abstract but react immediately to a draft. A quick first cut with caveats surfaces the misunderstanding while it is cheap to fix.

Note: Mentioning that you write the agreed question back to the requester in one sentence before starting is a simple, concrete practice that prevents most rework — and demonstrates that you have been burned by this before.

How you learn: working on real problems rather than tutorials, because clean tutorial datasets teach none of the skills that matter. Beyond that, documentation and release notes for the tools you use, and reading other people's analysis critically.

How you decide what to learn:

• Fundamentals repay more than tools. SQL, statistics, and knowing how to frame a question have not changed and will not. Most analytical errors are reasoning errors, not tooling errors.
• Learn what your bottleneck is. If you are limited by data access, learn more SQL and data modelling. If you are limited by people not acting on your work, learn communication and visualisation. Learning a new modelling technique rarely addresses either.
• Follow the direction of the role. Analytics engineering, version control, and testing practices have moved from software into data work, and they are increasingly expected.

Note: A credible and refreshing answer is that most business value comes from clean data and well-framed simple analysis rather than sophisticated methods. Being able to say you deliberately chose the simpler approach shows judgement, which is harder to teach than technique.

A join decides which rows survive when two tables are matched.

• INNER JOIN — only rows matching on both sides. Non-matching rows from either table disappear.
• LEFT JOIN — every row from the left table, with NULLs where the right has no match. Use it when you must not lose rows from your primary table.
• RIGHT JOIN — the mirror image. Rarely used, because swapping the tables and using LEFT reads more naturally.
• FULL OUTER JOIN — every row from both sides, with NULLs filling the gaps. Useful for reconciliation, finding records present in one system and not the other.
• CROSS JOIN — every combination of both tables. Occasionally deliberate, for generating a date-by-product grid; usually an accident.

The two traps that matter in real analysis:

• A WHERE condition on the right table turns a LEFT JOIN into an INNER JOIN. WHERE b.status = 'active' discards the NULL rows you were trying to keep. Put the condition in the ON clause instead.
• Joining on a non-unique key multiplies rows. If the right table has three rows per key, your row count triples and every SUM is inflated. Always check the row count before and after a join — this is the most common source of wrong numbers in analysis.

GROUP BY collapses rows sharing a value into one row per group, so aggregate functions — COUNT, SUM, AVG, MIN, MAX — can be applied per group.

The difference between WHERE and HAVING is when they run:

• WHERE filters rows before grouping. It cannot reference an aggregate, because the aggregates do not exist yet.
• HAVING filters groups after aggregation. This is the only place you can filter on an aggregate value.

SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE order_date >= '2026-01-01'   -- filters rows first
GROUP BY customer_id
HAVING SUM(amount) > 50000          -- filters groups after
ORDER BY total DESC;

The full logical order of execution — which explains most SQL confusion — is FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. Because SELECT runs after HAVING, you generally cannot use a column alias in HAVING, though some databases permit it.

Note: Prefer WHERE for anything it can do, since filtering rows before grouping is cheaper than aggregating and discarding. And remember that COUNT(*) counts rows while COUNT(column) skips NULLs — a distinction that silently changes results.

A window function performs a calculation across a set of rows related to the current row, without collapsing them the way GROUP BY does. You keep every row and gain an aggregate alongside it.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
FROM orders;

• PARTITION BY divides rows into groups — like GROUP BY, but without collapsing them.
• ORDER BY inside OVER determines the ordering for running calculations and ranking.

The main families:

• Ranking — ROW_NUMBER (always unique), RANK (ties share a rank, leaving gaps), DENSE_RANK (ties share, no gaps).
• Offset — LAG and LEAD to reach previous or next rows, which is how you calculate period-over-period change without a self-join.
• Aggregate — SUM, AVG, COUNT over a window, for running totals and moving averages.

The classic use is "get the most recent row per group": number rows with ROW_NUMBER() partitioned by the group and ordered by date descending, then filter to rn = 1 in an outer query — because window functions cannot be used in WHERE.

Duplicates:

• Find them by grouping on the columns that should be unique and filtering to HAVING COUNT(*) > 1. First decide what "duplicate" means — a full row copy is different from two records for the same customer with different spellings.
• Handle by deduplicating to the most recent or most complete record, usually with ROW_NUMBER(). Investigate the cause: duplicates from a broken pipeline should be fixed upstream, not cleaned repeatedly.

Missing values — understand why before deciding:

• Missing at random can be imputed with a median or mode, or the rows dropped if few.
• Missing for a reason is informative. A blank "cancellation date" means the order was not cancelled, not that the data is missing. Imputing it would be nonsense.
• Adding a flag indicating the value was missing often preserves useful signal.

Outliers:

• Find them with the IQR rule, z-scores, or simply by sorting and looking at the extremes.
• Do not delete them by default. Decide whether each is an error (a typo, a test record, a sensor fault) or a genuine extreme value. Removing real high-value customers because they are statistically unusual destroys the most important part of the data.
• Options are correcting, excluding with documentation, capping, or transforming — and using a median rather than a mean where the distribution is skewed.

Note: Document every cleaning decision. An analysis where nobody knows what was excluded cannot be trusted or reproduced.

• Mean — the arithmetic average. Uses every value, which makes it efficient but highly sensitive to outliers.
• Median — the middle value when sorted. Robust to outliers, because moving the largest value further out does not change it.
• Mode — the most frequent value. The only one usable for categorical data.

When to use each:

• Use the mean for roughly symmetric distributions without extreme values, and when you need the total to be recoverable — mean × count gives the sum, which matters for revenue.
• Use the median for skewed distributions. Income, house prices, session duration, and order values are all right-skewed, and the mean overstates the typical case. Median salary and mean salary can differ enormously in the same organisation, which is exactly why the choice matters.
• Use the mode for categories — the most common product, region, or plan.

The relationship tells you about shape: in a symmetric distribution all three roughly coincide. Mean above median indicates right skew; mean below median indicates left skew.

Note: The strongest point is that reporting a central value alone is usually insufficient. Two datasets with identical means can be completely different, so pairing it with a spread measure — standard deviation, IQR, or percentiles — is what makes the summary honest. For latency and response time, percentiles matter far more than the average, because the average hides the worst experiences.

Correlation means two variables move together. Causation means one produces the change in the other. Correlation is necessary but nowhere near sufficient.

Why correlation appears without causation:

• A confounding variable drives both. Ice cream sales correlate with drownings; temperature causes both. In business, the classic case is that customers who use a feature retain better — because engaged customers both use features and retain, so the feature may cause nothing.
• Reverse causation — the arrow points the other way.
• Selection bias — the way the sample was chosen created the pattern.
• Coincidence, particularly when many variables are tested until something correlates.

How to establish causation:

• A randomised controlled experiment is the gold standard. Random assignment makes the groups equivalent on everything, including factors you did not think of, so a difference in outcome can be attributed to the treatment. This is what A/B testing is.
• Where randomisation is impossible, quasi-experimental methods help: difference-in-differences, regression discontinuity, instrumental variables, or a matched control group. All are weaker and rest on assumptions that must be stated.

Note: The practically valuable habit is asking "what else could explain this?" before presenting a causal claim — and being explicit when you are reporting an association rather than a cause, because stakeholders will act on it as a cause unless you say otherwise.

A p-value is the probability of observing a result at least as extreme as yours if the null hypothesis were true. A small p-value means the data would be surprising under the assumption of no effect.

Statistical significance means the p-value falls below a threshold chosen in advance, conventionally 0.05. That number is a convention, not a law of nature.

The common misinterpretations — this is what interviewers are testing:

• It is not the probability the null hypothesis is true. It is the probability of the data given the null, which is a different conditional.
• It is not the probability your result is a fluke.
• Significance is not importance. With a large enough sample, a commercially meaningless 0.01% difference becomes statistically significant. Always report the effect size and a confidence interval alongside it.
• Non-significant does not mean no effect. It may mean the sample was too small to detect one — absence of evidence is not evidence of absence.
• Stopping a test when it becomes significant invalidates it. Repeatedly checking and stopping at the first significant moment produces false positives at a much higher rate than 5%. This is the most costly mistake in practical A/B testing.
• Testing many variables guarantees false positives. Twenty independent tests at p < 0.05 produce roughly one significant result by chance alone.

Note: Saying you prefer to report confidence intervals and effect sizes rather than a bare significant/not-significant verdict shows genuine statistical literacy.

OLTP (Online Transaction Processing) systems run the business. They handle many small, fast reads and writes — placing an order, updating a profile. They are normalised to avoid update anomalies, optimised for write throughput and row-level access, and hold current state.

OLAP (Online Analytical Processing) systems analyse the business. They handle fewer, much larger queries scanning millions of rows to aggregate. They are denormalised for query simplicity and speed, often column-oriented, and hold history.

Why they must be separate: running a heavy analytical query against the production transactional database competes for resources with the application, and can slow or block real customers. Beyond a small scale, this is not optional.

A data warehouse is the OLAP store — a central repository consolidating data from multiple source systems, cleaned and structured for analysis, holding history rather than just current state. Modern examples are BigQuery, Snowflake, and Redshift.

Related concepts:

• A data lake stores raw data in its native format, structured or not, cheaply and at scale. Flexible, but without governance it becomes a data swamp nobody can use.
• A data mart is a subset of a warehouse serving one team or function.
• A lakehouse combines lake storage economics with warehouse structure and transactions.

Note: ETL versus ELT is the natural follow-up — modern cloud warehouses are powerful enough to transform after loading, so ELT has largely replaced ETL.

Choose the chart from the relationship you are showing:

• Change over time — line chart. Area chart for cumulative totals.
• Comparison across categories — bar chart. Horizontal bars when labels are long, and sorted by value unless there is a natural order.
• Part of a whole — stacked bar, or a pie chart only with very few slices. Beyond three or four segments a pie is unreadable and a bar chart is better.
• Relationship between two variables — scatter plot.
• Distribution — histogram or box plot. This is important and underused: an average alone hides the shape entirely.
• Two dimensions plus intensity — heatmap.
• A single key number — just show the number, large. A gauge chart adds nothing.

What makes it good:

• Title it with the finding, not the contents. "Mobile conversion fell 40% after the redesign" beats "Conversion by Device".
• Start bar chart axes at zero. Truncating exaggerates differences and is genuinely misleading; line charts showing change may reasonably not.
• Remove everything that is not information — 3D effects, heavy gridlines, decorative colour, and redundant legends.
• Use colour to mean something, and check it works for colour-blind readers and in greyscale.
• Label directly rather than forcing a trip to a legend.

Three roles that overlap but answer different questions.

• Data engineer — builds and maintains the infrastructure that makes data available. Pipelines, warehouses, ETL and ELT, orchestration, and data quality. Skills: SQL, Python or Scala, cloud platforms, and increasingly software engineering practice — version control, testing, CI. They answer "how do we get reliable data to the people who need it?"
• Data analyst — turns data into decisions. Exploratory analysis, dashboards, reporting, and working with stakeholders to define the question. Skills: strong SQL, a BI tool, spreadsheets, statistics, and — most importantly — communication. They answer "what happened, and why?"
• Data scientist — builds models to predict or optimise. Statistical modelling, machine learning, experiment design. Skills: Python or R, statistics, ML libraries, and enough engineering to deploy. They answer "what will happen, and what should we do?"

Analytics engineer is the newer role between engineer and analyst — modelling data inside the warehouse with tools like dbt, applying software practices to transformation logic.

Note: The honest observation worth making is that titles vary enormously between organisations, and in smaller companies one person does all three. It is also worth saying that most business value comes from reliable data and clear analysis rather than sophisticated models — a well-built pipeline and a good dashboard usually beat a machine learning project that nobody deploys.