E · Interview Question Bank

This appendix collects 110 interview questions for data analysts, analytics engineers, data engineers and product data scientists, each with a short model answer. 78 come from the chapters’ Interview Corners, rewritten to stand alone; the other 32, marked New, cover what interviews often ask beyond them, from SQL basics and consecutive logins to pipeline design and behavioural questions.

TipHow to use this bank
  • Answer out loud first. Read one question, answer it in about two minutes, and only then open the model answer. Reading answers feels like learning; saying them is what the interview tests.
  • Lead with the answer, then the reasons. Interviewers remember “about 57,600 users per group” better than “it depends”. Give a number, an example or a query whenever you can, then name what you would check next.
  • Stars show depth. ★ is a definition or a warm-up query, to answer in under a minute. ★★ is the core of most interviews: a method, a trade-off or a short case. ★★★ is a deeper follow-up, common for senior or specialist roles.
  • Links. “Story and details” goes to the chapter whose Interview Corner the question comes from. For a New question, “Background” goes to the chapter that teaches the idea.
  • By role. Analysts and product data scientists: metrics, the SQL coding questions, statistics and experiments. Analytics engineers: SQL, metrics, and the warehouse and pipeline parts of data engineering. Data engineers: all of SQL, including the MySQL index questions, and all of data engineering.

Interviews differ between companies and roles. This bank collects common kinds of question, not any one company’s questions.

Phrases interviewers use. Walk me through …: explain the steps in order. How would you go about …: describe your plan, not only the final answer. Deep dive: a detailed look at one part. Sanity check: a quick test that a number is believable, such as comparing it with a second source. Push back: disagree politely and give your reasons. Rapid fire: many short questions, one after another.

Area Questions Difficulty New Chapters
Metrics 20 ★ 5 · ★★ 14 · ★★★ 1 5 1–2, 4–6, 12, 15–16, 20, 24
SQL 19 ★ 6 · ★★ 11 · ★★★ 2 10 3, 6–10, 15
Engineering 34 ★ 9 · ★★ 18 · ★★★ 7 8 7–15
Statistics 12 ★ 4 · ★★ 8 · ★★★ 0 3 4, 16–19, 21
Experiments 20 ★ 1 · ★★ 14 · ★★★ 5 4 19–23
Communication 5 ★ 2 · ★★ 3 · ★★★ 0 2 1, 15, 24
All 110 ★ 27 · ★★ 68 · ★★★ 15 32 1–24

Start here

If you have one week, practise these first: about 20 questions for your role, in the order of the bank. Each link opens the question, and the short title says what it is about.

Data analysts (22): M1 DAU up, revenue down · M2 Define an active user · M3 Two teams disagree · M6 Mean or median · M9 Mix shift in conversion · M14 One funnel step fell · M15 Cohorts, not MAU · M16 DAU fell 10% · M19 Did the feature work · S1 SQL basics · S2 Top 3 per city · S3 LEFT JOIN row count · S4 SUM doubles after join · S5 Day-1 and day-7 retention · S7 Running and moving totals · S9 Consecutive login days · S10 Anti-join for churn · T5 Explaining a CI · T8 What a p-value is · X1 Designing an A/B test · X8 Peeking at results · C1 Presenting to a CEO

Data engineers and analytics engineers (23): M3 Two teams disagree · S1 SQL basics · S2 Top 3 per city · S4 SUM doubles after join · S8 Remove duplicates · S9 Consecutive login days · S15 Why B+ trees · D2 CDC or nightly dumps · D4 Kafka message order · D6 Not losing messages · D8 OLTP and OLAP · D9 Why columnar is fast · D11 Small-files problem · D13 Partitions or buckets · D14 Data skew · D16 What triggers shuffles · D22 Why warehouse layers · D23 Modelling the order flow · D24 Zipper table SQL · D26 Idempotent pipelines · D27 Safe backfills · D30 Flink exactly-once · D33 Monitoring data quality

Product data scientists (23): M4 North-star metric · M5 Revenue metric tree · M16 DAU fell 10% · M19 Did the feature work · M20 Ship despite cancellations · S5 Day-1 and day-7 retention · S13 Ordered funnel in SQL · T1 Simpson’s paradox · T2 Central limit theorem · T4 What confounders are · T8 What a p-value is · T10 Not significant: now what · T11 Choosing a test · X1 Designing an A/B test · X2 Randomisation unit · X4 Sample size · X7 When not to test · X8 Peeking at results · X9 Sample ratio mismatch · X11 Many tests at once · X12 Ratio metrics · X14 CUPED variance reduction · X17 DiD assumptions

Metrics and analysis

Defining, reading and explaining numbers. Many interviews open with one of these as a short case. There is rarely one right answer, so say your definitions first, then your plan, then what would change your mind.

NoteInterview Corner · Defining a metric

M1. Daily active users went up 10%, but revenue fell. What do you check first?
★★ · Story and details: Chapter 1

The definitions, before the business. Did an app release, a tracking change or a time-zone change alter who counts as an app user, for example background refreshes now counted as visits? Is revenue counted from the same source in both periods, net of refunds both times? Once both definitions are stable, break revenue into parts: app users × share who become active customers (they place an order) × orders per active customer × value per order, and see which part moved. A campaign that brings many people who look around and do not buy raises app users and lowers the share who become active customers.

M2. Define an active user.
★ · Story and details: Chapter 1

First say which kind you mean: an app user, who opened the app, or an active customer, who placed an order. Then state every part of the rule. What counts: one meaningful action, not an app that a notification opened by itself. Where: client events, server logs or the orders database. When: a calendar day in one time zone, or a rolling 7 or 28 days. Grain: one person per user ID, not per device. Who: no bots, test accounts or staff. Say why this rule fits the question, and give it a version number so later changes are visible.

M3. Two teams report different numbers for the same metric. What do you do?
★★ · Story and details: Chapter 1

Put the two definitions side by side under the same five questions: what counts, where, when, the grain and who is included. Then build a bridge: start from team A’s number, change one part of the definition, record the new number, and repeat until you reach team B’s number. Each step shows what one difference is worth. When two differences interact, the order of the steps changes their sizes, so say which order you used. Finally, agree which definition answers which business question, and write it down in one shared place.

M4. How would you choose a north-star metric for a food or drink delivery app?
★★ · New · Background: Chapter 1, Chapter 12

A north-star metric is the one number that best shows customers getting value, and that leads to revenue over time. For a delivery app, a good candidate is completed (not refunded) orders per week, or weekly active customers with at least one completed order. A good north star counts value delivered, moves within weeks when the product improves, and is hard to raise in a harmful way; revenue is a result rather than a driver, and app opens are easy to inflate. Pair it with counter-metrics, such as refunds, delivery time and cost per order, so a team cannot win the north star by hurting something else. Then define it as carefully as any other metric: what counts, where, when, grain and who.

M5. Break revenue into a metric tree. How would you use it?
★★ · New · Background: Chapter 1, Chapter 12

A metric tree splits a top metric into parts that multiply or add up to it, down to parts a team can act on. For example: revenue = active customers × orders per active customer × average order value; active customers = new + returning + reactivated; average order value = drinks per order × average price per drink. When the top number moves, walk down the tree to the branch that moved: “revenue fell” becomes “returning customers in one city ordered less often”. Each part needs its own written definition and source, and the parts must multiply or add back to the top exactly; otherwise you will spend time on a gap that is only a definition difference. The tree also gives each team one part to own that connects to the north star.

NoteInterview Corner · Typical values, segments and charts

M6. Mean or median for average order value? Why?
★ · Story and details: Chapter 2

It depends on the question. Order values are right-skewed: a few very large orders, such as bulk orders from corporate accounts, pull the mean up. To describe a typical order, use the median and a few percentiles. To plan revenue or cash, use the mean, because only the mean times the number of orders gives the total. In practice, report both with the count, and show very large customers as a separate segment.

M7. How do you treat outliers in a KPI (a number a team is judged by)?
★★ · Story and details: Chapter 2

First find out whether they are errors or real. Fix errors at the source, such as a test order or a price entered in cents. For real values, choose a rule before you see the result, write it down, and apply it every time: report a robust number (median, trimmed mean), show the outliers as their own segment, or cap values at a percentile taken from past data. Never remove outliers after you have seen which way they push. Keep a list of the largest values and look at it whenever the KPI moves.

M8. Average order value fell 15% from Friday to Sunday. What do you check?
★★ · Story and details: Chapter 2

First check the definition: what the number averages, from which source, and with order statuses as of when. Then check whether the two days have the same mix of orders. Compare the whole distribution on both days, the median and the top percentiles, and split by customer type. If the fall disappears in the median, or among regular customers, it comes from the tail: for example, big weekday orders from corporate accounts, which do not order at weekends. Then compare Sunday with earlier Sundays, not with a Friday.

M9. Conversion fell overall but rose in every channel. How can that be, and what do you do?
★★ · Story and details: Chapter 4

The mix moved. Traffic shifted toward a channel with a low conversion rate, so the overall rate, which is a weighted average, fell even though each channel improved: Simpson’s paradox (T1). First check that the definitions did not change. Then show each channel’s change in rate and its change in share of traffic, and split the overall change into a “rate” part and a “mix” part. Then ask why the mix moved. That is often the real business question, for example a new campaign that brings many visitors with little intent to buy.

M10. When is it a mistake to segment?
★★ · Story and details: Chapter 4

When you segment by something the change itself affects. If you test a new checkout and compare “users who reached the payment page”, the change decides who is in the segment, so the groups are no longer alike, and you can create or hide a difference. Segment only by things fixed before the change, such as city or signup date. And do not slice the data many ways until one slice looks different: with enough slices, some differ by chance alone (X11).

M11. When is a truncated axis acceptable?
★ · Story and details: Chapter 5

Never for bars or areas: readers compare their lengths, so the base must be zero. Often fine for line and dot charts, where readers compare positions and shapes; zooming in on the data’s range can show a change that is invisible from zero. Label the axis clearly, and check that the zoom does not make ordinary noise look like an event. When zero has no meaning, as for a temperature in Fahrenheit, there is no reason to show it.

M12. A metric has very different scales across cities. How do you show it?
★★ · Story and details: Chapter 5

First ask whether you compare levels or changes. For changes, put every city on the same scale: a percent change, a rate per 100, or an index where each city starts at 100. Then draw small multiples with shared axes, or one chart with one line per city. A log scale also works: on it, equal percent changes have equal slopes in big and small cities. Avoid one shared linear axis, where the small cities’ changes look flat, and avoid dual axes.

M13. What is wrong with dual-axis charts?
★ · Story and details: Chapter 5

Each axis can be scaled on its own, so the designer controls where the lines sit, how steep they look and where they cross. Readers read a crossing or a shared trend as meaningful, but it comes from the scaling. Instead, show both series as an index or a percent change on one axis, or draw two charts, one above the other, with a shared x-axis. The one safe dual axis shows the same quantity in two units with a fixed conversion, such as °C and °F.

NoteInterview Corner · Diagnosing a change

M14. Conversion fell at one funnel step only. What are your next steps?
★★ · Story and details: Chapter 6

First ask whether it was measured correctly. Split the step by platform, app version, country and payment method, find the day it started, and compare that day with releases and tracking changes. Check the step against an independent source, such as orders or payments in the database. If the database agrees with the funnel, the drop is real behaviour: look at error rates, load times, prices and the screen itself. If the database disagrees, it is measurement: find the event that stopped firing.

M15. Why look at cohorts instead of monthly active users?
★ · Story and details: Chapter 6

Active-user counts mix new and old customers, so strong growth can hide old customers leaving. A cohort is a group that started in the same period, such as the same signup week. A cohort table has one row per cohort and one column per age, such as weeks since signup, and you read it three ways. Along a row, you see how one cohort’s activity changes as it ages. Down a column, you compare cohorts at the same age: are newer cohorts better or worse? Along a diagonal, every cell is the same calendar week, so a bad week, such as an outage, shows up as a stripe across all cohorts.

M16. Daily active users fell 10% yesterday. Walk me through your investigation.
★★ · New · Background: Chapter 1, Chapter 6, Chapter 15, Chapter 16

Clarify first: which definition of “active”, compared with what (the day before, or the same weekday last week), and is it one day or a trend? Then rule out measurement: data completeness and freshness, late or failed loads, tracking changes and app releases, and a second source such as server logs or the orders database. Next, decompose: new, returning and reactivated users, then platform, app version, country and channel; a drop that lives in one segment points to its cause, such as one app version or one paused campaign. Then check the calendar and the outside world (holidays, weather, outages, a competitor’s promotion) and how big a normal daily swing is. Finally, put a size on each explanation in points of the 10%, until they add up, and say plainly which part is still unexplained.

M17. Follow-up to M16: a metric dropped 12% overnight. What are your first five checks?
★★ · Story and details: Chapter 15

  1. The definition: did the metric’s query, filters or source change?
  2. Completeness: did all the data arrive (row counts, freshness, late or failed loads)?
  3. A second source: does an independent record, such as the orders database, show the same drop?
  4. Segments: is the drop everywhere, or in one platform, app version, city or payment method? What was released that day?
  5. The baseline: was the comparison day unusual (weather, holiday, promotion)?

Only then look for a real change in behaviour.

M18. Revenue fell 8% week over week. How do you decide if it is real?
★★★ · Story and details: Chapter 16

First check the measurement: definitions, completeness, late data, changed pipelines or app releases. Then check the comparison: how big are week-over-week changes normally, over many past weeks, and was the baseline week unusual (holiday, weather, promotion, a record high)? Better, build an expected value for the week from its drivers (weekday mix, season, trend, weather, marketing), fitted on past data, and compare the week with that. Only a gap that is large compared with the model’s normal misses needs explaining. Then split it by segment to find where it lives.

NoteInterview Corner · Measuring a launch

M19. How would you measure whether a new feature worked?
★★ · New · Background: Chapter 1, Chapter 20

Answer in four steps.

  • Goal: ask what the feature is for, and for whom. For example, “group ordering should raise orders from offices”.
  • Metrics: one primary metric tied to that goal (orders per office user), a few secondary ones (feature use, drinks per order) and guardrails (cancellations, delivery time). Define each one before you look at any data.
  • Method: an A/B test if you can randomise users; if not, a staged rollout with a holdout, or a comparison with similar users who did not get it (X7). Check adoption first: a feature few people use cannot move a big metric.
  • Decision: write down in advance which result means ship, improve or roll back. Report the size of the effect with its interval, not only a p-value.

M20. Follow-up to M19: orders rose 2%, but cancellations rose 1%. Do you ship?
★★ · New · Background: Chapter 20, Chapter 24

First ask what the 1% means. From a 5% cancellation rate, a 1% relative rise (to 5.05%) still leaves completed orders up about 1.9%; a rise of 1 point (to 6%) leaves them up only about 0.9%. Then check both intervals: can either change be told apart from zero? Put both in one unit that matters, such as completed orders or margin after the cost of a cancellation (refund, courier time, an unhappy customer). Follow the guardrail limit written before the test, if there was one. Then look at who cancels and why: if one city or payment method drives it, you may ship with a fix, or ramp up slowly while you watch cancellations.

SQL and databases

Coding questions, and the database questions that come with them. All SQL here is DuckDB SQL, and every query runs each time this book is built: on Steep’s tables when the query names them, or on a small test table whose CREATE TABLE statement is shown in the answer (it runs in DuckDB and PostgreSQL; elsewhere, create the table and add the rows with INSERT INTO … VALUES). The notes give the Hive, Spark SQL and MySQL forms where they differ. Appendix A has the full dialect table and a live SQL console on Steep’s data. In an interview, say the grain of the result first (“one row per city and rank”), then write the query, then name one edge case: ties, NULLs or missing days.

NoteInterview Corner · SQL basics, quick questions

S1. Quick questions: execution order, WHERE or HAVING, three COUNTs, UNION or UNION ALL, and ranking ties.
★ · New · Background: Chapter 3 · Appendix A

Answer each in one or two sentences.

  • Logical order: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT (window functions run here), DISTINCT, ORDER BY, LIMIT. So WHERE cannot use a window function, and most engines do not let it use a SELECT alias.
  • WHERE or HAVING: WHERE filters rows before grouping; HAVING filters groups after, so only HAVING can test an aggregate such as count(*) > 5.
  • COUNT: count(*) counts rows; count(col) skips NULLs; count(DISTINCT col) counts different non-NULL values.
  • UNION or UNION ALL: UNION removes duplicate rows, which costs extra work; UNION ALL keeps every row. Use UNION ALL unless you need the removal.
  • Ties: with two values tied for first, row_number() gives 1, 2, 3, 4; rank() gives 1, 1, 3, 4; dense_rank() gives 1, 1, 2, 3.

The test table, then the three counts and the three rankings:

CREATE TABLE orders_demo AS
SELECT * FROM (VALUES
  (1, 50), (2, 50), (3, 30), (4, 20), (5, NULL)
) AS t(order_id, amount);
SELECT count(*)               AS all_rows,
       count(amount)          AS non_null_amounts,
       count(DISTINCT amount) AS distinct_amounts
FROM orders_demo;
all_rows non_null_amounts distinct_amounts
5 4 3 
SELECT order_id, amount,
       row_number() OVER (ORDER BY amount DESC, order_id)
                                          AS by_row_number,
       rank()       OVER (ORDER BY amount DESC) AS by_rank,
       dense_rank() OVER (ORDER BY amount DESC) AS by_dense_rank
FROM orders_demo
WHERE amount IS NOT NULL
ORDER BY amount DESC, order_id;
order_id amount by_row_number by_rank by_dense_rank
1 50 1 1 1
2 50 2 1 1
3 30 3 3 2
4 20 4 4 3
NoteInterview Corner · Joins, ranking and retention

S2. Find the three largest orders in each city.
★ · Story and details: Chapter 3 · Appendix A: Top N per group

Number the rows inside each city with a window function, then keep the first three. A window function is computed after WHERE, so the numbering needs its own step, here a CTE. Add a tie-breaker (order_id) so the answer is the same on every run. Then name two edge cases. Ties: rank() and dense_rank() keep tied rows together, so rn <= 3 can return more than three rows per city. NULLs: PostgreSQL and Snowflake put NULLs first in a DESC sort by default, so an order with no amount could rank first; NULLS LAST prevents that.

WITH ranked AS (
  SELECT city, order_id, net_amount,
         row_number() OVER (
           PARTITION BY city
           ORDER BY net_amount DESC NULLS LAST, order_id
         ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT city, rn, order_id, net_amount
FROM ranked
WHERE rn <= 3
ORDER BY city, rn;
city rn order_id net_amount
harbor 1 4277712 2,762.50
harbor 2 4183507 2,727.85
harbor 3 4184604 2,563.25
northgate 1 4407197 2,561.15
northgate 2 4300961 2,213
northgate 3 3993972 2,127.50
oldtown 1 3938593 2,636.50
oldtown 2 4403508 2,361.05
oldtown 3 4266023 2,331.60
riverside 1 4265065 2,701
riverside 2 4218573 2,500.30
riverside 3 4088630 2,264.50

Dialect: Hive (2.1 or later), Spark SQL and PostgreSQL accept NULLS LAST; MySQL does not, but it already sorts NULLs last in a DESC sort. DuckDB, Hive 4.0 or later, Snowflake, BigQuery and Databricks SQL can also write QUALIFY rn <= 3 without the CTE. MySQL and PostgreSQL cannot, and Spark SQL added QUALIFY only in version 4.2.

S3. Table A has 1,000 rows. You LEFT JOIN table B. How many rows can come back?
★ · Story and details: Chapter 3

At least 1,000, because a left join keeps every row of A. Exactly 1,000 if each row of A matches at most one row of B. More than 1,000 if some rows of A match several rows of B: that is a fan-out. (An inner join can return anything from 0 rows to 1,000 times the rows of B.) Then name the trap: a WHERE condition on B’s columns, such as b.status = 'paid', drops the rows where B is NULL, so the left join behaves like an inner join; put such conditions in the ON clause instead. WHERE b.key IS NULL keeps only the rows with no partner: the anti-join (S10).

S4. Why does my SUM double after a join?
★★ · Story and details: Chapter 3

Because the join changed the grain. If one order matches three item lines, the order’s amount appears three times, and SUM adds all three. Check it by comparing count(*) with count(DISTINCT order_id) after the join. On Steep’s orders and order lines:

SELECT count(*)                   AS rows_after_join,
       count(DISTINCT o.order_id) AS orders,
       sum(o.net_amount)          AS joined_sum,
       (SELECT sum(net_amount) FROM orders
        WHERE status = 'completed') AS true_sum
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed';
rows_after_join orders joined_sum true_sum
1,333,442 926,466 21,052,080.86 11,611,937.46

Fix it by adding up the amount at its own grain, or by reducing the “many” side to one row per order before the join:

WITH drinks AS (               -- the "many" side, one row per order
  SELECT order_id, sum(qty) AS drinks
  FROM order_items
  GROUP BY order_id
)
SELECT count(*)          AS orders,
       sum(o.net_amount) AS net_amount,
       sum(d.drinks)     AS drinks
FROM orders AS o
JOIN drinks AS d ON d.order_id = o.order_id
WHERE o.status = 'completed';
orders net_amount drinks
926,466 11,611,937.46 1,564,442

Do not fix it with SUM(DISTINCT amount): two different orders with the same amount would be merged.

S5. Write SQL for day-1 and day-7 retention.
★★ · Story and details: Chapter 6 · Appendix A: Retention

Say the rule before the query. The start: the signup day. Who counts: an app user, anyone with any app event that day. Day N: active on exactly day N after the start (others use “on day N or later”, or “within N days”; each gives a different number). Join only days 1 and 7, and count each user once. Leave a rate empty for cohorts whose day N is after the data ends, and keep only cohorts that start on or after the first day of event data: Steep’s users table includes customers who signed up long before its events begin, and their cohorts would otherwise show 0%.

WITH bounds AS (
  SELECT min(event_date) AS first_day, max(event_date) AS last_day
  FROM events
),
cohort AS (               -- cohorts the events can see from day 0
  SELECT u.user_id, u.signup_date AS day0
  FROM users AS u
  CROSS JOIN bounds AS b
  WHERE u.signup_date >= b.first_day
),
active_days AS (
  SELECT DISTINCT user_id, event_date AS day FROM events
)
SELECT c.day0,
       count(DISTINCT c.user_id) AS users,
       CASE WHEN c.day0 + 1 <= b.last_day THEN
         count(DISTINCT CASE WHEN a.day = c.day0 + 1 THEN c.user_id END)
           * 1.0 / count(DISTINCT c.user_id) END AS day1_retention,
       CASE WHEN c.day0 + 7 <= b.last_day THEN
         count(DISTINCT CASE WHEN a.day = c.day0 + 7 THEN c.user_id END)
           * 1.0 / count(DISTINCT c.user_id) END AS day7_retention
FROM cohort AS c
CROSS JOIN bounds AS b
LEFT JOIN active_days AS a ON a.user_id = c.user_id
                        AND a.day IN (c.day0 + 1, c.day0 + 7)
GROUP BY c.day0, b.last_day
ORDER BY c.day0;
day0 users day1_retention day7_retention
2026-06-01 152 0.243 0.191
2026-06-02 155 0.181 0.226
2026-06-03 140 0.164 0.193
… … … …
2026-10-23 163 0.245 NULL
2026-10-24 143 0.217 NULL
2026-10-25 182 NULL NULL

First 3 and last 3 of 147 rows.

Dialect: day0 + 7 gives a date in DuckDB and PostgreSQL. Hive and Spark SQL write date_add(day0, 7). In MySQL, day0 + 7 returns a number such as 20260921, not a date: write DATE_ADD(day0, INTERVAL 7 DAY).

NoteInterview Corner · Common coding questions

S6. Find the second-highest order value. What if there is no second value?
★ · New · Background: Chapter 3

First ask what “second highest” means. Usually it is the second-highest distinct value, so two orders tied at the top are not first and second. Every answer must return NULL, not an empty result, when there is no second value. On the test table from S1 (amounts 50, 50, 30, 20 and one NULL), all three forms return 30. The form most interviewers expect is a scalar subquery, which gives NULL when it finds no row:

SELECT (                    -- a scalar subquery: NULL if empty
  SELECT DISTINCT amount
  FROM orders_demo
  WHERE amount IS NOT NULL
  ORDER BY amount DESC
  LIMIT 1 OFFSET 1
) AS second_highest;
second_highest
30

Two forms that avoid LIMIT … OFFSET, whose syntax differs between databases; max() over no rows also gives NULL:

SELECT max(amount) AS second_highest
FROM orders_demo
WHERE amount < (SELECT max(amount) FROM orders_demo);
second_highest
30 
SELECT max(amount) AS second_highest  -- max() of no rows is NULL
FROM (
  SELECT amount,
         dense_rank() OVER (ORDER BY amount DESC) AS dr
  FROM orders_demo
  WHERE amount IS NOT NULL
) AS ranked
WHERE dr = 2;                         -- the N-th highest: dr = N
second_highest
30

dense_rank() works for the N-th highest too; rank() would skip numbers after a tie and miss it.

S7. For each day, show orders, a running total, a 7-day moving average and the change against the same day last week.
★★ · New · Background: Chapter 3

Window functions keep every row and look at the rows around it.

  • Running total: a sum over all rows up to this one. Write the frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: with ORDER BY and no frame, the default is RANGE, which adds all tied rows at once.
  • 7-day moving average: an avg over this row and the 6 before it. The first six rows have fewer days, so show NULL until the window is full.
  • Against last week: lag(orders, 7) reads the row seven rows back. That is the same weekday last week only if every day has exactly one row; if a day can be missing, join to a calendar table first. NULLIF(…, 0) turns a zero into NULL, so the change is NULL instead of an error.
WITH daily AS (
  SELECT CAST(created_at_local AS DATE) AS day,
         count(*) AS orders
  FROM orders
  WHERE status = 'completed'
    AND created_at_local >= TIMESTAMP '2026-08-31'
    AND created_at_local <  TIMESTAMP '2026-09-14'
  GROUP BY CAST(created_at_local AS DATE)
)
SELECT day, orders,
       sum(orders) OVER so_far AS running_total,
       CASE WHEN count(*) OVER last_7 = 7     -- a full week only
            THEN avg(orders) OVER last_7 END AS avg_7d,
       orders * 1.0
         / NULLIF(lag(orders, 7) OVER by_day, 0) - 1 AS vs_last_week
FROM daily
WINDOW by_day AS (ORDER BY day),
       so_far AS (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING
                                        AND CURRENT ROW),
       last_7 AS (ORDER BY day ROWS BETWEEN 6 PRECEDING
                                        AND CURRENT ROW)
ORDER BY day;
day orders running_total avg_7d vs_last_week
2026-08-31 5,458 5,458 NULL NULL
2026-09-01 6,124 11,582 NULL NULL
2026-09-02 6,188 17,770 NULL NULL
2026-09-03 6,744 24,514 NULL NULL
2026-09-04 7,546 32,060 NULL NULL
2026-09-05 7,306 39,366 NULL NULL
2026-09-06 6,075 45,441 6,491.6 NULL
2026-09-07 5,265 50,706 6,464.0 −0.035
2026-09-08 5,879 56,585 6,429.0 −0.040
2026-09-09 6,045 62,630 6,408.6 −0.023
2026-09-10 6,188 68,818 6,329.1 −0.082
2026-09-11 6,849 75,667 6,229.6 −0.092
2026-09-12 6,939 82,606 6,177.1 −0.050
2026-09-13 5,988 88,594 6,164.7 −0.014

Dialect: the WINDOW clause, frames and lag look the same in Hive, Spark SQL, MySQL 8 and PostgreSQL. Dividing by zero stops the query in PostgreSQL, and in Spark 4, whose default ANSI mode makes it an error; other engines return NULL. The * 1.0 makes the division decimal where an integer divided by an integer stays an integer.

S8. Remove duplicate rows: keep one row per event ID, or the latest row per key.
★ · New · Background: Chapter 3, Chapter 8 · Appendix A: Latest row per key

Copies usually come from at-least-once delivery: the same event, with the same event_id, arrives twice. Number the rows inside each key with row_number(), ordered by the rule that decides which copy wins, and keep row 1. For “the latest row per key”, such as each order’s newest status in a change log, use the same pattern with ORDER BY updated_at DESC and a tie-breaker. SELECT DISTINCT * is not enough: copies that differ in any column, such as their arrival time, survive it.

SELECT event_id, event_name, user_id, event_time, ingest_time
FROM (
  SELECT *,
         row_number() OVER (
           PARTITION BY event_id
           ORDER BY ingest_time, "offset"  -- first copy wins
         ) AS copy_no
  FROM ods_events
  WHERE dt = DATE '2026-09-14'
) AS numbered
WHERE copy_no = 1;

Then check: the rows kept must equal the number of distinct keys. One day of Steep’s raw events:

SELECT count(*)                 AS raw_rows,
       count(DISTINCT event_id) AS distinct_event_ids
FROM ods_events
WHERE dt = DATE '2026-09-14';
raw_rows distinct_event_ids
51,927 51,779

Dialect: offset is a reserved word in DuckDB and PostgreSQL, so it needs double quotes there; in Hive and Spark SQL, backticks are safe but usually not needed. Spark DataFrames also offer dropDuplicates(["event_id"]), but it does not promise which copy it keeps.

S9. Find users who logged in on at least three consecutive days (连续登录).
★★ · New · Background: Chapter 3 · Appendix A: Days in a row

This is a gaps-and-islands problem: find the runs (“islands”) of consecutive days. Keep one row per user per day, number each user’s days in date order, and subtract the number from the date. Inside a run both go up by one each day, so login_date - rn stays the same, and it jumps after every gap. Group by user and that value to get each run and its length. The test table, then a query that lists every run of three days or more:

CREATE TABLE logins AS
SELECT user_id, CAST(login_date AS DATE) AS login_date
FROM (VALUES
  ('u1', '2026-08-30'), ('u1', '2026-08-31'), ('u1', '2026-08-31'),
  ('u1', '2026-09-01'), ('u1', '2026-09-05'),
  ('u2', '2026-09-01'), ('u2', '2026-09-02'), ('u2', '2026-09-04'),
  ('u2', '2026-09-05'),
  ('u3', '2026-09-03'), ('u3', '2026-09-04'), ('u3', '2026-09-05'),
  ('u3', '2026-09-06'), ('u3', '2026-09-09')
) AS t(user_id, login_date);
WITH days AS (                 -- one row per user per day
  SELECT DISTINCT user_id, login_date FROM logins
),
numbered AS (
  SELECT user_id, login_date,
         CAST(row_number() OVER (
                PARTITION BY user_id ORDER BY login_date
              ) AS INTEGER) AS rn
  FROM days
),
runs AS (               -- date - rn stays the same within a run
  SELECT user_id,
         min(login_date) AS first_day,
         max(login_date) AS last_day,
         count(*)        AS days_in_a_row
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT user_id, first_day, last_day, days_in_a_row
FROM runs
WHERE days_in_a_row >= 3
ORDER BY user_id, first_day;
user_id first_day last_day days_in_a_row
u1 2026-08-30 2026-09-01 3
u3 2026-09-03 2026-09-06 4

The question asks for users, so finish with:

SELECT DISTINCT user_id        -- replaces the last SELECT above
FROM runs
WHERE days_in_a_row >= 3
ORDER BY user_id;
user_id
u1
u3

The longest streak per user is the max of the run lengths. Dialect: DuckDB needs the CAST … AS INTEGER, because it subtracts only an integer from a date. Hive and Spark SQL write date_sub(login_date, rn); MySQL writes DATE_SUB(login_date, INTERVAL rn DAY). Never subtract day-of-month numbers: 31 August and 1 September are consecutive, as u1 shows.

S10. Find the customers who ordered in August but not in September.
★★ · New · Background: Chapter 3

This is an anti-join: keep the rows of one set that have no partner in another. NOT EXISTS and LEFT JOIN … WHERE … IS NULL work in DuckDB, Hive, Spark SQL, MySQL and PostgreSQL. First fix the definitions: which orders count (here, completed ones) and which clock sets the month (here, the store’s local time).

WITH aug AS (
  SELECT DISTINCT user_id FROM orders
  WHERE status = 'completed'
    AND created_at_local >= TIMESTAMP '2026-08-01'
    AND created_at_local <  TIMESTAMP '2026-09-01'
),
sep AS (
  SELECT DISTINCT user_id FROM orders
  WHERE status = 'completed'
    AND created_at_local >= TIMESTAMP '2026-09-01'
    AND created_at_local <  TIMESTAMP '2026-10-01'
)
SELECT count(*) AS ordered_in_aug_not_sep
FROM aug AS a
WHERE NOT EXISTS (
  SELECT 1 FROM sep AS s WHERE s.user_id = a.user_id
);
ordered_in_aug_not_sep
10,732

The LEFT JOIN version gives the same count, and so does EXCEPT (DuckDB, Hive 2.3 or later, Spark SQL, PostgreSQL, MySQL 8.0.31 or later):

-- the same aug and sep as above
SELECT count(*) AS ordered_in_aug_not_sep
FROM aug AS a
LEFT JOIN sep AS s ON s.user_id = a.user_id
WHERE s.user_id IS NULL;

The trap is NOT IN. If the list holds even one NULL, user_id NOT IN (…) is never true, so the filter keeps no rows and the count is 0:

-- the same aug and sep as above
SELECT count(*) AS ordered_in_aug_not_sep
FROM aug
WHERE user_id NOT IN (
  SELECT user_id FROM sep
  UNION ALL SELECT NULL          -- one NULL in the list
);
ordered_in_aug_not_sep
0

Spark SQL also has a LEFT ANTI JOIN keyword.

S11. Split each user’s events into sessions, with a new session after 30 minutes of inactivity.
★★★ · New · Background: Chapter 3, Chapter 7

Agree on the rule first: here, a new session starts after more than 30 minutes without an event (a common choice, not a law). For each event, read the same user’s previous event time with lag(). Mark the event 1 when there is no previous event or the gap is over 30 minutes, and 0 otherwise. A running sum of the marks numbers the sessions 1, 2, 3 for each user. Order both windows by time and then by event_id, so two events with the same time always come out in the same order.

CREATE TABLE clicks AS
SELECT event_id, user_id, CAST(event_time AS TIMESTAMP) AS event_time
FROM (VALUES
  ('e1', 'u1', '2026-09-14 09:00:00'),
  ('e2', 'u1', '2026-09-14 09:10:00'),
  ('e3', 'u1', '2026-09-14 09:50:00'),
  ('e4', 'u1', '2026-09-14 09:55:00'),
  ('e5', 'u2', '2026-09-14 10:00:00'),
  ('e6', 'u2', '2026-09-14 10:29:00'),
  ('e7', 'u2', '2026-09-14 11:00:00'),
  ('e8', 'u2', '2026-09-14 23:50:00'),
  ('e9', 'u2', '2026-09-15 00:10:00')
) AS t(event_id, user_id, event_time);
WITH gaps AS (
  SELECT user_id, event_id, event_time,
         lag(event_time) OVER (
           PARTITION BY user_id
           ORDER BY event_time, event_id) AS prev_time
  FROM clicks
),
marked AS (
  SELECT user_id, event_id, event_time,
         CASE WHEN prev_time IS NULL
                OR event_time - prev_time > INTERVAL 30 MINUTE
              THEN 1 ELSE 0 END AS new_session
  FROM gaps
)
SELECT user_id, event_time,
       sum(new_session) OVER (
         PARTITION BY user_id
         ORDER BY event_time, event_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_no
FROM marked
ORDER BY user_id, event_time, event_id;
user_id event_time session_no
u1 2026-09-14 09:00 1
u1 2026-09-14 09:10 1
u1 2026-09-14 09:50 2
u1 2026-09-14 09:55 2
u2 2026-09-14 10:00 1
u2 2026-09-14 10:29 1
u2 2026-09-14 11:00 2
u2 2026-09-14 23:50 3
u2 2026-09-15 00:10 3

From here, group by user and session number for each session’s start, end and number of events. Never group by day first: u2’s last session crosses midnight and stays one session. Dialect: in Hive and Spark SQL, write the gap test as unix_timestamp(event_time) - unix_timestamp(prev_time) > 30 * 60; in MySQL, TIMESTAMPDIFF(SECOND, prev_time, event_time) > 1800.

S12. Turn rows into columns and back (行转列 / 列转行).
★★ · New · Background: Chapter 3, Chapter 10

Rows to columns (pivot): one sum(CASE WHEN …) per new column, grouped by the row key. Last week’s completed orders, one row per city:

SELECT city,
       sum(CASE WHEN platform = 'ios'     THEN 1 ELSE 0 END) AS ios,
       sum(CASE WHEN platform = 'android' THEN 1 ELSE 0 END) AS android,
       sum(CASE WHEN platform = 'web'     THEN 1 ELSE 0 END) AS web
FROM orders
WHERE status = 'completed'
  AND created_at_local >= TIMESTAMP '2026-09-07'
  AND created_at_local <  TIMESTAMP '2026-09-14'
GROUP BY city
ORDER BY city;
city ios android web
harbor 10,007 5,627 1,602
northgate 6,654 3,829 1,150
oldtown 4,575 2,470 755
riverside 3,851 2,042 591

Columns to rows (unpivot): one SELECT per column, stacked with UNION ALL. From that result, saved as wide:

SELECT city, 'ios' AS platform, ios AS orders FROM wide
UNION ALL
SELECT city, 'android', android FROM wide
UNION ALL
SELECT city, 'web', web FROM wide
ORDER BY city, platform;
city platform orders
harbor android 5,627
harbor ios 10,007
harbor web 1,602
… … …
riverside android 2,042
riverside ios 3,851
riverside web 591

First 3 and last 3 of 12 rows.

Hive and Spark SQL often write the unpivot as LATERAL VIEW explode(map('ios', ios, 'android', android, 'web', web)) t AS platform, orders. Many values into one string, and back: Hive and Spark SQL write concat_ws(',', sort_array(collect_set(…))); collect_list keeps repeats, and neither promises an order, hence sort_array. DuckDB uses string_agg. The way back is LATERAL VIEW explode(split(login_days, ',')) in Hive and Spark SQL, and unnest(string_split(login_days, ',')) in DuckDB. On the logins table from S9:

SELECT user_id,
       string_agg(DISTINCT CAST(login_date AS VARCHAR), ','
                  ORDER BY CAST(login_date AS VARCHAR)) AS login_days
FROM logins
GROUP BY user_id
ORDER BY user_id;
user_id login_days
u1 2026-08-30,2026-08-31,2026-09-01,2026-09-05
u2 2026-09-01,2026-09-02,2026-09-04,2026-09-05
u3 2026-09-03,2026-09-04,2026-09-05,2026-09-06,2026-09-09

S13. Which users did A and then B within 7 days? Give the conversion at each step.
★★ · New · Background: Chapter 6, Chapter 3 · Appendix A: Funnel

Fix three choices before writing SQL. The anchor: the clock starts at each user’s first step-1 event in the period. Order: step 2 must come after step 1, and step 3 after step 2. The window: every step must happen within 7 days of step 1, and the data must reach 7 days past the last start. Then build one CTE per step, each joining the events after the previous step’s time. Here: the menu, then the cart, then checkout, for users whose first menu view was in the week of 14 September:

WITH ev AS (
  SELECT user_id, event_name, event_time
  FROM dwd_event_detail
  WHERE event_time >= TIMESTAMP '2026-09-14'
    AND event_time <  TIMESTAMP '2026-09-28'
),
step1 AS (              -- first menu view in the week of 14 Sep
  SELECT user_id, min(event_time) AS t1
  FROM ev
  WHERE event_name = 'view_menu'
    AND event_time < TIMESTAMP '2026-09-21'
  GROUP BY user_id
),
step2 AS (              -- then an add-to-cart within 7 days
  SELECT s.user_id, s.t1, min(e.event_time) AS t2
  FROM step1 AS s
  JOIN ev AS e
    ON e.user_id = s.user_id
   AND e.event_name = 'add_to_cart'
   AND e.event_time >= s.t1
   AND e.event_time <  s.t1 + INTERVAL 7 DAY
  GROUP BY s.user_id, s.t1
),
step3 AS (              -- then a checkout, still within 7 days
  SELECT DISTINCT s.user_id
  FROM step2 AS s
  JOIN ev AS e
    ON e.user_id = s.user_id
   AND e.event_name = 'checkout_start'
   AND e.event_time >= s.t2
   AND e.event_time <  s.t1 + INTERVAL 7 DAY
)
SELECT n1 AS menu, n2 AS then_cart, n3 AS then_checkout,
       round(100.0 * n2 / n1, 1) AS pct_step_2,
       round(100.0 * n3 / n2, 1) AS pct_step_3
FROM (SELECT (SELECT count(*) FROM step1) AS n1,
             (SELECT count(*) FROM step2) AS n2,
             (SELECT count(*) FROM step3) AS n3) AS n;
menu then_cart then_checkout pct_step_2 pct_step_3
49,705 44,255 39,342 89 88.90

Step conversion divides each step by the one before it; overall conversion divides by step 1. Dialect: Spark SQL writes s.t1 + INTERVAL 7 DAYS; a portable test is unix_timestamp(e.event_time) - unix_timestamp(s.t1) < 7 * 86400.

S14. An AI assistant wrote a query for you. How do you check it before you trust the number?
★★ · New · Background: Chapter 3, Chapter 15

Treat it like SQL from a new colleague: it looks right, so it needs proof.

  • Grain and joins: check the grain of every table and every join key, and compare count(*) before and after each join (S4).
  • Definitions: check statuses, time zones, whether date bounds include or exclude the end, and NULL handling (NOT IN, a WHERE on the right side of a LEFT JOIN).
  • Dialect: check functions that change answers between engines, such as datediff argument order, integer division and the default NULL sort order.
  • Known answers: run it on a small table where you know the answer, and reconcile the total with a trusted number, such as finance’s monthly total.
  • Data: do not paste confidential data or customer details into an outside tool.
NoteInterview Corner · Indexes and MySQL (asked more of data engineers and backend-leaning roles)

S15. Why does MySQL’s InnoDB use B+ trees, and not B-trees, binary trees or hash tables?
★★★ · Story and details: Chapter 9

Data is read in pages (16 KB by default in InnoDB), so the goal is to find a row in as few page reads as possible. Each inner page of a B+ tree has hundreds of children, so even tens of millions of rows need only three or four levels, and the top levels stay in memory. Records live only in the leaves, so the upper pages hold keys alone and fit more of them; the leaves are linked in key order, so range scans and ORDER BY walk along them. A binary tree, even a balanced one, is about log₂ n levels deep: 24 levels for 10 million keys, and each level can cost a page read. A hash index finds one exact key fast but keeps no order, so it cannot serve ranges, sorting or a leftmost prefix.

S16. What is the difference between a clustered and a secondary index? What is 回表?
★★ · Story and details: Chapter 9

In InnoDB, the clustered index is the table: a B+ tree on the primary key whose leaf pages hold the whole rows, so there is exactly one per table. (Without a primary key, InnoDB uses the first unique index whose columns are all NOT NULL, or else a hidden row ID.) A secondary index stores its own columns plus the primary key. A lookup through it finds the primary key first, then searches the clustered index for the row: that second search is 回表, “back to the table”. A short primary key keeps every secondary index small, and a covering index (S17) avoids the second search.

S17. What is a covering index?
★ · Story and details: Chapter 9

An index that holds every column a query needs, so the query is answered from the index alone, with no 回表. Example: with an index on (store_id, created_at), counting one store’s orders in one hour reads only the index. In InnoDB every secondary index also carries the primary key, so a query that also returns order_id is still covered. MySQL’s EXPLAIN shows Using index in its Extra column. Do not confuse it with Using index condition (S18), where the rows are still read.

S18. Explain the leftmost-prefix rule with an example.
★★ · Story and details: Chapter 9

A composite index on (a, b, c) is sorted by a, then b, then c. It can narrow a search on a; on a and b; and on all three, but not on b or c alone, because those values are scattered across the index. Example: an index on (store_id, created_at, status) helps WHERE store_id = 'HBR-01' AND created_at >= …, but not a filter on created_at alone. After a range condition, later columns (here status) can no longer narrow the search; they can only filter. The order of the conditions in WHERE does not matter. With WHERE a = 1 AND c = 3, only a narrows the search, and MySQL checks c inside the index before reading the row: index condition pushdown (索引下推), shown as Using index condition in EXPLAIN. (Since MySQL 8.0.13, a skip scan can sometimes use the index without its first column, when a has few values and the query reads only indexed columns; EXPLAIN then shows Using index for skip scan.)

S19. When does a query stop using an index?
★★ · Story and details: Chapter 9

Often, but not always, since optimisers differ. Common cases: a function or expression on the column, such as WHERE DATE(created_at) = … (write a range on the column instead); a LIKE pattern that starts with %; a type mismatch, such as a text column compared with a number; a missing leftmost column of a composite index; an OR across different columns, unless the optimiser merges two indexes; and a query that touches a large share of the table, where a full scan is cheaper. Check with EXPLAIN instead of guessing.

Data engineering

From the app to the warehouse: tracking, Kafka, storage, Hive and Spark, table formats, warehouse design, pipelines, streaming and data quality. Tool behaviour changes between versions, so the answers name the version where it matters.

NoteInterview Corner · Sources and Kafka

D1. Client-side or server-side tracking: what are the pros and cons?
★ · Story and details: Chapter 7

Client-side events see what happens on the device, including taps and screens that never reach a server. But they travel far: some are lost (offline phones, apps closed early, blocked scripts on the web), some arrive late or twice, phone clocks can be wrong, and a fix needs a new app release while old versions stay in use. Server-side events come from systems the company controls, so they are more complete and easier to fix, but they cannot see what happens only on the device. A common design takes facts about money from the server or the database, behaviour from the client, and joins them by shared keys such as order_id and session_id.

D2. What is change data capture, and why use it instead of nightly dumps?
★★ · Story and details: Chapter 7

Log-based change data capture (CDC) reads the database’s own change log (MySQL’s binlog, PostgreSQL’s write-ahead log) and passes on every committed insert, update and delete, in commit order, usually within seconds. A nightly dump shows only the state at dump time: it misses changes in between and deleted rows, it is a day late, and scanning whole tables loads production. Polling an updated_at column also misses deletes and in-between states. If you store the changes, you can replay them from a snapshot to rebuild the table at any past moment. The costs: more systems to run and watch (a connector such as Debezium, often Kafka), schema changes, a first full snapshot, and a source log kept only for a limited time, so a connector that falls too far behind must start again.

D3. How would you design a tracking plan?
★★ · Story and details: Chapter 7

Start from the decisions the data must support, then list the events they need. For each event, write a name that follows one pattern (object then action, such as menu_viewed), the exact moment it fires, its properties and their types, the keys that link it to other data (user_id, session_id, order_id), the platforms and an owner. Version the plan, and test new app builds against it before release. In production, reconcile the events every day with the system of record, such as orders in the database, and alert when the match drops.

D4. How does Kafka keep messages in order?
★★ · Story and details: Chapter 8

Only within a partition: consumers read each partition in offset order, and there is no order across partitions. Give related messages the same key (for example the user ID), so they land in the same partition, and avoid changing the number of partitions, because that moves keys to other partitions. With retries on and several requests in flight, a batch that fails and is resent can land after a later batch. The idempotent producer gives each batch a sequence number per partition, so the broker rejects one that arrives out of order and the order holds (max.in.flight.requests.per.connection must then be 5 or less). A consumer that processes one partition with several threads can still break the order itself.

D5. What happens when a consumer in a group crashes?
★★ · Story and details: Chapter 8

The member stops sending heartbeats. After the session timeout (session.timeout.ms, 45 seconds by default since Kafka 3.0) the group removes it; until then, nobody reads its partitions. A rebalance then shares its partitions among the members that are still alive. They start from the last committed offset, so messages processed after that commit are processed again. So processing must be idempotent, or copies must be removed later by a unique ID. (With the newer consumer group protocol, group.protocol=consumer, the broker setting group.consumer.session.timeout.ms sets the timeout instead.)

D6. How do you avoid losing messages in Kafka?
★★★ · Story and details: Chapter 8

Keep several copies of every message, make the producer wait until the copies exist, and let the consumer save its place only after its work is done. Producer: acks=all, retries on, idempotence on, and check the result of every send. Topic: a replication factor of 3 and min.insync.replicas=2, so a write succeeds only when at least two copies have it; keep unclean.leader.election.enable=false (the default). Consumer: commit offsets after processing, not before, and monitor lag. Since Kafka 3.0 the Java producer defaults to acks=all and idempotence on (early 3.x patch versions had a bug, so check); librdkafka clients such as confluent-kafka do not turn idempotence on by default.

D7. What are the ISR and the high watermark in Kafka, and why is Kafka fast?
★★★ · New · Background: Chapter 8

Each partition has a leader and followers. The in-sync replicas (ISR) are the copies that have caught up with the leader recently enough (within replica.lag.time.max.ms). With acks=all, the leader confirms a write only when every replica in the ISR has it, and min.insync.replicas sets how small the ISR may get before such writes are refused. The high watermark marks how far every in-sync copy has the messages; consumers read only up to it, so they never see a message that could vanish if the leader failed.

Kafka is fast for five reasons:

  • An append-only log: each partition is a file that only grows at its end.
  • Sequential disk access: writes and reads move through the file in order, which disks do fastest.
  • The page cache: the operating system keeps recent data in memory, so most reads never touch the disk.
  • Batching and compression: producers send many messages together, in one compressed batch.
  • Zero-copy: file data goes straight to the network without passing through the program (not possible with TLS).

Partitions then spread the load over many machines.

NoteInterview Corner · Storage and distributed compute

D8. OLTP and OLAP: what is the difference, and why keep them apart?
★ · New · Background: Chapter 9

OLTP (online transaction processing) is the work of the live application: many small reads and writes, each about one record, each in milliseconds, such as saving an order with its payment. OLAP (online analytical processing) is analysis: a few large queries that read a few columns of many rows and add them up. OLTP databases such as MySQL and PostgreSQL store rows and use B+ tree indexes; OLAP systems (ClickHouse, Doris, Snowflake, or Hive and Spark over Parquet files) store columns, compress them and scan in parallel. Keep them apart because a big analysis query on production slows the system customers are using: analysts read a replica, the lake or the warehouse, fed by CDC (D2). Systems sold as HTAP try to serve both, usually by keeping a row copy and a column copy of the data.

D9. Why is columnar storage faster for analytics?
★ · Story and details: Chapter 9

Analytical queries read a few columns of many rows. A column store reads only those columns, and the values of one column look alike, so they compress well (dictionary and run-length encoding, then a codec). Fewer bytes from disk means less time. Parquet and ORC also keep minimum and maximum values per chunk, so a reader can skip chunks that cannot match the filter, and engines process a column in batches, which suits modern processors. The price: writing or reading one whole row touches every column, so OLTP systems keep rows.

D10. ORC or Parquet?
★★ · Story and details: Chapter 9

Both are open, columnar, compressed and self-describing, with statistics for skipping, and both can store Bloom filters. ORC grew up in Hive: it uses large stripes, keeps statistics for every 10,000 rows by default, and is the format behind Hive’s transactional (ACID) tables. Parquet uses row groups, column chunks and pages, can keep statistics for every page, handles nested data well, and has the widest engine support: it is Spark’s default data source and the usual file format under Iceberg and Delta Lake. Choose what your engines support best; today that is usually Parquet, unless the stack is built around Hive.

D11. What is the small-files problem, and how do you fix it?
★★ · Story and details: Chapter 9

Too many files that are much smaller than the storage block or the recommended row-group size. Each file costs a lookup, an open and a footer read, and sometimes a task of its own; in HDFS, one server (the NameNode) keeps a record of every file in memory; and small files compress worse. Causes: too many partitions, streaming jobs that write often, and too many parallel writers.

  • When writing: compact on a schedule, control how many tasks write each partition, use coarser partitions, or use a table format with built-in compaction.
  • When reading: let the engine pack many small files into one task (Hive’s CombineHiveInputFormat, Spark’s spark.sql.files.maxPartitionBytes). This saves tasks, but not the cost of opening each file.

D12. B+ tree or LSM tree: which suits what?
★★★ · New · Background: Chapter 9, Chapter 11

  • B+ tree: updates pages in place. Reads are fast and predictable (one path from the root to a leaf), and range scans walk the linked leaves; the costs are random writes and page splits.
  • LSM tree (log-structured merge tree): appends instead. Writes go to a log and a sorted table in memory, which is written to disk as an unchangeable sorted file; background compaction merges the files. Writes are fast, but one read may check several files (Bloom filters help single keys, not ranges), and compaction rewrites data several times.
  • Choose: B+ trees for read-heavy OLTP with range queries (InnoDB, PostgreSQL’s B-tree indexes); LSM trees for write-heavy workloads and key-value stores (RocksDB, Cassandra, HBase, Apache Paimon).

Name the trade-off as read, write and space amplification: it is hard to make all three small at once.

D13. Partitioning or bucketing in Hive and Spark: what is the difference?
★★ · New · Background: Chapter 9, Chapter 10

  • Partitioning splits a table into folders by a column’s value, such as dt=2026-09-14, so a query that filters on that column reads only the matching folders (partition pruning). Use it for columns with few values that queries filter on, mostly dates; a column with many values, such as a user ID, creates very many small files.
  • Bucketing splits the rows inside each partition into a fixed number of files by a hash of a column: CLUSTERED BY (user_id) INTO 32 BUCKETS in Hive, bucketBy(32, "user_id") in Spark (only with saveAsTable). Use it for join keys with many values: two tables bucketed (and sorted) the same way can be joined bucket by bucket, without shuffling everything.

Spark’s own bucket layout is not Hive’s, so check before you rely on one engine using the other’s buckets. Iceberg offers bucket(N, col) as a partition transform instead.

D14. How do you handle data skew (数据倾斜) in Hive or Spark?
★★ · Story and details: Chapter 10

Confirm it first: in the Spark UI, the slowest task runs far longer and reads far more shuffle data than the median task; then count rows per key to find the hot keys: keys with far more rows than the others. Remove or separate placeholder keys, such as NULL, an empty string or 0, which often collect many rows, and give join keys the same type, so a failed cast does not create one huge NULL key. Broadcast the small side of a join (a map join in Hive; in Spark, any table below spark.sql.autoBroadcastJoinThreshold, 10 MB by default). In Spark 3.2 and later, AQE splits skewed partitions in sort-merge joins. In Hive, a global count(DISTINCT x) runs in a single reducer: rewrite it as SELECT count(*) FROM (SELECT x FROM t GROUP BY x) d, and use hive.groupby.skewindata or hive.optimize.skewjoin where they fit. More partitions alone do not help one hot key.

Follow-up (★★★): salting. For a hot key that must be joined, add a random number from 0 to N−1 to the key on the big side, and copy each matching row of the small side N times, once per number. For an aggregation, aggregate by the salted key first, then by the real key.

D15. What is the difference between managed (internal) and external Hive tables?
★ · Story and details: Chapter 10

For a managed table, Hive owns the data: DROP TABLE deletes the metadata and the files (the files may go to the trash first), and some features, such as ACID transactions and TRUNCATE, work only on managed tables. For an external table, Hive owns only the metadata: DROP TABLE leaves the files. Use external tables for raw data that other jobs write or other teams share, and run MSCK REPAIR TABLE (or ALTER TABLE … ADD PARTITION) when new partition folders appear. Version notes: since Hive 4.0, the table property external.table.purge makes DROP delete an external table’s files too, and in Hive 3 and later many installations make a plain CREATE TABLE a transactional managed table, so check with DESCRIBE FORMATTED.

D16. What triggers a shuffle in Spark?
★★ · Story and details: Chapter 10

Any step that needs all rows with the same key in one place: aggregations by key (groupBy, reduceByKey), joins (unless one side is broadcast or both sides are already partitioned the same way), distinct, window functions with PARTITION BY, a global sort, and repartition. Such a step is a wide dependency (宽依赖): each output partition needs data from many input partitions. Steps such as select, filter, map and coalesce (to fewer partitions) are narrow dependencies (窄依赖): each output partition needs only one input partition. Spark runs a chain of narrow steps inside one stage, and each shuffle ends a stage and moves data over the network. Fewer and smaller shuffles make faster jobs, so filter and drop columns early, and broadcast small tables.

D17. A Spark job is slow or runs out of memory. How do you tune it?
★★★ · New · Background: Chapter 10

Start from the Spark UI, not from settings: find the slowest stage, then compare its slowest task with the median (skew, D14), and look at shuffle sizes, spill to disk and garbage-collection time.

  • Shuffles: each wide dependency starts a new stage (D16). Filter and drop columns early, and broadcast small tables (spark.sql.autoBroadcastJoinThreshold, 10 MB by default).
  • Partitions: spark.sql.shuffle.partitions is 200 by default. Set it from the data size, so partitions are neither tiny nor huge, or let AQE (on by default since 3.2) merge small partitions and split skewed ones.
  • Memory: an executor out of memory usually means one partition is too big or skewed, a broadcast is too big, or a collect() pulls too much to the driver. Fix those first; then raise spark.executor.memory or spark.executor.memoryOverhead, or give each executor fewer cores, so fewer tasks share its memory.
  • Caching: cache a DataFrame only if it is reused, and unpersist it afterwards.
  • Input: many small files mean many tasks (D11).

D18. Exact or approximate COUNT DISTINCT: when, and how does it work?
★★ · New · Background: Chapter 10, Chapter 12

An exact count(DISTINCT x) must remember every value, and a distributed engine must shuffle the values, which is slow and uses much memory for billions of rows. HyperLogLog keeps a small, fixed-size sketch and estimates the count; its error depends on the sketch size, which engines choose very differently. Trino’s approx_distinct has a standard error of 2.3% by default, and Spark’s approx_count_distinct allows a relative standard deviation of 5% by default (you can ask for less). Sketches can be merged, so daily sketches combine into a monthly count; daily distinct counts cannot be added, because returning users would count twice. For exact counts that still merge, OLAP engines such as Doris and StarRocks keep bitmaps of integer IDs. Use approximate counts for large dashboards and trends only after you know your engine’s error, and exact ones for money and anything audited. On Steep’s app events, DuckDB’s approx_count_distinct is off by 8.2%, more than you might expect:

SELECT count(DISTINCT user_id)        AS exact_users,
       approx_count_distinct(user_id) AS approx_users
FROM dwd_event_detail;
exact_users approx_users
88,223 80,985
NoteInterview Corner · Lakehouse and warehouse design

D19. Why did teams move from plain Hive tables to Iceberg, Hudi or Delta Lake?
★★ · Story and details: Chapter 11

Plain Hive tables track only partitions. Changing one row means rewriting its partition, readers can see a rewrite half done, two writers can overwrite each other, and nothing keeps the old version. Planning a query means listing folders, which is slow on object storage. Table formats track every data file in snapshot metadata. That gives ACID commits; row-level UPDATE, DELETE and MERGE (copy-on-write or merge-on-read); time travel and rollback; schema and partition changes without rewriting data; file statistics for skipping; and built-in compaction, in open formats that many engines can share.

D20. What is time travel useful for?
★ · Story and details: Chapter 11

Reproducing a report exactly as it was first published, for an audit or a dispute. Debugging: compare today’s snapshot with yesterday’s to see what changed. Rolling back a bad write by making an older snapshot current again. Keeping a machine-learning training set reproducible. Its limits: it reaches back only as far as snapshots are kept, it follows commit time rather than business time, and it is not a backup.

D21. Data lake, data warehouse, lakehouse: what is the difference?
★ · Story and details: Chapter 11

A lake stores files of any kind in cheap storage, applies the schema when reading, and lets many engines share them, but on its own it has no transactions and weak control over what the files mean. A warehouse stores managed tables, checks the schema on write, supports transactions and fast SQL, and governs access, but it costs more and keeps the data in its own format. A lakehouse puts an open table format and a catalog on top of lake storage, so open files behave like warehouse tables: one copy of the data for dashboards, SQL and machine learning.

D22. Why build a warehouse in layers, such as ODS, DWD, DWS and ADS? (为什么要分层?)
★★ · Story and details: Chapter 12

Each layer has one job. ODS keeps a raw copy, so you can always rebuild; DWD cleans and standardises at the finest grain; DWS builds shared summaries at common grains; each ADS table serves one report or one team. The benefits: clean once and reuse many times; a change in a source touches one layer; any number can be traced down (lineage); summaries make common questions cheap; and each layer has a clear owner. Without layers, every report reads raw data on its own, a style Chinese teams call “chimney” development (烟囱式开发). The costs are more tables, more jobs and more delay, so each layer reads only from the layers below it, and exceptions are written down.

D23. Design the tables for the order flow (dimensional modelling).
★★ · New · Background: Chapter 12

Follow Kimball’s four steps, and say each one out loud.

  1. Business process: placing and fulfilling an order.
  2. Grain: one row per order line (one drink in one order) for the main fact table. Fix this before anything else.
  3. Dimensions: date, time of day, customer (tier history as SCD type 2), store, city, menu item, platform, payment method, promotion.
  4. Facts: quantity, unit price, discount and net amount. They are additive: they can be summed over every dimension.

Then add the other two kinds of fact table. A periodic snapshot has one row per store per day, such as orders and stock on hand; stock is semi-additive, because it adds across stores but not across days. An accumulating snapshot has one row per order, updated as it moves through placed, paid, delivered and refunded, with a time for each step, for questions about time between steps. Keep ratios such as average order value out of fact tables (they are non-additive): store their parts, and divide in the query.

D24. Write SQL to build a zipper table, and to update it every night. (拉链表怎么实现?)
★★★ · Story and details: Chapter 12

A zipper table (拉链表) is a slowly changing dimension of type 2: one row per version of a member’s value, with a start_date, an end_date (9999-12-31 for the version still true) and is_current.

Build it from a change log: keep one change per member per day, then end each version the day before the next one starts, with lead(). Without the first step, two changes on the same day give a row whose end_date is before its start_date.

WITH one_per_day AS (   -- keep the last change per member per day
  SELECT user_id, new_tier, effective_date
  FROM (
    SELECT *,
           row_number() OVER (
             PARTITION BY user_id, effective_date
             ORDER BY changed_at DESC) AS rn
    FROM user_tier_changes
  ) AS c
  WHERE rn = 1
)
SELECT user_id,
       new_tier       AS loyalty_tier,
       effective_date AS start_date,
       coalesce(
         lead(effective_date) OVER (
           PARTITION BY user_id ORDER BY effective_date) - 1,
         DATE '9999-12-31')  AS end_date,
       lead(effective_date) OVER (
         PARTITION BY user_id ORDER BY effective_date)
         IS NULL             AS is_current
FROM one_per_day;

On Steep’s loyalty-tier change log this returns 232,377 rows, the same rows as the warehouse’s dwd_user_zipper. (Hive and Spark SQL write date_sub(lead(…), 1).)

Use it with WHERE d BETWEEN start_date AND end_date for “as of day d”.

Update it every night, one partition per night. Read last night’s partition and tonight’s full copy of the users table. Close the open row of each member who changed or left, and open a new row for each member who changed or joined. UNION ALL the parts and INSERT OVERWRITE tonight’s partition, so a rerun gives the same result (the full SQL is in Chapter 12). A change dated in the past needs a rebuild of that member from the change log.

Test it every night: no gaps or overlaps, never two open rows for one member, and an open row for every member in tonight’s copy (a member who left has none):

WITH ordered AS (
  SELECT user_id, start_date, end_date,
         lead(start_date) OVER (
           PARTITION BY user_id ORDER BY start_date) AS next_start
  FROM dwd_user_zipper
),
open_rows AS (
  SELECT user_id, count(*) AS n_open
  FROM dwd_user_zipper
  WHERE end_date = DATE '9999-12-31'
  GROUP BY user_id
),
tonight AS (              -- members in the latest users copy
  SELECT user_id
  FROM ods_users_snapshot
  WHERE dt = (SELECT max(dt) FROM ods_users_snapshot)
    AND loyalty_tier IS NOT NULL
)
SELECT
  (SELECT count(*) FROM ordered
   WHERE next_start IS NOT NULL
     AND next_start <> end_date + 1) AS gaps_or_overlaps,
  (SELECT count(*) FROM open_rows
   WHERE n_open > 1)                 AS two_open_rows,
  (SELECT count(*) FROM tonight AS t
   LEFT JOIN open_rows AS o ON o.user_id = t.user_id
   WHERE o.user_id IS NULL)          AS present_but_no_open_row;
gaps_or_overlaps two_open_rows present_but_no_open_row
0 0 0

D25. Star schema or snowflake schema? (星型模型和雪花模型的区别?)
★ · Story and details: Chapter 12

A star joins the fact table directly to flat dimension tables: one join per dimension, simple queries, fast. A snowflake splits dimensions into smaller tables (store, then city, then region): less repetition and one place to change an attribute, but more joins and harder queries. Kimball recommends stars, and column stores compress repeated values well, so a snowflake saves little space. Many big-data teams go further than a star and copy dimension columns into wide tables in DWD and DWS, trading storage and update work for queries with no joins.

NoteInterview Corner · Pipelines, streaming and quality

D26. How do you make a pipeline idempotent? (如何保证任务幂等?)
★★ · Story and details: Chapter 13

Make every task a function of its data date: it reads fixed input partitions for that date and fully replaces its output partition. In Hive, use INSERT OVERWRITE … PARTITION (dt = '…'), never INSERT INTO; elsewhere, delete and insert the partition in one transaction, or MERGE on a unique key. Never read “the latest” data, and never depend on what an earlier run left behind. Then retries and backfills are safe. Prove it with checks after each write: row counts, control totals against the layer below, and a uniqueness test on the grain key.

A Spark trap: spark.sql.sources.partitionOverwriteMode is static by default. An INSERT OVERWRITE that does not name the partition value, or a DataFrame written with mode("overwrite") and partitionBy, first deletes every partition that matches, which can be the whole table. Set the mode to dynamic, or name the partition. (Hive SerDe tables are always overwritten dynamically.)

D27. How do you run a safe backfill? (补数怎么做?)
★★ · Story and details: Chapter 13

Start at the first table whose input changed, and rerun every task downstream of it, so the summaries are rebuilt from the new detail. Check first that every task in the path overwrites its partition: an appending task run twice doubles the data. Choose the date range, and run in order anything that builds on the day before, such as a zipper table or a running total; days that do not depend on each other can run in parallel. Record control totals before and after, and compare them with the layer below. Tell the people who read the tables, and give the backfill a lower priority than the nightly run. Schedulers support this: Airflow 3 has airflow backfill create (Airflow 2: airflow dags backfill), DolphinScheduler has “complement data”, and DataWorks has “data backfill”.

D28. What is the difference between ETL and ELT?
★ · Story and details: Chapter 13

ETL transforms data on its way into the warehouse, on a separate engine, and loads only the result. ELT loads raw data first and transforms it inside the warehouse with SQL. ELT keeps a raw copy, so you can transform it again when the rules change, and it uses the warehouse’s own power; it is the common choice today, and layered warehouses (ODS first, then DWD and above) follow it. ETL is still useful when data must be filtered or masked before it may be stored, or when the target cannot do heavy work.

D29. What is a watermark?
★★ · Story and details: Chapter 14

A marker in a stream that says how far event time, the time things happened, has progressed. A watermark with time t means: no more events with a time at or before t are expected. Event-time windows fire when the watermark passes their end. A common way to make watermarks is the largest event time seen, minus a fixed delay (bounded out-of-orderness); the delay trades freshness against completeness. Events that come after the watermark are late: in Flink they are dropped by default, kept with allowed lateness, or sent to a side output for a later fix.

D30. How does Flink achieve exactly-once?
★★★ · Story and details: Chapter 14

With checkpoints. Flink regularly saves the state of every step together with its position in each input, such as the Kafka offsets. After a failure it loads the last checkpoint and reads again from those positions, so every event affects the state once, even if it is read twice. Results that leave the job need the sink’s help. Flink’s Kafka sink in exactly-once mode (off by default) writes each checkpoint’s results in a Kafka transaction and commits it when the checkpoint completes: a two-phase commit. Readers must use isolation.level=read_committed, and the transaction timeout must be longer than the longest checkpoint plus the longest restart. An idempotent sink, such as an upsert by key, also works. None of this removes copies that were already in the source: remove those by a unique ID.

D31. Lambda or Kappa architecture?
★★ · Story and details: Chapter 14

Lambda runs two paths: a stream path for fresh, approximate results and a batch path that recomputes exact results and replaces them. It is robust, but the same logic lives in two code bases that must agree, and they tend to drift apart. Kappa keeps only the stream path and recomputes by replaying the log through a new version of the job. It is simpler, but it needs long retention and fast replays. Many teams mix them: the stream for alerts and live screens, batch for finance and official reports. Many teams also aim for 流批一体 (one design for stream and batch): Flink writes into a lakehouse table such as Paimon or Iceberg, and batch jobs read the same table, so there is one copy of the data.

D32. A live GMV screen and a daily finance report: design the pipeline.
★★★ · New · Background: Chapter 8, Chapter 11, Chapter 13, Chapter 14, Chapter 15

Start with the two promises: the screen must be fresh, and finance must be exact.

  • Source: order changes from the orders database by CDC (binlog to Kafka), not app events, because the money facts live in the database.
  • Live path (实时数仓): Flink reads the topic, removes copies by order ID, keeps each order’s latest status, and sums GMV per minute and city with event-time windows and a watermark. It upserts each result by key (minute, city) into a serving store such as Redis, ClickHouse, Doris or Hologres, so a result written twice does no harm. Label the screen “live estimate”.
  • Batch path: the same changes land in the lake (ODS), and the nightly jobs build DWD and DWS with final statuses, refunds and late data. The finance report reads only these tables, built with partition overwrites so reruns and backfills are safe.
  • Trust: every morning, compare the live total with the batch total, and alert when the gap passes a set limit. Add lag and freshness alerts on Kafka and Flink, and data tests on the batch tables.

D33. How do you monitor data quality in a pipeline?
★★ · Story and details: Chapter 15

With tests after each step, grouped by what they protect: not null, unique and accepted values on keys and codes; relationships between tables; freshness; volume and anomaly checks against history; and reconciliation against an independent source, at the grain where problems appear (for example, per platform and app version). Each test has a threshold set from historical noise, a severity (warn, or stop the pipeline) and an owner who gets the alert. Back-test a new test on past data before you trust it: would it have caught last month’s incident, and how many false alarms would it have raised?

D34. What is a data contract?
★ · Story and details: Chapter 15

A written, versioned agreement between the producers of data (such as an app team) and its consumers. It defines the schema and meaning of each field, when each event fires, the allowed values, quality promises (completeness and freshness targets), the owner, and how changes are announced. Good contracts come with automated tests, so a release that breaks the contract is caught before it ships or right after.

Statistics

The ideas under every experiment and every “is this real?” question. Appendix B has the formulas.

NoteInterview Corner · Chance and causes

T1. Explain Simpson’s paradox with an example.
★★ · Story and details: Chapter 4

A comparison that holds in every group can reverse when you add the groups together. Example: a new menu converts better than the old one in each of four cities, but most of its visits come from the city with the lowest conversion, so its overall rate, a weighted average, is lower. It happens when the two things you compare are spread over the groups differently, and the groups have different base rates. Compare inside groups, or at the same mix (standardisation), and split only by variables that existed before the change.

T2. What does the central limit theorem say, and why does an analyst care?
★★ · New · Background: Chapter 16, Chapter 17

The central limit theorem says that the average of many independent values with a finite variance has a distribution close to a normal (bell) curve, whatever the shape of the single values, with standard deviation \(\sigma/\sqrt{n}\). That is why a normal-based confidence interval works for an average, a conversion rate or a difference between two groups, and why A/B tests on skewed metrics such as revenue per user still work with large samples. It does not make the data itself normal, and “large” depends on the skew: a few huge orders mean you need far more data before the bell appears, so check with a simulation or the bootstrap. It also needs independent units: one user’s orders are related, so analyse per user. A simpler, related result, the law of large numbers, says only that the average gets close to the true mean as \(n\) grows.

T3. What is regression to the mean?
★★ · Story and details: Chapter 16

When a measurement is partly luck, an extreme value is usually followed by a value closer to the average, because the luck does not repeat. It is not a force, and nothing “corrects” itself; the next measurement gets new luck. It matters because it looks like a cause: act after an extreme result, such as a terrible week or the worst store, and the next result will usually look better even if your action did nothing. The fix is a comparison group, or an expectation that does not assume the extreme will continue.

T4. What is a confounder? Give an example.
★ · Story and details: Chapter 16

A third variable that affects both who is in each group and the outcome. Example: customers who used a coupon ordered more the next month. But frequent customers get more coupons and also order more anyway, so past order frequency confounds the comparison: part of the gap would be there without any coupon. The best fix is to randomise who gets the coupon. Otherwise compare like with like (stratify or match on past order frequency), or adjust for it in a regression, using only variables measured before the coupon, never ones the coupon could change.

NoteInterview Corner · Ranges and tests

T5. Explain a 95% confidence interval to a non-technical manager.
★★ · Story and details: Chapter 17

Give the estimate and the range in the units of the decision: “Our best estimate is 3 points; the range is 1 to 5.” Then the meaning: “We built the range with a method that, used again and again on new data, contains the true value about 95 times in 100.” Then the limits: the range covers the luck of which data we happened to see, not a mistake in the data or the model. Avoid “there is a 95% chance the true value is in this range”: the 95% describes the method, not this one range.

T6. What is the difference between standard deviation and standard error?
★ · Story and details: Chapter 17

The standard deviation describes how spread out single values are, for example how much one day’s orders differ from another’s. The standard error describes how much an estimate, such as an average or a regression coefficient, would vary from sample to sample. For an average of \(n\) independent values, SE = SD / \(\sqrt{n}\). The SD stays about the same as you collect more data; the SE shrinks.

T7. When would you use the bootstrap?
★★ · Story and details: Chapter 17

When there is no simple formula for the standard error, or you do not trust the formula’s assumptions: medians, percentiles, ratios, the output of a model, or a number built in several steps. Resample the independent units with replacement (users rather than orders; whole blocks of days when days are linked), recompute the number each time, and read its spread, for example the 2.5th and 97.5th percentiles for a 95% interval. Use a few thousand resamples for an interval. It does poorly with very small samples and with extremes such as the maximum.

T8. What is a p-value?
★ · Story and details: Chapter 18

Assume the null hypothesis is true: nothing changed. The p-value is the probability of a test statistic at least as extreme as the one you observed. It measures how surprising the data are under that assumption. It is not the probability that the null hypothesis is true, and it does not measure the size or importance of an effect: report an interval for that.

T9. What is the difference between a Type I and a Type II error?
★ · Story and details: Chapter 18

A Type I error is a false positive: you reject the null hypothesis when nothing changed. Its rate is alpha, which you choose. A Type II error is a false negative: you miss a real effect. Its rate, beta, depends on the effect size, the noise and the sample size; power is 1 − beta. For a fixed amount of data, lowering alpha raises beta. More data, or less noise, lowers beta at the same alpha, or lets you lower both.

T10. Your test is not significant. What can you conclude?
★★ · Story and details: Chapter 18 · see also Chapter 19

Only that the data did not give strong evidence against the null hypothesis; it is not proof of no effect. Look at the interval for the effect: if it is narrow and close to zero, any effect is probably too small to matter; if it is wide, the data cannot tell. Then check the power the test had for an effect size that matters: with low power, “not significant” means “cannot tell”, not “nothing happened”.

T11. t-test, Welch, chi-square or Mann–Whitney: which test, and one-sided or two-sided?
★★ · New · Background: Chapter 18, Chapter 21 · Appendix B

Start from the metric and the unit you randomised.

  • A conversion rate per user: a two-proportion z-test, or the chi-square test on the 2×2 table, which is the same test (\(z^2 = \chi^2\)).
  • A mean per user, such as revenue: Welch’s t-test, which does not assume equal variances. With large samples it works even for skewed data (the central limit theorem, T2); for very heavy tails, check with a bootstrap.
  • A ratio of totals, such as average order value: the delta method or a user-level bootstrap (X12).
  • Mann–Whitney does not compare means, and compares medians only when the two shapes are the same. It asks whether a value from one group tends to be larger than a value from the other, so use it only for that question, never for revenue totals.

Choose one-sided or two-sided before the test. Two-sided is the default, because harm matters too; one-sided is honest only when a change in the other direction would lead to the same decision.

T12. A fraud rule catches 90% of fraud and flags 5% of normal orders. 1% of orders are fraud. What share of flagged orders is fraud?
★★ · New · Background: Chapter 18

About 15%, not 90%. Count it out for 10,000 orders: 100 are fraud, and the rule flags 90 of them. The other 9,900 are normal, and 5% of them, 495, are flagged too. So 90 of the 585 flagged orders are fraud. When the thing you look for is rare, even a good rule produces mostly false alarms; the share of flags that are real is called precision. Auditors meet this in exception reports: most flagged items are not problems, so the follow-up work must be planned for the flags, not for the fraud.

Experiments and causal inference

Planning a test, reading it honestly, making it faster, and what to do when you cannot randomise. Appendix C turns these answers into checklists.

NoteInterview Corner · Planning a test

X1. Walk me through designing an A/B test.
★★ · Story and details: Chapter 20

Start with a hypothesis that names the change, the metric and the direction. Choose the randomisation unit, usually the user, and check that the groups will not interfere with each other. Pick one primary metric, a few secondary metrics, and guardrails that must not get worse. Size the test from the baseline, the minimum detectable effect, alpha and power, round the length up to whole weeks, write the decision rule down, and run an A/A test or a logging check before the start. At the end, check the split for SRM first, read the primary metric once with its interval, then the guardrails and novelty; decide by the rule, ramp the launch, keep a holdout, and write it up so someone else can reproduce it.

X2. How do you pick the randomisation unit?
★★ · Story and details: Chapter 20

Randomise the unit the question is about, and make sure one unit always gets one experience. For most product changes that is the user: sessions would show both versions to the same person, and one person’s orders are not independent. Analyse at the unit you randomised, or use the delta method for ratio metrics (X12). Use a bigger unit, such as a city or a time slot, only when units would otherwise share resources and spill over into each other (X13), and accept fewer units and wider intervals.

X3. What are guardrail metrics?
★ · Story and details: Chapter 20

Metrics that must not get worse, even if the primary metric improves: for a checkout change, cancellations, refunds, payment errors, revenue per order or page speed. Some protect the business; others protect the test itself, such as the SRM check or a metric the change should barely affect. Read each one’s interval, not only its p-value, and set in advance how much harm is acceptable. Slow guardrails, such as refunds, need a second read once the late data are in.

X4. How do you calculate the sample size for an A/B test?
★★ · Story and details: Chapter 19

Measure the primary metric’s baseline and noise from recent data. Choose the MDE (the smallest effect worth detecting), alpha (often 0.05, two-sided), power (often 80%) and the split. With those settings and a 50/50 split, each group needs about \(16\sigma^2/\delta^2\) users, where \(\sigma^2\) is the metric’s variance per user and \(\delta\) the absolute change; for a conversion rate \(p\), \(\sigma^2 = p(1-p)\). Example: a 10% baseline conversion and a 5% relative MDE (0.5 points) need about 57,600 users per group (the full formula in Appendix B gives about 57,800). Then turn users into days with real traffic, remembering that distinct users grow more slowly than daily visits, because many people come back, and round up to whole weeks.

X5. What is the MDE, and how do you choose it?
★★ · Story and details: Chapter 19

The minimum detectable effect is the smallest true effect the test will detect with the planned power, at the planned alpha. Choose it from the business side first: the smallest effect that would change the decision, or pay for the change. Then check what the traffic allows. If the two do not match, make the trade-off on purpose and write it down, including the power you will have for smaller effects.

X6. Traffic is too low for the MDE you want. What now?
★★ · Story and details: Chapter 19

Options, roughly in order: run longer, in whole weeks; use a 50/50 split and include more of the traffic; pick a metric closer to the change, with less noise (checkout conversion rather than revenue); reduce variance with pre-test data (CUPED, X14); test a bolder change, which has a bigger effect; or accept a larger MDE and say so. Do not run an underpowered test and trust a lucky significant result: the winner’s curse makes such results look bigger than they are.

X7. When should you not run an A/B test?
★★ · New · Background: Chapter 20, Chapter 23

When you cannot randomise well: too few units (one city, a handful of large customers), interference you cannot avoid (X13), or a change that must reach everyone at once, such as a new law. When randomising would be unethical or unlawful, such as holding back a safety fix. When the effect is too slow or too rare to measure in a test, such as brand trust or yearly churn, or the traffic cannot reach a useful MDE. When the decision does not depend on the result: a required bug fix, or a change so cheap to undo that a staged rollout with monitoring is enough. Then use other evidence (a rollout with a holdout, difference-in-differences, regression discontinuity or matching, user research) and say plainly how much weaker it is.

NoteInterview Corner · Reading a test, and its traps

X8. Why does peeking inflate the false-positive rate?
★★ · Story and details: Chapter 21

A 0.05 line promises 5% false positives for one look at a fixed time. With no real effect, the p-value wanders up and down, so stopping at the first value below 0.05 gives many chances to cross. In A/A tests (both groups get the same thing) with 14 daily looks, about 22% cross at least once, and with no limit on the number of looks the rate tends to 100%. Fix the length in advance and read once, or use a sequential method built for repeated looks (X15).

X9. How do you detect and handle a sample ratio mismatch?
★★ · Story and details: Chapter 21

A sample ratio mismatch (SRM) means the groups’ sizes differ from the planned split by more than chance allows. Before reading any result, run a chi-square test of the user counts against the planned split, with a strict alarm level such as 0.001, overall and also by platform and by day. If it fails, do not trust the result and do not reweight it: the missing users are usually not random, for example slow phones that time out before their assignment is logged. Find the cause, fix it, and rerun.

X10. A test shows a +3% lift in week 1 but +0.5% in week 3. What do you report?
★★ · Story and details: Chapter 21

Report the settled effect, about +0.5%, with its interval and whether it includes zero. Call the early lift a likely novelty effect: people try something new, then go back to their habits. Check it by plotting the lift by days since each user’s first exposure, not by calendar week, because the people in the test change over time (frequent users arrive first); the curve should have stopped falling. If it has not, run longer. A holdout that never gets the change can show the long-run effect months later.

X11. You tested 20 metrics and segments, and three are significant. What do you do?
★★ · New · Background: Chapter 21

With 20 tests of pure noise at alpha = 0.05, you expect about one false positive, and the chance of at least one is \(1 - 0.95^{20}\), about 64% (for independent tests). So first count every test you ran, including the ones nobody wrote down. Then correct for the count: Bonferroni (alpha divided by the number of tests) or Holm controls the chance of any false positive; Benjamini–Hochberg controls the expected share of false discoveries and suits a first screen of leads. Better still, choose one primary metric before the test, and treat every segment result as a hypothesis for a new test, not a finding. Expect the biggest of many results to be too big: luck helped push it to the top.

X12. Your metric is a ratio, such as average order value or click-through rate. How do you test it?
★★★ · New · Background: Chapter 21

A ratio metric divides two totals: revenue by orders, clicks by views. It has two traps. First, the bottom number can move: average order value can rise while revenue per user stays flat, because people place fewer, larger orders. So also read the top and the bottom number per user. Second, the unit: the test randomised users, not orders, and one user’s orders are related, so treating each order as independent gives too narrow an interval and too many false alarms. Use the delta method, which computes the ratio’s variance from per-user totals. With \(R = \bar Y / \bar X\) for \(n\) users in a group,

\[\operatorname{Var}(R) \approx \frac{1}{n \bar X^2}\left(s_Y^2 - 2R\, s_{XY} + R^2 s_X^2\right),\]

where \(s_Y^2\), \(s_X^2\) and \(s_{XY}\) are the variances and covariance of the per-user totals. Or bootstrap by user. Both treat users as the independent units.

X13. How can network effects or shared resources break an A/B test, and what do you do?
★★★ · New · Background: Chapter 21

A standard test assumes no interference: each user’s result depends only on their own group. Shared resources and social links break this. In a delivery app, giving group B priority with couriers makes group A slower, so the test shows a gap that a full launch would not deliver; in a social app, a feature for B changes what A sees. Ask whether the groups compete for anything (couriers, stock, budget, attention) or interact. Then randomise a bigger unit that does not share, such as cities or clusters of connected users; run a switchback test, which switches a whole market between A and B in random time slots; or split the shared resource itself, such as a separate budget per group. Each fix gives fewer units, so expect wider intervals.

NoteInterview Corner · Faster tests

X14. What is CUPED, and when does it help?
★★ · Story and details: Chapter 22

CUPED (Controlled-experiment Using Pre-Experiment Data) adjusts each user’s outcome with a covariate measured before the test, usually the same metric in a pre-period: \(Y - \theta(X - \bar X)\), with \(\theta = \operatorname{Cov}(Y, X)/\operatorname{Var}(X)\) estimated on both groups together. The adjusted metric’s variance is \(1 - \rho^2\) times the original, where \(\rho\) is the correlation between \(X\) and \(Y\), with no bias, because the treatment cannot change a value measured before it started. It helps most for metrics the past predicts well, such as orders or revenue per user, and little for weakly predictable ones or for new users with no history. It can also move the estimate when chance gave one group a higher starting value; that correction is part of the point.

X15. How can you stop a test early safely?
★★★ · Story and details: Chapter 22

Use a sequential design chosen before the test. A group-sequential plan uses an alpha-spending function, usually O’Brien–Fleming-type: very strict at early looks and close to the usual line at the end, so the overall false-positive rate stays at alpha and little power is lost. For continuous monitoring, use an always-valid method such as mSPRT (the mixture sequential probability ratio test). Look at whole-week boundaries if the metric has a weekly cycle. Expect an effect that stopped early to be overstated, and confirm it with a holdout. Guardrails can be watched every day with an always-valid p-value, and a test stopped at once for clear harm (Appendix B).

X16. Frequentist or Bayesian A/B testing?
★★ · Story and details: Chapter 22

A frequentist test asks how surprising the data would be if there were no effect, and controls the error rate of a decision rule over many tests. A Bayesian analysis combines a prior with the data and reports direct statements, such as the probability that B is better and the expected loss of choosing B. With a lot of data and a weak prior, the two usually agree. When data are thin, the Bayesian answer depends on the prior, so report the prior you used. Choose by what the decision needs: a known error rate for a launch rule, or direct probabilities and expected loss for a business discussion. Neither makes peeking free: check any stopping rule on A/A tests.

NoteInterview Corner · When you cannot randomise

X17. What are the assumptions of difference-in-differences?
★★ · Story and details: Chapter 23

Parallel trends: without the treatment, both groups would have changed by the same amount on the scale you analyse (often logs, so the same percent). No anticipation: nobody changed behaviour early because they knew the change was coming. No other change hitting only the treated group at the same time, and no spillover to the comparison group. For the interval, allow for noise that is related from day to day within a group, and be careful when there are only a few groups.

X18. How do you check parallel trends?
★★ · Story and details: Chapter 23

You can check it only before the change. Plot both groups over a long pre-period on the analysis scale and see whether they move together. Run placebo tests, with a fake start date or a fake treated group, and check that they find about zero. Then see whether the answer survives other reasonable windows and comparison groups. Passing these checks makes parallel trends believable for the period after the change; nothing can prove them.

X19. When would you use regression discontinuity?
★★★ · Story and details: Chapter 23

When a sharp rule on a measured score gives the treatment, such as a loyalty tier earned at a spending threshold. Compare units a little above and a little below the cutoff, with a trend fitted on each side. It needs units that cannot place themselves exactly on one side, and nothing else changing at the cutoff: check that the number of units and unrelated traits do not jump there, and try several bandwidths. If the cutoff changes only the chance of treatment, use a fuzzy RD. The answer is local: it describes units near the cutoff.

X20. What are the pitfalls of propensity score matching?
★★★ · Story and details: Chapter 23

It balances only what you measured; hidden confounders remain, and you cannot test for them. It needs overlap: treated units with no comparable untreated units must be dropped, which changes the population you describe. The score model can be wrong, so check the balance of each variable after matching. Never match on things the treatment itself changed.

Communication

Every analysis ends with people. These questions test whether your work reaches a decision, and whether you can tell your own story.

NoteInterview Corner · Communication

C1. How would you present an analysis to a CEO?
★★ · Story and details: Chapter 24

Start from the decision they must make and how much time they have. The answer and the recommendation come first. Then two or three points, each tagged as counted, estimated with a range, or judgement, with the effect in money or customers; then the options, the cost of being wrong, and what would change your mind. Use one chart, titled with the finding. Send it a day ahead, and talk first to the people whose work is in it. An appendix ties every number to its query.

C2. How do you explain uncertainty to a non-technical audience?
★★ · Story and details: Chapter 24

First, what it means for the decision: “Even at the bad end of the range, revenue per user fell by at most 1%, and we can change the price back any day.” Then the range in plain words, rounded to what the data support, with what it covers and what it does not. No “likely” without a number; give chances as counts, such as “about one range in twenty misses”. If the range is too wide to decide, say what data would narrow it, how long that would take, and what it would cost.

C3. Your analysis contradicts what a senior leader believes. What do you do?
★★ · Story and details: Chapter 24

First learn why they believe it: they may know something the data does not. Check your own work with a sceptical colleague. Then meet them before any wider meeting, show the evidence, and ask what would convince them. If it is their decision, accept it and record the evidence and the decision. If it is a material risk, such as a wrong number going to the board, raise it through the proper channel.

C4. Tell me about an analysis that changed a decision.
★ · New · Background: Chapter 24, Chapter 1

Use STAR: Situation, Task, Action, Result, in about two minutes, with most of the time on the Action. A sketch: Situation: the weekly dashboard showed orders down sharply, and the team was ready to cut prices. Task: find out why within two days. Action: I compared the dashboard with the orders database, found that one app version had stopped sending an event, and sized the rest of the drop against weather and a recent price change. Result: most of the drop was measurement, the price cut was not made, and a daily check now compares the two sources. Use one real example of your own, give numbers where you can, say what you would do differently, and make your own part clear (“I”, not “we”).

C5. Why move from internal audit into data work?
★ · New · Background: Chapter 1, Chapter 15, Chapter 24

Be honest and specific: what drew you, what you bring, and what you are still learning. For example: “In audit I spent most of my time tracing numbers back to their source, testing controls and writing findings that managers had to act on. The part I enjoyed most was the data work, so I want to do it full time.” Then name the skills that transfer, each with a data-work example: tracing a number to its source (data lineage), sampling and testing (data-quality checks), scepticism toward a single source (reconciliation), and clear written findings (a one-page memo). End with what you have done to learn the skills you were missing, such as SQL practice or a project, and what you still want to learn. Do not criticise your old job; describe the move as a step toward something.

Questions by chapter

Each chapter, its Interview Corner questions, and the other questions that build on the same ideas.