- Home
- Prep by Job Role
- Data Analyst
- Top 100 Q&A
Top 100 Data Analyst Interview Questions and Answers
The questions most likely to come up in your Data Analyst interview, ranked by likelihood, with senior-level answers that cover what the interviewer is listening for.
Curated: · Written: · Reviewed:
Q&A-105Walk me through INNER, LEFT, RIGHT and FULL OUTER JOIN: which rows each keeps, and how the choice changes row counts and any averages computed afterwards?
Joins differ in exactly one thing: what happens to a row that finds no partner. INNER keeps only matched pairs. LEFT keeps every row from the left table, padding right-side columns with NULL where there is no match. RIGHT is LEFT with the tables swapped. FULL OUTER keeps unmatched rows from both sides. The ON predicate and the fan-out behaviour are identical across all four.
Row counts follow directly. With customers (1, 2, 3, 4) and orders (o1→1, o2→1, o3→2, o4→5, o5→5):
| Join | Rows | Composition |
|---|---|---|
| INNER | 3 | o1, o2, o3 |
| LEFT (customers first) | 5 | 3 matched + c3, c4 padded with NULL |
| RIGHT | 5 | 3 matched + o4, o5 padded with NULL |
| FULL OUTER | 7 | 3 matched + 2 left-only + 2 right-only |
FULL = LEFT + RIGHT − INNER, which is a fast sanity check when a query returns a count you did not expect.
Aggregation is where the choice stops being cosmetic. Order amounts are 100, 200, 50, 300, 400. After customers LEFT JOIN orders, AVG(o.amount) is still 350/3 = 116.67, because AVG skips NULLs: the padded rows do not move it. COUNT() is 5, not 3. So a metric written as SUM(amount)/COUNT() returns 70 instead of 116.67, a 40% understatement, with no error and no NULL anywhere in the output. COALESCE(o.amount, 0) does the same thing on purpose: it turns "no orders" into "zero revenue" and pulls the average down. Decide which of the two you mean before writing the expression, and use COUNT(o.order_id) rather than COUNT(*) whenever the denominator should be matched rows only.
LEFT JOIN also fans out. When the right side has many rows per key, every left row is duplicated, so SUM of a left-table column inflates: summing customer lifetime value after joining to orders counts each customer once per order. Aggregate the left table first or use a subquery.
The most common silent bug is a WHERE predicate on the right table:
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped'; -- c3 and c4 vanish: this is an INNER JOIN now
NULL = 'shipped' evaluates to unknown, not true, so unmatched rows are filtered out. Move the predicate into the ON clause to keep them. Filtering on the right table's key with IS NOT NULL is safe, it is an explicit inner join, but write INNER JOIN if that is what you mean.
To find rows with no match, use an anti-join: LEFT JOIN ... WHERE o.customer_id IS NULL, or NOT EXISTS, which is usually clearer and avoids the NOT IN NULL trap.
Detect all of this by comparing COUNT(*) before and after the join against the row counts of both inputs; if the joined count is not between them, you have fan-out or a dropped filter. And MySQL has no FULL OUTER JOIN: emulate it with LEFT JOIN UNION RIGHT JOIN.
Curated: · Written: · Reviewed: —
Q&A-101Explain the difference between a fact table and a dimension table, and how you would model a simple sales dataset as a star schema.
A fact table holds the events you measure. A dimension table holds the context you slice them by. Fact rows are narrow, long, numeric, and grow with every transaction; dimension rows are wide, short, mostly text, and change slowly. The fact carries foreign keys into each dimension plus the measures; the dimensions carry the descriptive attributes.
Before I draw anything, I state the grain of the fact table in one sentence: one row per order line per store per day. That sentence decides what every measure means. If I say "one row per order" but the source has multiple lines, COUNT(*) counts orders while SUM(quantity) is still line-level, and any join to a line-level table fans out.
A retail sales star:
fact_sales
date_key INT -> dim_date
product_key INT -> dim_product
store_key INT -> dim_store
customer_key INT -> dim_customer
order_id TEXT -- degenerate dimension, no dim table
quantity INT
unit_price DECIMAL
discount_amt DECIMAL
net_revenue DECIMAL
Dimensions around it:
| Table | Columns (abridged) |
|---|---|
| dim_date | date_key, date, month, quarter, year, is_weekend |
| dim_product | product_key, product_id, name, category, subcategory, brand |
| dim_store | store_key, store_id, city, region, format |
| dim_customer | customer_key, customer_id, signup_date, segment, country |
Why analysts care. Every join is one hop off the fact, so a query reads fact → dimension → filter, and the shape is the same for every question. Measures are additive across the dimensions they don't touch: SUM(net_revenue) by month, by region, by category all come from the same table. Conformed dimensions (one dim_date, one dim_product) let sales and returns be compared without reconciling two definitions of "category". Contrast a normalized OLTP schema where revenue is spread across orders, order_items and payments, and every analyst writes a different join and gets a slightly different number.
Failure modes. Fan-out: if a dimension has duplicate natural keys (two rows for the same product_id), SUM(net_revenue) inflates silently. Detect it with SELECT product_id, COUNT(*) FROM dim_product GROUP BY 1 HAVING COUNT(*) > 1. Second: putting a measure in a dimension, such as current_price on dim_product, makes historical revenue wrong the moment price changes.
The follow-up is usually slowly changing dimensions. If a product moves from "Outdoor" to "Sports", do past sales report under Outdoor or Sports? Type 1 overwrites the attribute: history is rewritten and last quarter's revenue moves to Sports. Type 2 adds a new row with validity dates and a current flag, and the fact keeps the surrogate key of the version that was live at sale time:
dim_product
product_key INT -- surrogate, unique per version
product_id TEXT -- natural key, repeats across versions
category TEXT
valid_from DATE
valid_to DATE
is_current BOOL
Sales before the move join to the old product_key and stay in Outdoor; sales after join to the new one. That gives "revenue by category as reported at the time", which is what finance usually wants. Type 3 (a previous_category column) holds only one prior value and is rarely worth the trouble. Type 2 costs you a predicate, AND is_current = TRUE when you want today's attributes, and forgetting it is the most common bug in these models: you join every version and double count.
Curated: · Written: · Reviewed: —
Q&A-1You join orders to order items and revenue doubles. What happened, and how do you fix it?
The first question I would ask about join fan-out inflating totals is what decision the answer will change.
A join returns one row per matching pair, so joining a one-row-per-order table to a many-rows-per-order table repeats every order-level value once per item. Any sum of an order-level column after that join is multiplied by the item count.
Concretely, check the grain of both tables before joining, aggregate the many side to the grain of the one side first, and compare row counts before and after the join so fan-out is caught rather than discovered.
The reason for that specificity is a failure I have seen: A revenue query joined orders to order lines and summed order_total, reporting 4.1 million against 1.7 million actually billed because the average order had 2.4 lines.
Row counts expose the fan-out.
| Step | Rows | SUM(order_total) |
|---|---|---|
| orders alone | 10,000 | 1.7m |
| joined to lines | 24,000 | 4.1m |
| lines pre-aggregated | 10,000 | 1.7m |
I would not consider it settled without evidence: Compare COUNT(*) and COUNT(DISTINCT order_id) after the join and reconcile the revenue total to the billing system.
Know the grain of every table before you join it.
Curated: · Written: · Reviewed:
Q&A-104A join returns 4 million rows where you expected 200,000. Walk me through how you diagnose the fan-out and fix it.
First 90 seconds: don't touch the join yet. Fan-out is a grain problem, so I check the grain of each input before I look at the join condition. Run COUNT(*) and COUNT(DISTINCT join_key) on both tables. If a table has more rows than distinct keys, that side is one-to-many on the key, and every row on the other side multiplies by it.
Then the arithmetic. Suppose orders has 200,000 rows and 200,000 distinct order_id: grain is one row per order. shipments has 260,000 rows and 200,000 distinct order_id: 1.3 parcels per order. The join should return 260,000 rows, not 200,000. That is legitimate fan-out, and it is the case people misread as a bug.
The bug is when the side you assumed unique isn't. customers with 200,000 rows but only 150,000 distinct customer_id (from SCD-2 history, a re-loaded batch, or a soft-delete flag) turns a 200,000-row order table into 260,000+ rows.
-- grain check, run on each input before joining
SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS keys FROM orders;
-- 200000 | 200000 unique -> one row per order
SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS keys FROM shipments;
-- 260000 | 200000 1.3 per order -> one row per parcel
Then predict the join size from the many side and compare:
SELECT SUM(n) AS expected_rows
FROM (SELECT COUNT(*) AS n FROM shipments GROUP BY order_id) t;
-- 260000
SELECT COUNT(*) AS actual_rows
FROM orders o JOIN shipments s USING (order_id);
-- 260000 -> matches; 1.4M would mean something else is wrong
If actual exceeds expected, the extra rows come from duplicate keys on the side you thought was unique. The worst case is duplicates on both sides: 3 rows for a key on the left and 4 on the right produce 12 rows for that key. A Cartesian product inside the key, not across the whole table.
The fix is to join at the grain you need. Aggregate the many side first:
WITH ship AS (
SELECT order_id,
COUNT(*) AS parcels,
SUM(shipping_cost) AS ship_cost
FROM shipments
GROUP BY order_id
)
SELECT o.order_id, o.order_total, ship.parcels, ship.ship_cost
FROM orders o
LEFT JOIN ship USING (order_id);
When you need one representative row rather than an aggregate (the current customer record, the latest status) dedupe with a window function and filter to rn = 1:
WITH c AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
FROM customers
)
SELECT o.order_id, c.segment
FROM orders o
JOIN c ON c.customer_id = o.customer_id AND c.rn = 1;
Why it matters and how it hides. Fan-out doesn't error; it inflates. SUM(order_total) after a fan-out counts each order once per parcel, so revenue is overstated by exactly the duplication factor. COUNT(*) looks plausible; COUNT(DISTINCT order_id) is what exposes it. So after any join I check the row count against the expected, and I check COUNT(DISTINCT grain_key) before and after. If the grain key count changed, the join changed the grain, and every downstream aggregate is suspect.
If asked to be quick: check COUNT(*) vs COUNT(DISTINCT key) on both inputs, predict the join size from the many side, compare, then aggregate or dedupe to the target grain before joining.
Curated: · Written: · Reviewed: —
Q&A-106You join a 200,000-row table to a 50,000-row table and get 4 million rows back. Walk me through what you check, in order, and how you fix it.
A join can only add rows by matching more than one row on the other side. The output count is the sum, over left rows, of how many right rows each one matches. 200,000 rows joined to 50,000 rows producing 4,000,000 means an average fan-out of 20 per left row: that is a many-to-many on the join key, not a mystery. My first move is to measure key uniqueness on each side, not to reread the query.
The checks, in order
- Grain on both sides. Run this on each input before joining:
SELECT COUNT(*) AS rows, COUNT(DISTINCT product_id) AS keys
FROM product_price_history;
-- 50,000 rows, 2,500 keys -> 20 rows per product
If rows > keys, that side fans out. Do it on the left too: a duplicate on the left multiplies just as hard.
-
The ON clause. Fan-out is often a missing predicate, not a duplicate row.
ON product_idwhen the right table is really keyed by(product_id, valid_from)matches every historical price. The fix is the predicate that got dropped:AND o.order_date BETWEEN p.valid_from AND p.valid_to. -
Wrong grain. Joining a daily table to a monthly table on
monthgives every day roughly 30 matches. Confirm both sides sit at the grain the join key implies. -
LEFT JOIN does not protect you. It guarantees at least one row per left row, not exactly one. If the right side has 20 rows per key, a LEFT JOIN returns 20 rows per left row and NULLs only where nothing matched at all.
The trace
| step | rows | distinct key | note |
|---|---|---|---|
| order_items | 200,000 | 200,000 | unique on order_item_id |
| product_price_history | 50,000 | 2,500 | 20 rows per product |
| join on product_id | 4,000,000 | — | 200,000 × 20 |
Fixes
Aggregate or de-duplicate the right side to the join grain first:
WITH latest_price AS (
SELECT product_id, price,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY valid_from DESC) AS rn
FROM product_price_history
)
SELECT ...
FROM order_items o
JOIN latest_price p
ON p.product_id = o.product_id AND p.rn = 1;
GROUP BY product_id with MAX(valid_from) does the same job when you only need the latest value. If the fan-out is legitimate, one order to many line items, keep it and aggregate after the join to the grain the report needs.
The trap that survives the row count
Even when the row count looks right, a one-to-many join double counts any measure from the "one" side. SUM(o.order_total) after joining orders to line items counts each order once per line. Sum measures from the side that owns them, or aggregate to order grain before joining. I check SUM before and after every join, not just COUNT(*).
In a pipeline I add an assertion after each join: the row count must equal the expected grain, and any join that changes the grain has to be deliberate and commented. The mirror failure, far fewer rows than expected, is unmatched keys, type or whitespace mismatches, or NULLs in the key, and it gets the same treatment from the other direction.
Curated: · Written: · Reviewed: —
Q&A-21A join between two tables matches far fewer rows than expected. What would you look for?
First 60–120 seconds. Before touching the join, I'd confirm the expectation: "far fewer rows" compared to what? A count from a differently filtered query, a dashboard number, and a row count on one side are three different denominators, and a wrong baseline is the most common cause of a "broken join." Then I'd walk the predicate outward: join type, then the ON clause, then the key values themselves.
Triage order, cheapest first:
- Join type and WHERE demotion. A
LEFT JOINwith a right-table predicate inWHERE(WHERE r.status = 'active') silently becomes an inner join and drops every unmatched left row. Move the predicate intoONand re-count. - NULLs in the key.
NULL = NULLis unknown, not true, so rows with a NULL key never match. Count NULLs on both sides. - Key representation. Type, collation, case, padding, whitespace, encoding.
CHAR(n)pads with spaces whereVARCHARdoesn't. A string column compared to an integer literal coerces, and in MySQL a non-numeric string coerces to 0, which changes the match set in both directions. - Genuine gaps. The keys may simply not exist on the right. An anti-join count tells you whether this is a data problem or a predicate problem.
- Fan-out confusion. Duplicate keys on the right multiply rows; if you're comparing against a deduplicated expectation, the join can look wrong when it isn't.
Measure it, don't eyeball it. This gives the match rate and the shape of the misses in one pass (PostgreSQL 16):
SELECT
count(*) AS left_rows,
count(r.id) AS matched,
round(100.0 * count(r.id) / count(*), 1) AS match_pct
FROM left_tbl l
LEFT JOIN right_tbl r ON r.id = l.id;
SELECT l.id, length(l.id) AS chars, octet_length(l.id) AS bytes
FROM left_tbl l
LEFT JOIN right_tbl r ON r.id = l.id
WHERE r.id IS NULL
LIMIT 20;
count(r.id) counts only matched rows, because the right side is NULL on a miss. The second query dumps the unmatched keys with their character and byte lengths: a byte count above the character count means multibyte characters, and a visible trailing space points at padding. For a targeted check, compare l.id = r.id against trim(l.id) = trim(r.id) and see whether the match count moves.
Signatures:
| Cause | Signature in the sample | Fix |
|---|---|---|
| NULL key | id is NULL on one side | fix the loader; decide the NULL semantics |
| Padding/whitespace | trailing space visible in the sample | TRIM upstream, or a persisted normalized column |
| Case/collation | same letters, different case | align collation, or lower() both sides |
| Type mismatch | one side numeric, one string | cast explicitly at the boundary |
| Genuine gap | key absent from the right table | referential-integrity fix, not a join fix |
Trade-off: normalize in the query or fix the data. ON trim(l.id) = trim(r.id) is a one-line fix that also disables the index on r.id. On a 50M-row table that's a full scan per join. Prefer fixing the loader, or adding a generated column with an index. Don't normalize when the mismatch is a real signal: two ID spaces that look similar but aren't should fail loudly, not be coerced into matching.
Illustrative: a CRM export wrote customer IDs with a trailing space; the join to billing matched 29,000 of 50,000 (58%) until a TRIM brought it to 49,500 (99%). The analyst had reported it as a churn spike.
Curated: · Written: · Reviewed:
Q&A-2Your LEFT JOIN is returning fewer customers than exist. Why?
The short answer: a predicate on the right-hand table's column is sitting in WHERE, not ON. WHERE is evaluated after the join, and NULL >= DATE '2026-01-01' is UNKNOWN, not TRUE, so every unmatched row the LEFT JOIN produced is discarded. The query is an inner join wearing a LEFT JOIN's clothes.
Mechanism
A LEFT JOIN emits one row per left row, padding the right side with NULLs when nothing matches. WHERE then filters that result set and keeps only rows where the predicate is TRUE. Three-valued logic does the rest: NULL >= x is UNKNOWN, WHERE drops UNKNOWN, so the padded rows die. Move the same predicate into ON and it becomes part of the match condition instead: it decides which orders count, not which customers survive.
Worked example (hypothetical figures)
customers has 122,000 rows. orders has 310,000 rows covering 84,000 distinct customers in 2026; 38,000 customers have no 2026 order.
-- A: filter in ON — keeps all customers
SELECT c.id, o.order_date
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= DATE '2026-01-01';
-- B: filter in WHERE — silently inner
SELECT c.id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= DATE '2026-01-01';
| Query | Rows returned | Customers with no 2026 order |
|---|---|---|
A (filter in ON) | 122,000* | 38,000 (31.1%) |
B (filter in WHERE) | 84,000 | 0 (0.0%) |
*Only if each customer has at most one 2026 order. With multiple matches per customer, A returns more rows than the left table: that is row multiplication, not a bug.
Detecting it
Do not compare COUNT(*) to the left table's row count; the right side can legitimately multiply rows. Compare distinct left keys:
SELECT COUNT(DISTINCT c.id) FROM customers c; -- 122,000
SELECT COUNT(DISTINCT c.id)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= DATE '2026-01-01'; -- 84,000 -> the filter is in the wrong place
For the share of inactive customers, COUNT(o.id) counts non-NULLs only, so 1 - COUNT(o.id) / COUNT(DISTINCT c.id) is the honest denominator. COUNT(*) there would report 0% inactive.
Other ways the left side shrinks
- A LEFT JOIN followed by an INNER JOIN to a third table on a right-side column: the inner join re-imposes the match requirement.
HAVINGon a right-table column, orGROUP BYon it, collapsing padded rows.- A join key that never matches because of type coercion or trailing whitespace: the rows are padded, then filtered.
WHERE o.id IS NULLused as an anti-join is correct, but only if nothing else inWHEREtouches the right table.
What to reach for instead
If the intent is "customers with no 2026 orders", say so directly with NOT EXISTS: it cannot be broken by a later WHERE edit, and it does not multiply rows:
SELECT c.id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.order_date >= DATE '2026-01-01'
);
If the intent is "customers plus their 2026 order totals", pre-aggregate the right side in a subquery and LEFT JOIN the aggregate, so the filter lives inside the subquery where it belongs.
The rule to carry into the interview: a LEFT JOIN stays left only while every predicate on the optional table lives in ON.
Curated: · Written: · Reviewed:
Q&A-83After a pandas merge your DataFrame has more rows than either input. What happened?
A many-to-many merge. When the join key appears more than once on both sides, pandas emits the full Cartesian product of the matching rows for each key value. Three left rows and four right rows sharing a key become twelve output rows, not three or four.
That is the whole mechanism. merge is a relational join, not a lookup. If you expected one row per left row, you assumed the right side was unique on the key, and it wasn't.
Output rows per key value:
| Left rows per key | Right rows per key | Output rows per key |
|---|---|---|
| 1 | 4 | 4 |
| 3 | 4 | 12 |
| 3 | 1 | 3 |
The result's row count is the sum over keys of (left count × right count), plus unmatched rows if you used an outer join.
How to catch it before it corrupts a number:
import pandas as pd
# Check uniqueness first
left.duplicated(subset="user_id").any() # True means many rows per key
right.duplicated(subset="user_id").any()
# Let pandas enforce the relationship you believe holds
merged = left.merge(right, on="user_id", validate="many_to_one")
# raises MergeError if right isn't unique on user_id
# Or assert the count you expect
assert len(merged) == len(left)
validate accepts "one_to_one", "one_to_many", "many_to_one", "many_to_many". Pick the one that matches your mental model; pandas raises MergeError when the data violates it. This is the cheapest guard against a silent explosion.
A real case: a notebook joined a sessions table to a purchases table on user_id. Both sides had many rows per user: a user with 5 sessions and 4 purchases produced 20 rows. 2 million sessions became 37 million rows, and "spend per session" came out 18× too high because each purchase was counted once per session. The number looked plausible; only the row count gave it away.
The fix depends on intent:
- If you want one row per left row, deduplicate the right side first (
right.drop_duplicates(subset="user_id")) or aggregate it (right.groupby("user_id").agg(...)). - If you genuinely want the cross product, keep it but aggregate before reporting any per-row metric.
- If you want one row per purchase, merge the other direction.
After any merge, compare len(merged) against what you expect. If you can't state the expected count, you don't yet know the relationship between the two tables.
Curated: · Written: · Reviewed:
Q&A-13Why does a NOT IN subquery sometimes return no rows at all?
NOT IN returns zero rows whenever the subquery produces even one NULL. A comparison against NULL is UNKNOWN, not FALSE, and a row survives the predicate only if every comparison is TRUE, so a single NULL empties the result set no matter how many rows should have matched.
Why it behaves that way. x NOT IN (SELECT customer_id FROM refunds) is x <> ALL (...), which the planner evaluates as x <> r1 AND x <> r2 AND ... AND x <> rn. If any r is NULL, that conjunct is UNKNOWN. In three-valued logic TRUE AND UNKNOWN is UNKNOWN, and WHERE keeps only TRUE. The same trap fires on the outer side: NULL NOT IN (1, 2, 3) is UNKNOWN too. An empty subquery is the opposite case (it returns every row, not none) so it is not the cause here.
What to do instead.
NOT EXISTS (SELECT 1 FROM refunds r WHERE r.customer_id = c.id): a NULL in the subquery column never satisfies the equality, so the outer row is kept. This is the usual fix and the one to reach for by default.- Filter the subquery:
... WHERE customer_id IS NOT NULL. Correct only if you have decided that NULL customer_ids should count as "not in the set". LEFT JOIN refunds r ON r.customer_id = c.id WHERE r.customer_id IS NULL: same semantics asNOT EXISTS, and on large tables it sometimes gets a better plan.- A
NOT NULLconstraint on the column removes the hazard permanently; if the column is nullable, the hazard is live.
| Subquery values | Predicate | Rows returned |
|---|---|---|
| 1, 2, 3 | NOT IN | 4,997 |
| 1, 2, 3, NULL | NOT IN | 0 |
| 1, 2, 3, NULL | NOT EXISTS | 4,997 |
Failure mode. A query for customers who never received a refund returned 0 rows because one refund record had a NULL customer_id. The retention team read the empty result as "every customer has been refunded" and nearly shipped a win-back campaign to the whole base. The tell is cheap to check: count NULLs in the subquery column, then run the NOT EXISTS version of the same query side by side. If the counts differ, the NULL is the reason. A small literal list like NOT IN (1, 2, 3) is safe; the danger is a subquery over a nullable column.
Curated: · Written: · Reviewed:
Q&A-20When would UNION give you a wrong answer that UNION ALL would not?
The 90-second answer
UNION gives a wrong answer whenever two rows that are genuinely distinct events happen to be identical across every column you selected. UNION is UNION ALL followed by DISTINCT over the whole row, so it deletes those rows, and every downstream aggregate (SUM, COUNT, AVG) is then computed over a smaller set. Two $10 card payments from the same customer on the same day are two rows of revenue; UNION keeps one.
Default to UNION ALL and deduplicate explicitly on a key you can name, so the definition of "duplicate" lives in the query instead of being implied by the column list.
A worked trace
-- PostgreSQL 16
CREATE TABLE card_payments (customer_id int, paid_on date, amount numeric(10,2));
CREATE TABLE wallet_payments (customer_id int, paid_on date, amount numeric(10,2));
INSERT INTO card_payments VALUES (7, '2024-03-01', 10.00),
(7, '2024-03-01', 10.00);
INSERT INTO wallet_payments VALUES (7, '2024-03-01', 10.00);
SELECT count(*), sum(amount) FROM (
SELECT * FROM card_payments UNION ALL SELECT * FROM wallet_payments) t;
-- 3 | 30.00
SELECT count(*), sum(amount) FROM (
SELECT * FROM card_payments UNION SELECT * FROM wallet_payments) t;
-- 1 | 10.00
Three identical rows go in; UNION ALL returns three and sums to 30.00, UNION collapses them to one and reports 10.00. Nothing errored. The report is just wrong.
Dedup is broader than your equality
UNION compares rows with set semantics, not with =. Three consequences bite in production:
| Expression | UNION rows | UNION ALL rows |
|---|---|---|
SELECT NULL::int, 1 UNION SELECT NULL::int, 1 | 1 | 2 |
MySQL 8.0 (default utf8mb4_0900_ai_ci): SELECT 'Alice' UNION SELECT 'alice' | 1 | 2 |
PostgreSQL 16: SELECT 'Alice' UNION SELECT 'alice' | 2 | 2 |
SELECT 1 UNION SELECT 1.0 | 1 | 2 |
NULLs are treated as equal to each other for set operations, so rows that a IS DISTINCT FROM b would call different collapse. A case-insensitive collation makes 'Alice' and 'alice' the same row. Type resolution between the branches can coerce values into equality: 1 and 1.0 resolve to one numeric type and dedupe. Any of these can silently drop rows you meant to keep, and none of them is visible from reading the column list.
Failure modes
- Masked fan-out. A join that multiplies rows gets quietly repaired by UNION, so the bug ships and reappears the moment someone switches to UNION ALL. Detect it by running the same query both ways and diffing counts.
- Blocking plan. UNION needs a sort or hash aggregate over the full combined input and can spill to disk; UNION ALL streams. On a large stack that is the difference between an append and a blocking operator.
- Order assumptions. UNION's internal sort is an implementation detail, not an ORDER BY. Neither form guarantees row order.
How to check what UNION removed
SELECT customer_id, paid_on, amount, count(*)
FROM (SELECT * FROM card_payments UNION ALL SELECT * FROM wallet_payments) t
GROUP BY 1, 2, 3
HAVING count(*) > 1;
Every row this returns is a row UNION would have deleted. If they are real events, UNION was the wrong operator.
When UNION is right
When the two branches are the same set queried twice and you want set semantics: combining two overlapping id lists, for instance. If you need to dedupe on a key but keep a chosen row, use DISTINCT ON (key) in PostgreSQL or ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1, not UNION.
Curated: · Written: · Reviewed:
Q&A-3What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?
The 60-second answer. All three are aggregates over the same rows; they differ in what they count and what they do with NULL.
COUNT(*)counts rows. NULLs and duplicates included. It never reads a column value.COUNT(col)counts rows wherecol IS NOT NULL. Duplicates included.COUNT(DISTINCT col)counts distinct non-NULL values ofcol. NULL is not a value here, so it is excluded, not counted as one distinct value.
COUNT(1) behaves the same as COUNT(*) in every engine I've used; the constant is never NULL.
Trace on six rows:
| id | |
|---|---|
| 1 | a@x.com |
| 2 | a@x.com |
| 3 | NULL |
| 4 | b@x.com |
| 5 | NULL |
| 6 | c@x.com |
COUNT(*)= 6COUNT(email)= 4 (rows 3 and 5 dropped)COUNT(DISTINCT email)= 3 (a@x.com, b@x.com, c@x.com)
Cost. This is where the choice bites at scale.
COUNT(*) is the cheapest. InnoDB scans the smallest available secondary index rather than the clustered index; Postgres can do an index-only scan on any index. No per-row value work.
COUNT(col) needs col readable. With an index on col it is an index-only scan; without one the engine reads each row to test for NULL, so it is a full table scan.
COUNT(DISTINCT col) must dedupe. The engine either sorts the non-NULL values or builds a hash table keyed by them, so memory grows with the number of distinct values, not the row count. On a high-cardinality column (user_id, request_id) that hash or sort spills to disk and the query goes from seconds to minutes. EXPLAIN ANALYZE shows it as HashAggregate/GroupAggregate with a large memory figure, or MySQL's Using temporary; Using filesort. If you need distinct counts on a huge table, precompute them in a rollup table, or use a HyperLogLog sketch when an approximate count is acceptable.
Failure modes.
- Denominator mismatch. A conversion rate using
COUNT(email)as its denominator counts only sessions with an email. If 12% of sessions are guests with no email, the rate is inflated: 1,550 conversions over 44,000 emails reads 3.5%, over 50,000 sessions it reads 3.1%. (Illustrative figures.) The bug is silent because the number is plausible. COUNT(DISTINCT)treated as "distinct including NULL". It isn't. To include the NULL group, useCOUNT(DISTINCT col) + CASE WHEN COUNT(*) > COUNT(col) THEN 1 ELSE 0 END.- Multi-column distinct:
COUNT(DISTINCT a, b)counts distinct non-NULL pairs, not distinctaplus distinctb.
How to pick. Name the entity first (sessions, users, events) then choose the form that counts exactly that. Check the NULL rate of the column so the gap between COUNT(*) and COUNT(col) is expected rather than a surprise; if the gap is large and you did not know it, that is the finding.
Curated: · Written: · Reviewed:
Q&A-4When do you filter in WHERE and when in HAVING?
The 60-second answer
Assume standard SQL on a mainstream engine (PostgreSQL 16, MySQL 8). WHERE filters rows before grouping; HAVING filters groups after aggregation. So a predicate on a raw column goes in WHERE, and a predicate on an aggregate (COUNT, SUM, AVG, MAX) can only go in HAVING. When a predicate is legal in both places, put it in WHERE: it removes rows before the aggregation has to do any work.
Why it splits that way
Logical order of evaluation: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. WHERE sees individual rows. By the time HAVING runs, rows have collapsed into groups, and the only things it can reference are grouping keys and aggregates.
SELECT status, COUNT(*) AS n
FROM orders
WHERE created_at >= DATE '2024-01-01' -- row attribute: before grouping
GROUP BY status
HAVING COUNT(*) > 1000; -- aggregate: after grouping
created_at exists per row, so WHERE can use it. COUNT(*) does not exist until GROUP BY has run, so only HAVING can use it.
The failure mode
Putting a row predicate in HAVING forces the engine to build every group first and throw most of them away afterwards. Illustrative numbers from a 9M-row orders table:
| Placement | Rows entering aggregation | Runtime |
|---|---|---|
HAVING status = 'refunded' | 9,000,000 | ~3 min |
WHERE status = 'refunded' | 210,000 | ~4 s |
Same result set. The cost is the hash or sort build over 9M rows instead of 210k.
That gap is not guaranteed, though. Some optimizers rewrite a HAVING predicate that references only grouping columns into a pre-aggregation filter, and the difference disappears. Verify with EXPLAIN (ANALYZE, BUFFERS) and check whether the filter sits above or below the Aggregate node.
When HAVING is the only option
- Aggregate predicates:
HAVING COUNT(*) > 1000,HAVING SUM(amount) > 10000. - A SELECT alias, in MySQL: MySQL allows aliases in HAVING and GROUP BY. PostgreSQL allows output column names in GROUP BY and ORDER BY but not in WHERE or HAVING.
- Window functions: neither WHERE nor HAVING can reference them. Wrap the query in a subquery, or use
QUALIFYon engines that have it (Snowflake, BigQuery, DuckDB).
One more trap: HAVING on a column that is neither a grouping key nor aggregated is rejected under standard SQL and under MySQL's ONLY_FULL_GROUP_BY. With that mode off, MySQL silently picks an arbitrary row's value from each group.
Default to WHERE, reach for HAVING only when the predicate needs the aggregate, then confirm in the plan that the row filter runs below the aggregation.
Curated: · Written: · Reviewed:
Q&A-22How do NULL values affect AVG and SUM, and when does that mislead?
Core answer
SUM and AVG both ignore NULLs. SUM ignores them in the numerator; AVG ignores them in the numerator and the denominator, because AVG(x) is defined as SUM(x) / COUNT(x) and COUNT(x) counts only non-NULL values. So SUM is almost always unchanged by NULLs, and AVG is the one that moves.
That means AVG silently answers a different question depending on how many rows are NULL. AVG(amount) over an orders table is average order value. AVG(COALESCE(amount, 0)) over users left-joined to orders is average revenue per user. Both are correct; they are not the same number, and the gap is exactly the NULL fraction.
Worked example
10,000 users, 3,000 orders, $255,000 total spend.
-- average order value: denominator is orders
SELECT AVG(amount) FROM orders;
-- 255000 / 3000 = 85.00
-- average revenue per user: denominator is users
SELECT AVG(COALESCE(o.amount, 0))
FROM users u LEFT JOIN orders o ON o.user_id = u.id;
-- 255000 / 10000 = 25.50
| Query | Rows in denominator | Result |
|---|---|---|
AVG(amount) on orders | 3,000 | $85.00 |
AVG(COALESCE(amount,0)) on users ⟕ orders | 10,000 | $25.50 |
SUM(amount) either way | — | $255,000 |
SUM is identical in both because adding zeros changes nothing. Only the denominator moved. A dashboard that labels the first number "average spend per user" is off by 3.3×.
Where it bites
- All-NULL group.
AVGandSUMboth return NULL, not 0.SUM(COALESCE(x,0))returns 0. A report that renders NULL as blank looks like a bug; one that renders it as 0 hides "no data" as "zero". WHEREfilters drop NULLs too.WHERE amount <> 0excludes NULL rows, shrinking the denominator without saying so.WHERE amount IS NULL OR amount <> 0keeps them.GROUP BYon a nullable key. All NULLs collapse into one group, so "unknown region" becomes a bucket that looks like a real region.- Window functions.
AVG(x) OVER (PARTITION BY user_id)skips NULLs inside the frame; a user whose only rows are NULL gets NULL, not 0.
What to do
Decide per column whether NULL means zero, unknown, or not-applicable. Zero → COALESCE and say so. Unknown → leave it NULL and report the denominator. Then put COUNT(*) and COUNT(col) in the same query as the aggregate; if they differ, the denominator is the non-NULL count, and that is the number to show next to the average.
Curated: · Written: · Reviewed:
Q&A-16How would you report typical order value, and how do you compute it in SQL?
What I'd say first
"Typical" is doing two jobs here. If the ask is the business metric, average order value, that's revenue ÷ orders, a mean, and it's the number finance reconciles against. If the ask is what a typical customer actually spends, that's the median, because order values are right-skewed and the mean sits above most of the distribution. I'd report both plus the quartiles, and say which one the decision is being made on.
Computing it
Postgres 16:
SELECT
count(*) AS orders,
avg(value) AS mean,
percentile_cont(0.5) WITHIN GROUP (ORDER BY value) AS median,
percentile_cont(0.25) WITHIN GROUP (ORDER BY value) AS p25,
percentile_cont(0.75) WITHIN GROUP (ORDER BY value) AS p75
FROM orders
WHERE status = 'completed'
AND placed_at >= now() - interval '90 days';
percentile_cont is an ordered-set aggregate in Postgres: no OVER clause. SQL Server's PERCENTILE_CONT is a window function and requires one, so the same query needs OVER () and SELECT DISTINCT:
SELECT DISTINCT
AVG(value) OVER () AS mean,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) OVER () AS median
FROM orders
WHERE status = 'completed';
Trace
Ten completed orders, sorted: 12, 20, 28, 40, 58, 70, 100, 124, 180, 788.
percentile_cont interpolates: position = fraction × (n − 1) over the sorted array.
- p25: 0.25 × 9 = 2.25 → between the 3rd (28) and 4th (40) → 28 + 0.25×12 = 31
- median: 0.5 × 9 = 4.5 → between 58 and 70 → 64
- p75: 0.75 × 9 = 6.75 → between 100 and 124 → 100 + 0.75×24 = 118
- mean: 1420 / 10 = 142
| Statistic | Order value |
|---|---|
| mean | $142 |
| p75 | $118 |
| median | $64 |
| p25 | $31 |
percentile_disc returns an actual row instead of interpolating: p25 = 28, median = 58, p75 = 124. On ten rows the two functions disagree by $6 on the median; on a thousand rows they converge. Say which one you used.
Failure modes
- The base population. Refunds, cancellations, test orders and mixed currencies all move the median. Filter to
status = 'completed'and one currency, and state the window: 90 days of Q4 is not 90 days of Q1. - NULLs. Both functions skip NULLs, so a NULL
valueon refunded rows silently changes the denominator. Checkcount(value)againstcount(*). - The mean is decomposable, the median is not. You can roll daily means into a monthly mean; you cannot roll daily medians. If you need a cheap median at scale, keep a per-day histogram or the raw values, not a pre-averaged table.
- Cost. Ordered-set aggregates sort every filtered row. On a large fact table that's a real sort; a daily rollup table keeps the dashboard query cheap.
The failure I'd cite
A pricing proposal used mean order value of $142 as "typical" and set a free-shipping threshold there. On the distribution above, 8 of 10 orders sit below $142: the threshold landed above the 75th percentile, so it changed behaviour for almost nobody while the model assumed it changed it for everybody. The median, $64, was the number that would have caught it.
When not to use the median
If the decision is about total revenue (inventory, capacity, cash) the mean is the right number, because revenue is a sum and the mean is what sums. The median answers "what does a typical order look like", not "what will we collect". Report both and let the decision pick.
Curated: · Written: · Reviewed:
Q&A-25When would you use a window function instead of GROUP BY?
Use GROUP BY when the output grain is the group. Use a window function when the output grain is the row but the value comes from the group: a share of customer spend, a running total, a rank, the previous row's value, or a dedupe pick.
That is the whole decision. The rest is why it bites.
Why: evaluation order. SQL evaluates FROM → WHERE → GROUP BY → HAVING → window functions → SELECT/DISTINCT → ORDER BY → LIMIT. GROUP BY has already destroyed the row detail by the time SELECT runs, so you cannot get it back without a join. Window functions run after grouping but before DISTINCT and ORDER BY, which is where two common bugs come from: you cannot filter on ROW_NUMBER() in WHERE, and SELECT DISTINCT does not dedupe rows a window function numbered.
The shape. PostgreSQL 16:
SELECT o.order_id, o.customer_id, o.amount,
SUM(o.amount) OVER (PARTITION BY o.customer_id) AS customer_total,
ROUND(100.0 * o.amount
/ SUM(o.amount) OVER (PARTITION BY o.customer_id), 1) AS pct
FROM orders o;
| order_id | customer_id | amount | customer_total | pct |
|---|---|---|---|---|
| 101 | A | 40 | 100 | 40.0 |
| 102 | A | 60 | 100 | 60.0 |
| 103 | B | 25 | 25 | 100.0 |
Three rows in, three rows out. The GROUP BY version of the same data returns two rows and no order_id: the detail is gone.
Trade-offs. A window function keeps every row alive through a sort (or hash) of the partition; GROUP BY can hash-aggregate and emit one row per group. On a 50M-row fact table grouped to 2M customers, GROUP BY emits 2M rows; the window version emits 50M rows and sorts them. So if you only need the group totals, GROUP BY is cheaper and the result is smaller to ship. Reach for the window when you need the detail anyway: it replaces a self-join back to the aggregate, which is usually where the cost was. Each distinct PARTITION BY / ORDER BY combination is its own sort; three different window specs in one query means three sorts.
Failure modes.
- Joining detail to a group total without the group key cross-joins. 2M orders against 2,300 customer totals is 4.6B rows; the query is killed before it returns. The window version never leaves 2M rows.
SUM(x) OVER (ORDER BY day)is a running total, not a partition total: with ORDER BY the default frame isRANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. UseOVER ()for the whole-partition total.ROW_NUMBER() OVER (...) = 1in WHERE fails to parse; wrap it in a subquery or CTE.- NULL partition keys all land in one partition, so a missing
customer_idsilently merges unrelated rows. - RANK leaves gaps on ties, DENSE_RANK does not, ROW_NUMBER is arbitrary among ties. Pick deliberately.
Verification. Row count out equals row count in for the window version; shares within each partition sum to 100; EXPLAIN shows WindowAgg where you expect it and HashAggregate for the GROUP BY. If the plan shows a Sort feeding the WindowAgg on a large table, check whether an index covers the partition and order keys.
Curated: · Written: · Reviewed:
Q&A-5How do ROW_NUMBER, RANK, and DENSE_RANK differ when there are ties?
All three are window functions that number rows in an ORDER BY order. They differ only in what the counter does after a tie.
- ROW_NUMBER: a pure counter, 1..N in sort order. Tied rows get different numbers, assigned arbitrarily.
- RANK: tied rows share a rank; the next distinct value jumps by the size of the tie group (rank = 1 + rows strictly ahead).
- DENSE_RANK: tied rows share a rank; the next distinct value is the previous rank + 1.
SELECT rep, deals,
ROW_NUMBER() OVER (ORDER BY deals DESC) AS rn,
RANK() OVER (ORDER BY deals DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY deals DESC) AS drnk
FROM sales;
| Rep | Deals | rn | rnk | drnk |
|---|---|---|---|---|
| A | 150 | 1 | 1 | 1 |
| B | 142 | 2 | 2 | 2 |
| C | 142 | 3 | 2 | 2 |
| D | 130 | 4 | 4 | 3 |
Trace: the sort descends to A(150), then B and C tied at 142, then D(130). rn is the position in that order, so B and C get 2 and 3 in whichever order the plan emits them. rnk: A has 0 rows ahead → 1; B and C each have 1 row ahead → 2; D has 3 rows ahead → 4. drnk counts distinct values: 150 → 1, 142 → 2, 130 → 3.
The divergence that bites is at the cutoff. WHERE rn <= 3 and WHERE rnk <= 3 both return three rows here, but WHERE drnk <= 3 returns four. Make the tie group larger, three reps tied at 142, and the next row gets RANK 5 but DENSE_RANK 3, so rnk <= 3 returns four rows and drnk <= 3 returns five. Since DENSE_RANK values are never larger than RANK values, drnk <= k is always a superset of rnk <= k. Neither is wrong; they answer different questions: "how many are strictly better than me" versus "how many distinct tiers are above me".
Failure mode: ROW_NUMBER over a non-unique key is not stable. A top-3 bonus query ordered only by deals, with two reps tied at 142, returned a different third rep after a statistics update flipped the plan from an index scan to a sequential scan. The query is valid; the result is plan-dependent. Detect it by checking the ordering key for duplicates, SELECT deals FROM sales GROUP BY deals HAVING COUNT(*) > 1, and if any exist, either add a deterministic tiebreaker (ORDER BY deals DESC, rep ASC) or switch to RANK/DENSE_RANK when the business rule is "everyone tied at the cutoff qualifies".
RANK and DENSE_RANK are deterministic on the ordering key alone; their failure mode is the opposite one: a tie straddling the cutoff silently widens the result set, which is a business decision rather than a bug.
Practical notes: all three require the ORDER BY sort (an index on the sort key can supply it), none accept a frame clause, and NULL placement follows the engine default (PostgreSQL and Oracle sort NULLs last for ASC, SQL Server treats them as lowest) so a NULL in the ranking key lands in a tie group you may not have intended.
Curated: · Written: · Reviewed:
Q&A-6How would you compute a running total of revenue by day, and what can go wrong?
The honest answer on running totals with a window frame begins with what the data cannot tell us.
SUM() OVER (ORDER BY day) produces a running total, but the default frame with ORDER BY is RANGE, which treats rows with the same order value as peers and adds them all at once. Duplicate dates therefore jump instead of accumulating row by row.
Concretely, aggregate to one row per day before applying the window, or state ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly, and fill missing days so the running total has no gaps.
The reason for that specificity is a failure I have seen: A cumulative revenue chart was computed on raw transactions, and because 3 transactions shared a timestamp the curve showed the same 18,400 total on three rows, which a reviewer read as a data freeze.
One row per day, then accumulate.
| Day | Revenue | Running total |
|---|---|---|
| Sep 1 | 5,000 | 5,000 |
| Sep 2 | 7,200 | 12,200 |
| Sep 3 | 6,200 | 18,400 |
| total check | 18,400 | 18,400 |
I would not consider it settled without evidence: Check that the last running total equals the plain SUM over the whole period and that there is exactly one row per day.
Pin the grain and the frame before trusting a cumulative line.
Curated: · Written: · Reviewed:
Q&A-8A table has several rows per customer. How do you keep only the most recent one?
Assume three things before writing the query: you want the whole row and not just the timestamp, updated_at can tie between two rows for the same customer, and "most recent" means the largest updated_at. Then the answer is a window function filtered to rank 1.
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id
ORDER BY updated_at DESC, id DESC) AS rn
FROM customer_events
) t
WHERE rn = 1;
The id DESC tiebreaker is not decoration. Two rows can share an updated_at: a batch load writes both in one transaction, or the column has second granularity. Without a tiebreaker the engine picks arbitrarily, and the row you get can change between runs or between plans. With it, the result is deterministic.
Why not GROUP BY: GROUP BY customer_id, MAX(updated_at) gives the latest timestamp but not the other columns of that row. Joining back on (customer_id, updated_at) returns both rows when the timestamps tie. And aggregating each column separately, MAX(email), MAX(plan), is worse: it builds a row from values that never coexisted. I have seen that ship. A dedup that took MAX per column produced customer rows whose email came from one update and whose plan came from a later one, a combination that never existed, for 2,300 customers.
Alternatives, and when each wins:
| Approach | Dialect | What it costs | Use when |
|---|---|---|---|
ROW_NUMBER() | Postgres 8.4+, MySQL 8.0+, SQL Server 2005+ | sorts every row in the table | default; you need the whole row and ties are possible |
DISTINCT ON (customer_id) | Postgres | index scan if an index matches the ORDER BY | Postgres, and you want the shortest query |
NOT EXISTS self-join | any | one index lookup per row | few customers, many rows each, index on (customer_id, updated_at DESC) |
DELETE ... USING a ranked CTE | any with CTEs | sort, then delete | you want the table deduped in place, not just queried |
DISTINCT ON is the shortest in Postgres:
SELECT DISTINCT ON (customer_id) *
FROM customer_events
ORDER BY customer_id, updated_at DESC, id DESC;
The ORDER BY must lead with the DISTINCT ON expression, or Postgres errors.
ROW_NUMBER is the wrong tool when the table is large and you only need a handful of customers. It sorts the entire table before the filter, so on 500M rows it spills to disk even if you want 100 customers. A correlated NOT EXISTS with an index on (customer_id, updated_at DESC) does one index lookup per row and stops at the first match: no global sort. If you genuinely need every customer deduped, you are sorting either way, and ROW_NUMBER is fine.
Verify with two checks, not one. Assert the output has exactly one row per customer_id:
SELECT customer_id, COUNT(*)
FROM deduped
GROUP BY 1
HAVING COUNT(*) > 1;
Then confirm each output row exists whole in the source: join back on the primary key, not on (customer_id, updated_at), which ties.
Curated: · Written: · Reviewed:
Q&A-9Find the second-highest salary in each department. How do you handle ties and departments with one employee?
Clarify, then answer. Two things decide this: does "second-highest" mean the second distinct salary value or the second person by salary, and must a department with one employee appear in the result? I'd state my assumption out loud: second distinct value, every department present, NULL where there is no second value, and name the ROW_NUMBER variant if they meant second person.
WITH ranked AS (
SELECT dept_id, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
WHERE salary IS NOT NULL
)
SELECT d.dept_id, r.salary AS second_salary
FROM departments d
LEFT JOIN ranked r
ON r.dept_id = d.dept_id AND r.rnk = 2;
DENSE_RANK gives equal salaries the same rank and leaves no gaps, so a tie at the top still leaves the next distinct value sitting at rank 2. ROW_NUMBER would number the tied pair 1 and 2 and hand back the top salary as the "second" one. The LEFT JOIN is what keeps single-employee and empty departments in the output.
Trace on four departments: Sales(1): 90k, 90k, 70k; Ops(2): 60k; Tech(3): 150k, 120k, 120k; Legal(4): no employees.
| dept | salary | DENSE_RANK | ROW_NUMBER |
|---|---|---|---|
| Sales | 90000 | 1 | 1 |
| Sales | 90000 | 1 | 2 |
| Sales | 70000 | 2 | 3 |
| Ops | 60000 | 1 | 1 |
| Tech | 150000 | 1 | 1 |
| Tech | 120000 | 2 | 2 |
| Tech | 120000 | 2 | 3 |
Filter to rnk = 2 and left join back: Sales → 70000, Ops → NULL, Tech → 120000, Legal → NULL. Four rows. ROW_NUMBER would give Sales → 90000, which is the second person, not the second value.
Alternatives. A correlated subquery gets the same semantics: (SELECT MAX(salary) FROM employees e WHERE e.dept_id = d.dept_id AND e.salary < (SELECT MAX(salary) FROM employees e2 WHERE e2.dept_id = d.dept_id)). It scans twice per department and does not extend to the Nth value, so it is fine for a one-off and wrong for a report. ORDER BY salary DESC LIMIT 1 OFFSET 1 with no PARTITION BY returns one row for the whole company rather than one per department: the classic wrong answer.
Failure modes. NULL salaries: in Postgres, ORDER BY salary DESC defaults to NULLS FIRST, so a NULL takes rank 1 and pushes the real top earner to rank 2. Filter WHERE salary IS NOT NULL or write ORDER BY salary DESC NULLS LAST. MySQL sorts NULLs last on DESC, so this bug is engine-specific: check yours. An empty department survives only because of the LEFT JOIN; an INNER JOIN silently drops it. If the question wants only departments that have a second salary, switch to INNER JOIN, but say which you are doing. On performance, the window function sorts each partition; an index on (dept_id, salary DESC) lets the engine read partitions in order, so this stays a single pass rather than a query per department.
I would not call it settled without testing a department with a tie at the top and a department with one employee, and confirming both give the answers above.
Curated: · Written: · Reviewed:
Q&A-15How would you find users who logged in on at least 5 consecutive days?
What I'd pin down first. Two things decide the query: what a "day" is (calendar day in which timezone. A login at 23:50 local and one at 00:10 local are different days), and whether "at least 5 consecutive days" means any streak of length ≥ 5 anywhere in history, or a streak still alive today. Assume: any streak of ≥ 5 days, day boundary is UTC, one row per user per calendar day.
The technique. Gaps-and-islands. Deduplicate to one row per user per day, then subtract a per-user row number from the date. Inside a run of consecutive dates both the date and the row number advance by one, so the difference is constant; at a gap the date jumps but the row number doesn't, so the difference changes. Group by (user, difference) and each group is one streak.
-- PostgreSQL 16
WITH daily AS (
SELECT DISTINCT user_id, login_date
FROM logins
),
anchored AS (
SELECT user_id, login_date,
login_date - (ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY login_date))::int AS anchor
FROM daily
)
SELECT user_id, anchor, COUNT(*) AS streak_len,
MIN(login_date) AS streak_start, MAX(login_date) AS streak_end
FROM anchored
GROUP BY user_id, anchor
HAVING COUNT(*) >= 5;
Trace on a fixture:
| login_date | row_number | login_date - rn | group |
|---|---|---|---|
| Sep 1 | 1 | Aug 31 | A |
| Sep 2 | 2 | Aug 31 | A |
| Sep 3 | 3 | Aug 31 | A |
| Sep 5 | 4 | Sep 1 | B |
Group A has 3 rows, group B has 1; neither passes HAVING COUNT(*) >= 5. Add Sep 4 and group A has 5 rows and passes.
Alternatives.
- LAG self-comparison. Take
LAG(login_date)per user, flag a break whenlogin_date - prev > 1, running-sum the breaks to get a streak id. Same complexity, but it's the shape you want when the question is "is this user's streak alive today": you can walk backwards and stop at the first break. - Precomputed streaks table.
user_streaks(user_id, current_streak, longest_streak, last_login_date), updated on each login. O(1) write, O(1) read, and it answers "who just hit 5 days" without a scan. The cost: it's only correct if the write path is the sole writer. Backfills, replays and late-arriving events silently corrupt it, so you need a reconciliation job. - Serving layer. Pull the last 5 days of logins for one user and check in application code. Fine for a profile page, useless for "find all users".
When gaps-and-islands is the wrong tool. It's a batch recompute over the whole table: the window sort dominates, and on a billion-row login table a dashboard refreshing hourly will not survive it. If the question is "who is on a 5-day streak right now", filter to the last 5 days before the window function: that alone cuts the scan by orders of magnitude. If you need it continuously, a stateful streaks table or a streaming job is the right shape.
Failure modes. Skipping the dedup is the classic one: two logins on one day push the row number ahead of the date, the anchor drifts, and users get credited with streaks they never had. Timezone is the second: computing the day in UTC when the product means local days splits streaks at the wrong boundary. Both are caught by a fixture with two logins on one day and a one-day gap, asserting exact streak lengths.
Curated: · Written: · Reviewed:
Q&A-17How do you turn one row per month per product into one row per product with a column per month?
The 90-second answer. Pivoting is conditional aggregation: one SUM(CASE WHEN ... THEN ... END) per output column, GROUP BY the row key. The column list is fixed at parse time, so the first decision is whether the month set is known and stable, if it isn't, pivot in the BI layer or generate the SQL. The second is what a missing month means: NULL or 0. They are different facts, and AVG treats them differently.
SELECT
product,
SUM(CASE WHEN month = DATE '2024-01-01' THEN revenue END) AS jan,
SUM(CASE WHEN month = DATE '2024-02-01' THEN revenue END) AS feb,
SUM(CASE WHEN month = DATE '2024-03-01' THEN revenue END) AS mar
FROM monthly_revenue
GROUP BY product;
No ELSE. Trace it on four rows: Basic has (Jan, 12000), (Feb, 13500), (Mar, 14100); Pro has only (Mar, 9800).
- Basic, Jan: CASE yields 12000, the other two yield NULL,
SUM= 12000. Feb 13500, Mar 14100. - Pro, Jan: CASE yields NULL,
SUM(NULL)= NULL. Feb NULL. Mar 9800.
| product | jan | feb | mar |
|---|---|---|---|
| Basic | 12000 | 13500 | 14100 |
| Pro | NULL | NULL | 9800 |
Why NULL, not 0. SUM ignores NULLs either way, so the totals are identical and the bug hides until something averages or counts. Pro launched in March with 39,200 across 4 months. Averaging across the twelve pivoted columns with NULLs gives 39200/4 = 9800; with ELSE 0 it gives 39200/12 = 3267, so Pro reads 67% weaker than it is. COUNT(jan) returns 4 versus 12 for the same reason. Use 0 only when the business fact really is "zero revenue that month": a product that was live and sold nothing.
Alternatives. Postgres and standard SQL:2003 have SUM(revenue) FILTER (WHERE month = DATE '2024-01-01') AS jan. Same plan, less noise. BigQuery, Snowflake and Spark SQL have a PIVOT clause taking the aggregate and an IN list; underneath it is the same conditional aggregation, and the column list is still written out. When the month set isn't known ahead of time, either build the list from SELECT DISTINCT month and execute dynamic SQL, or leave the data long and pivot in the semantic layer. Dynamic SQL is the one to be careful with: months interpolated from user input are an injection path, and every new month produces new query text, so plan caching and downstream schema drift.
Failure modes and how to catch them. Wrong grain: if monthly_revenue holds more than one row per (product, month), say one per region, the pivot silently sums them, so each cell is right but the row is a rollup, not a month. Check with SELECT product, month, COUNT(*) FROM monthly_revenue GROUP BY product, month HAVING COUNT(*) > 1. Column drift: a hardcoded month list quietly drops months added later; compare COUNT(DISTINCT month) in the source against the number of columns. The reconciliation that catches all of it is summing each pivoted row and comparing to the unpivoted total per product:
SELECT product, SUM(revenue) AS total FROM monthly_revenue GROUP BY product;
If those disagree, the CASE predicates don't partition the months.
Curated: · Written: · Reviewed:
Q&A-7Write the logic for month-over-month revenue growth. What edge cases matter?
What I'd say first
Before writing the query I'd pin down three things: what counts as revenue (captured payments net of refunds, or bookings), which timezone defines the month boundary, and whether the source table is guaranteed to have one row per month. The first two change the number; the third decides whether LAG is even the right tool.
The query itself is short: order by month, take LAG of revenue, divide. The work is in guaranteeing the series is dense and in deciding what to do when the prior value is zero, negative, or absent.
WITH months AS (
SELECT generate_series(DATE '2024-01-01', DATE '2024-12-01',
INTERVAL '1 month')::date AS month
),
rev AS (
SELECT date_trunc('month', booked_at)::date AS month,
SUM(amount) AS revenue
FROM orders
WHERE status = 'captured'
GROUP BY 1
)
SELECT m.month,
COALESCE(r.revenue, 0) AS revenue,
LAG(COALESCE(r.revenue, 0)) OVER (ORDER BY m.month) AS prior,
CASE WHEN LAG(COALESCE(r.revenue, 0)) OVER (ORDER BY m.month) > 0
THEN COALESCE(r.revenue, 0)
/ LAG(COALESCE(r.revenue, 0)) OVER (ORDER BY m.month) - 1
END AS growth
FROM months m
LEFT JOIN rev r USING (month)
ORDER BY m.month;
The GROUP BY matters as much as the spine: if the fact table has several rows per month, LAG reads whichever row the sort happened to place last, not the month total.
Trace
| Month | Revenue | Prior | Growth |
|---|---|---|---|
| Jan | 100,000 | NULL | NULL |
| Feb | 118,000 | 100,000 | 18.0% |
| Mar | 146,000 | 118,000 | 23.7% |
| Mar, no Feb row | 146,000 | 100,000 | 46.0%: wrong |
The last row is the failure: LAG walks rows, not calendar months. Drop February from the source and March compares against January, reporting two months of change as one.
Trade-offs on making the series dense
- Persisted calendar table. One definition of "month" shared by every report, can carry fiscal periods and an is_complete flag. Costs a table to maintain and backfill.
- date_spine CTE (above). Self-contained, reviewable in the same diff, no object to keep in sync. Recomputed per query, and you must generate a range wide enough to cover the earliest revenue month: a spine starting after the first sale silently drops it.
- COALESCE on a sparse dimension. This fills NULLs in the joined result; it does not create missing rows. It's what you use after the spine exists, not instead of it.
Plain LAG over ORDER BY month is fine when the source is already dense: a monthly snapshot table written by a job that inserts a row every month regardless of activity. Then the spine is dead weight. Confirm density once with a count check rather than assuming it.
Edge cases
- First month: prior is NULL, growth is undefined, not 0%.
- Prior month zero: division by zero. Return NULL and label it "new", not 0% or ∞.
- Prior month negative (refunds exceed sales): the ratio flips sign and "-150% growth" is meaningless. Report the absolute delta instead.
- Partial current month: the last month is incomplete, so it compares a partial month to a full one. Exclude it or flag it.
- Restatement: if last month's figure is restated after close, growth changes retroactively. Snapshot the numbers you reported.
- Timezone: date_trunc in UTC vs local shifts which payments land in which month.
Curated: · Written: · Reviewed:
Q&A-40December sales are up 40 percent on November. Is that good news?
Not on its own. A 40 percent month-over-month jump is a fact about December versus November, not about the business. For most retailers December is always far above November, so the headline is mostly calendar.
What I'd ask first: is this seasonal, and do we have last December? If yes, the number that decides it is the year-over-year comparison on an aligned calendar. If the business is genuinely non-seasonal (a B2B subscription book, say) month-over-month is defensible, but I'd still check the historical distribution of MoM changes before calling +40% good.
The arithmetic that settles it:
| Comparison | Sales | Change |
|---|---|---|
| November 2026 | $1.00m | — |
| December 2026 | $1.40m | +40% MoM |
| December 2025 | $1.49m | −6% YoY |
Same headline, opposite conclusion. The business shrank 6 percent year over year while the deck says 40 percent growth.
Alignment matters as much as the window. December 2026 and December 2025 can differ by a trading day and by where the holidays land, so compare comparable retail weeks, the five weeks ending the last Saturday, rather than calendar months. A single extra trading Saturday is a few percent of monthly revenue for a retailer, which is the same order as the growth you're trying to detect.
Failure mode I've seen: a 40 percent MoM rise went into a board deck as growth, December was actually down 6 percent YoY, and the miss only surfaced when January came in weak and the annual number was already blown. Detection is cheap: put MoM and YoY on the same dashboard and alert on the divergence (MoM positive, YoY negative) rather than on either series alone.
Two things to check before you trust either number. Was November depressed? A stockout, an outage, or a promo pulled forward from December makes MoM look like growth when the level never moved; a two-year stack or a three-month rolling average separates the two. And is the base comparable? A new product line or a new market has no prior December, so use a seasonally adjusted series or a matched control (same cohort, same promo calendar) instead of raw MoM.
Month-over-month is the right lens when you're dating a step change or you have no prior-year data. For a seasonal business reporting December, it's the wrong one.
Curated: · Written: · Reviewed:
Q&A-11How would you build a signup-to-purchase funnel from an events table?
The 90-second answer. A funnel is an ordered, per-user, windowed count. One query: take each user's first timestamp per step, require each step's timestamp to be strictly after the previous one, and bound the last step by a window measured from the previous step. Then state the three choices that decide the number (ordering, window length, and which occurrence of a repeatable step counts) because the same table yields 13% or 28% depending on them.
Assumptions: one row per event, user_id stable across sessions and devices, ts is server-side UTC, and the cohort is users who signed up inside the period.
-- PostgreSQL 16
WITH firsts AS (
SELECT user_id,
MIN(ts) FILTER (WHERE event = 'visited') AS t_visit,
MIN(ts) FILTER (WHERE event = 'signed_up') AS t_signup,
MIN(ts) FILTER (WHERE event = 'purchased') AS t_purchase
FROM events
WHERE ts >= '2024-06-01' AND ts < '2024-07-01'
GROUP BY user_id
)
SELECT count(*) FILTER (WHERE t_visit IS NOT NULL) AS visited,
count(*) FILTER (WHERE t_signup > t_visit) AS signed_up,
count(*) FILTER (WHERE t_purchase > t_signup
AND t_purchase < t_signup + interval '7 days') AS purchased_7d
FROM firsts;
Trace it on four users. A visits 10:00, signs up 10:05, buys three days later. Counted at all three steps. B buys with no visit or signup row, a returning customer: counted nowhere, because t_signup > t_visit evaluates to NULL. C visits, signs up, buys nine days later: counted at visited and signed_up, not purchased_7d. D signs up at 10:00 and the visit event lands at 10:02. Dropped at signed_up, because the comparison is strict.
| Step | Users | Step conversion |
|---|---|---|
| visited | 50,000 | — |
| signed up | 6,000 | 12.0% |
| purchased within 7 days of signup | 1,260 | 21.0% |
Ordered vs unordered. Unordered counts each step independently, so anyone who ever purchased lands in the purchase bucket whether or not they signed up in the period. I have seen that produce 64% against an ordered 21%, because returning customers bought without a new signup. Unordered is the right query when steps are not causally sequential: "users who ever viewed pricing" is reach, not a funnel.
Window length. Same table, illustrative: 1 day gives 780 (13.0%), 7 days 1,260 (21.0%), 30 days 1,690 (28.2%). Report the curve, not one point. The window is a product decision (trial length, consideration period) and it must be anchored to the previous step, not to funnel entry, or a slow signup eats the purchase budget.
First-touch vs any-touch. MIN(ts) is first-touch. For repeatable steps (viewed pricing, added to cart) last-touch or any-touch changes which session is credited; for one-shot steps (signup, first purchase) it does not. Dedupe by user_id, never by email: one person with two accounts is one user.
When strict ordering is wrong. Late-arriving events, clock skew between services, and mobile SDKs buffering offline all produce a signup timestamp before the visit that caused it. If D's pattern is more than a fraction of a percent of signups, either allow a tolerance (signup within 60s before visit still counts) or order by session id rather than raw timestamp.
Failure modes and how to catch them. Step counts that are not monotonic, or conversion above 100%, mean an unordered count or a missing join: assert visited >= signed_up >= purchased_7d in the query's test. A number that will not reproduce means the window is unstated or anchored wrong; re-run the same query against a 7-day-old snapshot and diff, which also surfaces late events. Bot or internal traffic inflating the top step shows up as a top-step drop far larger than the signup drop; filter against a known internal user list.
One caveat in the query above: the date filter is on events, so a user who visited in May and signed up in June has t_visit NULL and drops out of signed_up. For a signup cohort, select the cohort first (MIN(ts) WHERE event = 'signed_up' inside the period) and join all of that cohort's events back in, so the pre-signup visit is visible.
Curated: · Written: · Reviewed:
Q&A-10How would you compute monthly retention by signup cohort in SQL?
First pin down what "cohort" means, because the two definitions give different numbers. A signup cohort uses users.created_at and includes people who never activated: good for onboarding funnels, and it makes retention look worse. A first-activity cohort uses MIN(occurred_at) from the event table and measures product retention. Say which one you're computing; the query below is the activity version, Postgres 16.
WITH cohort AS (
SELECT user_id,
date_trunc('month', min(occurred_at))::date AS cohort_month
FROM events
GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id,
date_trunc('month', occurred_at)::date AS activity_month
FROM events
),
sized AS (
SELECT cohort_month, count(*) AS cohort_size
FROM cohort
GROUP BY cohort_month
)
SELECT c.cohort_month,
s.cohort_size,
(EXTRACT(YEAR FROM a.activity_month) - EXTRACT(YEAR FROM c.cohort_month)) * 12
+ (EXTRACT(MONTH FROM a.activity_month) - EXTRACT(MONTH FROM c.cohort_month)) AS month_offset,
count(DISTINCT a.user_id) AS active_users,
round(count(DISTINCT a.user_id)::numeric / s.cohort_size, 4) AS retention
FROM cohort c
JOIN activity a ON a.user_id = c.user_id
JOIN sized s ON s.cohort_month = c.cohort_month
GROUP BY 1, 2, 3
ORDER BY 1, 3;
Three pieces: cohort assigns each user one month, activity collapses to one row per user per month so a user with 400 events in March counts once, and sized fixes the denominator. The offset is year/month arithmetic, not (activity_month - cohort_month) / 30: that drifts across 28- and 31-day months. For weekly buckets, date_trunc('week', ...) and (activity_week - cohort_week) / 7 is exact, because both are Mondays.
Month offset 0 is 100% by construction: every user in the cohort has activity in their own cohort month. That's the sanity check, not a result.
| Cohort | Size | M1 | M2 | M3 |
|---|---|---|---|---|
| Jan | 1,000 | 42% | 33% | 29% |
| Feb | 1,200 | 40% | 31% | — |
| Mar | 900 | 44% | — | — |
Trade-offs. Weekly buckets give a signal in days instead of weeks, but at a few hundred signups a month the weekly denominator is small enough that one enterprise account swings the curve; monthly is the default for B2B, weekly for consumer. Recomputing from raw events is always correct and gets expensive: at a few hundred million events you materialize a (cohort_month, month_offset, active_users, cohort_size) snapshot nightly and let the dashboard read that, accepting that today's cohort is stale by up to 24 hours. Counting distinct users is standard; counting sessions or events inflates retention and hides churn.
Failure modes. The one that bites hardest is filling a date spine with COALESCE(..., 0): months that haven't happened yet become zero activity, the three newest cohorts appear to churn to nothing, and average month-6 retention reads 18% instead of 27%. Leave them NULL or absent. Second, timezone: date_trunc in UTC puts a user who signed up at 23:30 Pacific into the next month's cohort; truncate in the user's timezone if the business reports on local days. Third, denominator drift, if cohort_size is computed over a filtered event set (only event_type = 'login', say) while activity counts everything, the ratio exceeds 1. Fourth, returning users: someone active in M1, gone in M2, back in M3 counts in M1 and M3, which is correct for retention but not for a churn-rate metric.
Checks before you trust the chart. Month 0 is 100% for every cohort; no cell exists beyond the current month; active_users <= cohort_size everywhere; and the sum of M1 active users across cohorts matches a hand count on one cohort.
Curated: · Written: · Reviewed:
Q&A-18How do you compute DAU/MAU, and what does it tell you?
DAU/MAU is the average DAU across the days of a window divided by the distinct users active in that same window. On a 30-day window, multiply the ratio by 30 to get the average days a monthly user is active: 0.18 → 5.4 days, 0.31 → 9.3 days. That is the computation. Everything that matters is upstream of it, in the definition of "active", because the ratio is a function of that definition before it is a function of the product.
| Definition of active | Avg DAU | MAU | DAU/MAU | Implied days/month |
|---|---|---|---|---|
| any app open | 62,000 | 200,000 | 0.31 | 9.3 |
| core action (sent a message) | 36,000 | 200,000 | 0.18 | 5.4 |
The denominator barely moves here because almost everyone who opens the app eventually sends a message at least once in a month; the numerator moves because most opens are not core actions. That asymmetry is why the ratio is so easy to inflate.
Choosing the definition is a trade-off, not a correctness question.
- Loose (any open) is right when the monetised event is the open: an ad-supported app where an impression is the revenue unit. Then the loose number is the one that tracks the business, and the strict number is the vanity metric.
- Strict (core action) is right when the product's value is the action and the open is a means to it: a messaging or collaboration tool.
- The failure is mixing them: quoting the loose ratio to claim engagement and the strict one to claim revenue. Pick one, name it on the chart, and keep it fixed across releases.
Window choices change the number too.
- Calendar month is the default and what most dashboards show, but a 28-day and a 31-day month are not comparable, and the boundary cuts cohorts arbitrarily.
- Rolling 30-day smooths both problems and lags by up to 30 days; it is the better series for trend detection, the worse one for a monthly review deck.
- DAU/WAU reacts faster and is the right ratio for weekday-cadence products. A tool used only Monday–Friday sits near 5/7 ≈ 0.71 on DAU/WAU and near 0.2 on DAU/MAU purely from the workweek, with no stickiness signal at all.
Failure modes and how to catch them.
- Notification inflation. A push campaign lifts opens, the ratio climbs, time spent is flat. Detect it by plotting DAU/MAU against sessions-per-DAU and minutes-per-DAU on the same axis; if the ratio moves and those two do not, the definition moved, not the users.
- Denominator contamination. MAU includes users who churned on day 2, dragging the ratio down. Detect it by cohorting MAU by acquisition month and watching the ratio per cohort rather than in aggregate.
- Compression. The ratio is bounded at 1, so a product at 0.5 cannot show the same absolute improvement as one at 0.1. Compare deltas within a product, never levels across products.
What it does not tell you. A 0.2 can be six consecutive days or six scattered ones; the ratio cannot distinguish them, and those are different products. Retention curves and the session-frequency distribution answer that. DAU/MAU is a one-number summary of frequency, useful for tracking a fixed definition over time and nearly useless for comparing two products with different definitions.
Curated: · Written: · Reviewed:
Q&A-32How would you calculate monthly churn for a subscription product?
Monthly churn is the number of customers who cancel during the month divided by the number of customers active on the first day of that month. Fix the base at day 1, count only cancellations from that base, and report customers acquired and lost inside the same month separately.
Three conventions decide the number, and an interviewer will ask which you picked:
- Base. Customers who sign up mid-month are not in the denominator, and their cancellations are not in the numerator.
- Timing. Count the cancellation on the date the customer cancels, or on the last day of the paid period? On monthly plans that is up to 30 days of difference. Pick one and keep it.
- Gross vs net. Gross churn counts cancellations only. Net churn subtracts expansion and upgrades, so a product with strong upsell can post negative net churn while still losing customers.
September for a monthly consumer subscription:
| Item | Count |
|---|---|
| Active on Sep 1 | 10,000 |
| Of those, cancelled in Sep | 600 |
| Monthly churn | 6.0% |
| New in Sep | 3,000 |
| New in Sep, cancelled in Sep | 450: 15% of the new cohort, reported separately |
The same data supports two wrong answers. Divide all cancellations by the end-of-month active count: end of month is 10,000 − 600 + 3,000 − 450 = 11,950, so 1,050 / 11,950 = 8.8%. Drop the new-customer cancellations from the numerator but leave them in the denominator: 600 / 12,400 = 4.8%. Three numbers, one month. Only 6.0% moves when retention moves; the other two move when acquisition moves.
In SQL, against a subscriptions table with started_at and canceled_at (Postgres 16):
WITH base AS (
SELECT *
FROM subscriptions
WHERE started_at < DATE '2024-09-01'
AND (canceled_at IS NULL OR canceled_at >= DATE '2024-09-01')
)
SELECT
count(*) AS base_customers,
count(*) FILTER (
WHERE canceled_at >= DATE '2024-09-01'
AND canceled_at < DATE '2024-10-01'
) AS churned,
round(100.0 * count(*) FILTER (
WHERE canceled_at >= DATE '2024-09-01'
AND canceled_at < DATE '2024-10-01'
) / count(*), 2) AS churn_pct
FROM base;
Failure modes worth naming:
- Growth hides churn. A 30% acquisition month pushes the end-of-month denominator to 12,400 and the reported rate to 4.8% while the original cohort's retention is unchanged. Detect it by recomputing from the start-of-month cohort and diffing against the dashboard number.
- Involuntary churn. A failed card that ends in cancellation is real churn, but it lands weeks after the customer stopped using the product. If dunning recovers 40% of failed charges, counting at the failed charge overstates churn by that much.
- Mixed billing periods. An annual subscriber can cancel once a year. A monthly churn figure over a book that is half annual is a blend of two clocks; split the cohorts or convert to a monthly-equivalent rate.
- Reactivation. Cancel-and-resubscribe in the same month is one churn and one signup, not zero.
For a product with tiers, logo churn and revenue churn diverge: losing 600 customers on the cheapest plan is 6% logo churn and maybe 2% MRR churn. State which one you are quoting.
Curated: · Written: · Reviewed:
Q&A-33How would you estimate customer lifetime value for a subscription business?
For customer lifetime value estimation, I would state the assumption out loud and then try to break it.
A simple estimate multiplies average monthly margin per customer by the expected lifetime, which is roughly one divided by monthly churn. It uses margin rather than revenue, and it assumes churn stays constant, which usually overstates value for recent cohorts.
Concretely, compute margin per customer per month, estimate churn from recent cohorts, cap the horizon, for example at 36 months, and compare the formula result with the observed cumulative margin of older cohorts.
The reason for that specificity is a failure I have seen: A marketing plan used revenue rather than margin in an LTV of 1,200 dollars, and at a 40 percent margin the real figure was 480 dollars, so the team was paying 600 dollars to acquire customers worth less than that.
LTV built on margin.
| Input | Value |
|---|---|
| revenue per month | $50 |
| margin at 40% | $20 |
| monthly churn | 4% |
| expected lifetime | 25 months |
| LTV on margin | $500 |
I would not consider it settled without evidence: Check the formula result against the actual cumulative margin earned by a cohort that is at least two years old.
Lifetime value is margin, not revenue.
Curated: · Written: · Reviewed:
Q&A-34How do you calculate CAC and payback period, and what makes them misleading?
Start by pinning the definitions and the assumptions that decide them. CAC is acquisition spend divided by the new customers that spend produced, over the same window. Payback is CAC divided by the monthly gross margin a customer generates: revenue minus the cost to serve them, not revenue. The assumptions that matter: which costs are in the numerator (media, sales salaries, tooling, agency fees), whether a "customer" is a new logo or a new seat, and whether the window is a calendar period or a cohort.
The first fork is blended versus paid CAC. Blended divides total acquisition spend by all new customers, including organic. It is the right lens for a board-level efficiency check on the whole go-to-market motion, and the wrong lens for deciding whether to spend more on a channel. Paid CAC divides only paid-attributable spend by paid-sourced customers.
| Lens | Numerator | Denominator | Result |
|---|---|---|---|
| Blended CAC | $200,000 paid spend | 2,500 new customers | $80 |
| Paid CAC | $200,000 paid spend | 1,000 paid customers | $200 |
| Revenue payback | $200 CAC | $60 monthly revenue | 3.3 months |
| Margin payback | $200 CAC | $25 monthly margin | 8 months |
If 1,500 of 2,500 new customers in a quarter came in organically and you spent $200,000 on paid, blended CAC is $80 and paid CAC is $200: a 2.5x difference that changes the decision.
The second fork is margin versus revenue payback. Revenue payback flatters the number. With $60 monthly revenue and $35 cost to serve, margin is $25. On $200 CAC, revenue payback is 3.3 months and margin payback is 8 months. Use revenue payback only as a rough sanity check; use margin payback for anything that gates spend.
Then the part most candidates miss: payback on a static margin assumes the customer stays. Trace a cohort with 4% monthly churn. Survival at month 8 is 0.96^8 = 0.72, so only 72% of the cohort is still paying. Cumulative margin over 8 months is $25 × (0.96 + 0.96^2 + ... + 0.96^8) = $25 × 6.687 = $167. You have not recovered the $200 CAC. Cumulative margin crosses $200 at month 10 ($201), so real payback is 10 months, not 8. That gap is the difference between a channel that looks fundable and one that does not.
Where these numbers mislead, concretely:
- Window mismatch: spend in Q1, customers counted in Q2 after a 45-day sales cycle. Match by cohort or apply a lag.
- Organic counted as paid: inflates the denominator, deflates CAC.
- Blended hiding channel economics: a cheap channel subsidises an expensive one; compute CAC per channel.
- Revenue instead of margin: understates payback by the gross margin ratio.
- Ignoring churn before payback: as above.
- Fully loaded versus media-only spend: including sales salaries can double CAC versus media-only; state which you mean.
- Expansion revenue: payback on first-order margin ignores upsell, which can shorten it; but do not count expansion you have not earned.
When to use which: blended CAC and margin payback for portfolio-level capital allocation; paid CAC per channel and cohort payback for the spend decision; revenue payback only when margin data is unavailable and you flag it as a proxy.
Curated: · Written: · Reviewed:
Q&A-44How would you tell whether each order is profitable?
Clarify first: "Profitable" has two defensible definitions, and they give different answers. Contribution margin per order is revenue minus the variable costs that order causes: product, payment fees, shipping, returns. Fully loaded cost also spreads fixed costs (warehouse lease, salaried ops, platform) across orders. I'd answer with contribution margin, because it's the number that changes when you take one more order, and say so explicitly. If the interviewer wants fully loaded, the same method applies with an allocation rule attached, and the allocation rule is where the argument moves to.
The mechanism. An order is contribution-positive when the revenue it brings exceeds the costs it triggers. Costs that don't move with order count (the lease, the salaried team) don't belong in this number; including them makes a profitable order look unprofitable and hides which orders to cut.
Worked example (hypothetical figures). A delivery service computed contribution by basket band:
| Basket size | Revenue | Variable cost | Contribution |
|---|---|---|---|
| under $15 | $11.00 | $13.40 | -$2.40 |
| $15 to $40 | $27.00 | $22.10 | $4.90 |
| over $40 | $62.00 | $44.30 | $17.70 |
The under-$15 band loses $2.40 per order. When the service promoted small baskets to grow order count, losses rose 18% in a month: order count grew, contribution fell. The band, not the total, is what exposed it.
Trade-offs and when this lens is wrong.
- Contribution vs fully loaded. Contribution tells you whether to take the next order. Fully loaded tells you whether the business covers its fixed base. A segment can be contribution-positive and still not cover its share of fixed cost: that's a scale question, not a per-order one.
- Per-order is the wrong lens when a shared resource is the constraint. If delivery capacity or a scarce SKU is the bottleneck, the right metric is contribution per unit of that resource (per delivery slot, per pallet), not per order. A $4.90 order that frees a slot can beat a $17.70 order that consumes two.
- Acquisition cost is not a per-order variable cost. CAC belongs at the cohort level; folding it into per-order contribution double-counts it and makes every first order look unprofitable.
What I'd do, and what would change it. Compute contribution per order by band and region, reconcile the total against the finance margin to confirm the cost list is complete, and flag negative bands. I'd act on the negative bands: raise the minimum basket, add a small-order fee, or stop promoting them. I'd change the decision if the negative band turns out to be an acquisition channel for high-value repeat customers: then the per-order loss is a CAC, and the test is cohort lifetime contribution, not the single order.
Curated: · Written: · Reviewed:
Q&A-41How would you segment customers to understand who drives revenue?
What I'd say first (60–120 seconds)
Before segmenting I'd pin down the decision the segmentation feeds, because that picks the method. "Who drives revenue" for a retention budget is a different question from "who should sales call" or "who do we acquire more of". Then I'd default to a behavioural segmentation on revenue concentration, because revenue is almost always concentrated, and I'd verify that rather than assume it.
Choosing the method
| Approach | Best when | Fails when |
|---|---|---|
| Demographic / firmographic | Acquisition targeting; no transaction history yet | Explaining spend: two 30-year-olds differ 100x |
| Cohort / lifecycle (signup month, tenure, plan) | The question is churn or net revenue retention | Acquisition mix shifts between cohorts, confounding the curve |
| Behavioural value (RFM, spend deciles, usage depth) | The question is revenue concentration | Long purchase cycles: annual contracts make recency meaningless |
| Model-based (k-means/GMM on scaled features, or a supervised LTV/churn model) | Many features, structure you haven't hypothesised | You need marketing to act on it: clusters shift between runs and resist naming |
RFM is the wrong tool when revenue is contractual (in a subscription, recency is a churn signal, not a value signal), when usage doesn't map to revenue (freemium), and when the decision is acquisition, where there is no history to score.
Worked example (illustrative figures)
100,000 customers, 12 months of orders. Score R, F, M in quintiles → 125 cells; collapse to named segments.
| Segment | Customers | Revenue share | ARPU |
|---|---|---|---|
| Champions | 8,000 (8%) | 41% ($16.4M) | $2,050 |
| Loyal | 17,000 | 28% ($11.2M) | $659 |
| At risk | 22,000 | 14% ($5.6M) | $255 |
| Lapsed | 53,000 | 17% ($6.8M) | $128 |
Total revenue $40M. Champions are 8% of customers and 41% of revenue: 16x the ARPU of lapsed. That ratio is the number that justifies a retention budget.
Failure modes
- Too many cells. 125 cells over 100k customers leaves a median cell of ~800, but the tail cells hold fewer than 50: no test reaches significance on them. Detect it by checking median and 10th-percentile cell size before presenting.
- Drift. Recompute monthly and measure migration. If more than 30% of customers change segment month over month, the boundaries are noise, not behaviour.
- Revenue ≠ margin. A segment can be 40% of revenue and negative after support, returns and discounts. Join to gross margin before recommending spend.
- Survivorship. Lapsed customers who closed their accounts vanish from the table, so "lapsed" understates the loss.
I'd also check the segmentation beats a naive spend-decile split on whatever the decision metric is. If it doesn't, the extra complexity buys nothing.
What I'd present
Segment, customer count, revenue share, gross margin, the action, and the owner. A segment nobody can act on is a chart, not an analysis.
Curated: · Written: · Reviewed:
Q&A-19Your query takes 20 minutes. What would you try before asking for more compute?
First two minutes
I'd pin down what the 20 minutes is before touching anything: a hard SLA on a dashboard, a one-off backfill, or a job that's merely annoying. That decides whether the goal is "faster" or "cheap enough to stop caring". Then I'd read the query plan, because the plan tells me which cost I'm actually paying: bytes scanned, shuffle, skew, or a bad join order. My default assumption is that the query reads far more than it needs, and that assumption is usually right, but it's an assumption I'd verify against the plan rather than act on.
What I'd try, in order
- Prune. Filter the partition column with a range, never a function.
- Project. Name the columns; drop
SELECT *. - Pre-aggregate the large side before the join.
- Inspect the join itself: fan-out and key skew.
- Then caching, materialized views, incremental processing, sampling.
- Then more compute.
Worked example (hypothetical; a columnar warehouse scanning about 2 GB/s across the cluster)
| Change | Bytes scanned | Runtime |
|---|---|---|
| original | 2.4 TB | 20 min |
range filter on created_at | 18 GB | 9 s |
4 named columns instead of SELECT * | 6 GB | 3 s |
2.4 TB at 2 GB/s is 1200 s, which is the 20 minutes. Pruning to one week's partition drops it to 18 GB, and projecting 4 of 40 columns in a columnar store drops it again to 6 GB. The join is a separate axis: the fact table fans out 40:1 against the dimension, so a 6 GB scan becomes a 240 GB shuffle. Pre-aggregating the fact table to one row per join key before the join cuts that shuffle to roughly 6 GB.
Where each alternative wins, and what it costs
| Technique | Wins when | Cost or risk |
|---|---|---|
| Partition pruning | Table is partitioned on a column the query filters | A function-wrapped predicate silently disables it |
| Column projection | Wide table, few columns needed | Breaks consumers that expect the full row |
| Pre-aggregation | Many-to-one join fan-out | Wrong if the aggregate isn't additive |
| Materialized view | Same query shape repeats, freshness tolerance ≥ refresh interval | Refresh cost; a 5-minute-fresh dashboard can't use a nightly view |
| Incremental processing | Append-only facts, only new partitions matter | Late-arriving data and backfills need a separate path |
| Sampling | Exploratory or approximate answers | Not acceptable for billing or anything with a correctness guarantee |
| More compute | One-off backfill, or a plan that is already minimal | Doubles cost for a linear speedup |
Deciding criteria. Freshness requirement, one-off versus repeated, whether the answer must be exact, and the ratio of engineer time to compute cost. A query that runs hourly on a 20-node cluster burns 8 hours of cluster time a day; a day of engineering to take it from 20 minutes to 3 seconds pays back in weeks. A backfill that runs once is the opposite: doubling the cluster for ten minutes is cheaper than a day of engineering, and I'd say so.
Failure modes and how I'd detect them.
- Function-wrapped predicate kills pruning. Detect by comparing bytes scanned in the plan before and after; if the number doesn't move, the predicate isn't pruning.
- Stale statistics make the planner pick a broadcast join that OOMs. Detect when estimated rows differ from actual by orders of magnitude in the plan.
- Key skew leaves 19 of 20 nodes idle. Detect in the per-task duration histogram.
- Caching a query whose freshness requirement is tighter than the TTL produces wrong answers, not slow ones.
- Sampling a metric that feeds a decision presents an approximation as exact.
Verification. Compare bytes scanned and shuffle bytes in the plan before and after each change. A change that moves neither is noise, and I'd revert it.
If the plan is already minimal (a full scan of a table I genuinely need, no filter, no fan-out) then more compute is the right call, and I'd bring the plan to that conversation as the evidence.
Curated: · Written: · Reviewed:
Q&A-23You need a quick look at a 5-billion-row table. How do you sample it safely?
What I'd say first
Before picking a method I'd pin down two things: what number I need (a share, a distribution, a join sanity check) and how much I'm allowed to scan. Then: never LIMIT, and never ORDER BY RANDOM() on 5B rows. Use a deterministic hash sample on the entity key, sized to the precision I need, and validate it against the full table on two cheap aggregates before I quote anything.
Why LIMIT is not a sample
LIMIT 100000 returns the first 100k rows the engine happens to read. On a table clustered or partitioned by date, that's the oldest partition. On a table clustered by user_id, it's one or two users. Either way you've sampled one dimension, not the table.
Methods, with what they cost
| Method | What it reads | Representative? | Reproducible? |
|---|---|---|---|
LIMIT n | first n rows in storage order | no: one partition or key range | yes, but wrong |
ORDER BY RANDOM() LIMIT n | full scan + top-n sort | yes | no |
TABLESAMPLE SYSTEM (p) | p% of blocks | biased if data is clustered | yes |
TABLESAMPLE BERNOULLI (p), Snowflake SAMPLE (p) | full scan, keeps p% of rows | yes | yes |
| hash of entity key | full scan, or partition pruning | uniform over entities | yes, with a fixed seed |
The one I'd actually run
Hash the entity key so every sampled user keeps all their events. That matters when the metric is per-user (retention, sessions per user): row-level sampling splits users across the sample and the full table, and you can't reconstruct them.
-- BigQuery; 0.01% of users, fixed seed 42
SELECT user_id, event_ts, platform
FROM `proj.dataset.events`
WHERE MOD(ABS(FARM_FINGERPRINT(CONCAT(user_id, ':42'))), 10000) = 0
Two things to watch. FARM_FINGERPRINT(NULL) is NULL, so MOD(NULL, ...) is NULL and those rows drop silently: COALESCE(user_id, '') first. And the seed belongs in the query text, not in your head; without it the number isn't reproducible.
This is cluster sampling, not simple random sampling. Row-level shares stay unbiased, but variance is higher when events within a user are correlated, so size up if the metric is per-row rather than per-user.
Sizing
For a share estimate, the 95% margin of error is roughly 1/sqrt(n). 10,000 rows gives ±1%; 1,000 gives ±3%. So 0.001% of 5B (50,000 rows) is plenty for "what fraction of traffic is mobile". You don't need 1%.
Validating before you quote
Compare the sample against the full table on two aggregates that are cheap to compute exactly: MIN/MAX(event_ts) and COUNT(DISTINCT user_id). If the sample's date range is 2019-only, you've reproduced the LIMIT bug with extra steps.
The failure I've seen: an analyst ran LIMIT 100000 on a date-partitioned table, got 2019 rows only, and reported mobile at 20% when the full table was 64%. One query on the sample's date range would have caught it.
Curated: · Written: · Reviewed:
Q&A-84Why is a row-by-row apply slow in pandas, and what do you use instead?
apply(func, axis=1) runs your Python function once per row. Each call pays interpreter dispatch, builds a Series for the row, boxes each cell into a Python object, calls the function, unboxes the result, and appends to a list that becomes the output Series. Vectorized column ops skip all of that: pandas hands two contiguous numpy arrays to a compiled C loop that never creates a Python object per element. On 8M rows the gap is routinely 50-150×.
What the per-row path actually does. For df.apply(f, axis=1) pandas, for each row: slices the row into a new Series (allocation), converts each scalar to a Python object (boxing), invokes f through the interpreter, converts the return back, and stores it. That is roughly 50-100 µs of overhead per row before your logic runs. A vectorized expression like df["a"] * df["b"] calls one C function over the whole column: no per-row Series, no boxing, and the loop runs over contiguous memory the CPU can prefetch.
Code and trace.
import pandas as pd
df = pd.DataFrame({"qty": [2, 5, 3], "price": [10.0, 4.0, 7.0]})
def total(row):
return row["qty"] * row["price"]
df["apply_total"] = df.apply(total, axis=1)
df["vec_total"] = df["qty"] * df["price"]
Step by step:
apply(axis=1)builds a Series per row. Row 0's Series is{"qty": 2.0, "price": 10.0}:qtyis upcast to float64 because the row mixes int64 and float64 columns.totalreturns2.0 * 10.0 = 20.0.- Row 1:
{"qty": 5.0, "price": 4.0}→20.0. - Row 2:
{"qty": 3.0, "price": 7.0}→21.0. apply_total=[20.0, 20.0, 21.0], dtype float64.vec_total=qty * priceelementwise =[20.0, 20.0, 21.0], dtype float64.
Same values here, but note row["qty"] is 2.0, not 2. If the function did integer division or bit operations, that upcast changes the answer.
Arithmetic. 8M rows at ~80 µs/row = 640 s ≈ 10.7 min. Vectorized at ~0.75 µs/row = 6 s. Ratio ≈ 107×.
| Method | Rows | Runtime |
|---|---|---|
apply per row | 8,000,000 | ~11 min |
numpy.where / column arithmetic | 8,000,000 | ~6 s |
When apply is the right call. Vectorization is not always available or worth it:
- Logic that branches on many columns, calls an external service, or parses free text per row: there is no column expression for it.
- Small frames. Under ~10k rows the overhead is milliseconds; readability wins.
- When you need speed but the logic is complex, compile it instead of vectorizing:
numba.njitoverdf.to_numpy(), or Cython.itertuples()is ~10× faster thanapplyfor simple iteration because it yields a namedtuple and skips the per-row Series.
Failure modes.
- Silent dtype change.
applyupcasts mixed-dtype rows to a common dtype, and returnsobjectdtype if the function returns mixed types. Checkdf.dtypesafter. - NaN disagreement. Vectorized ops propagate NaN; a Python
if x > 0treats NaN as False without error.np.whereandapplycan disagree on NaN rows. - Division by zero. Vectorized gives
inf; Python raisesZeroDivisionError. Different failure, different fix. - Detection. Profile with
cProfile/%prunor time a 100k-row sample; grep hot paths forapply/itertuples; watch forobjectdtype columns indf.dtypes.
Curated: · Written: · Reviewed:
Q&A-80How do you make your analysis reproducible for someone else?
What a strong candidate says first
Reproducible means one thing: someone else, on a clean machine, gets the same numbers you did, without asking you anything. That needs four things pinned: the code, the environment, the data snapshot, and a single entry point. Miss any one and the analysis is a story, not a result.
Clarify scope before choosing machinery. A one-off number for a decision already made needs a committed query and a recorded extraction date. A metric that feeds a recurring report, or that an auditor may revisit, needs the full stack.
The three ways it breaks
| Drift source | What changes | How you catch it |
|---|---|---|
| Code | Query edited in place; notebook cells run out of order | Commit hash recorded next to the output; restart-and-run-all in CI |
| Environment | A dependency bump changes a default (sort order, float formatting, null handling) | Lockfile with hashes; container image digest |
| Data | Source table backfilled, late rows arrive, timezone shifts | Extraction date, row count, checksum of the input; an --as-of parameter |
Every output should carry its provenance. Hypothetical header for a query file:
-- churn.sql
-- source: warehouse.orders
-- extracted: 2026-09-10, 1,204,338 rows
-- filters: status = 'paid', exclude internal staff accounts
-- commit: 4f2a91c
Trade-offs: how heavy to go
Lightweight: repo with a README, a loosely pinned requirements file, the query in a .sql file, the notebook committed, extraction date in the header. Cost: an hour. It fails when the analysis is rerun months later against a source table that has moved or been backfilled.
Heavy: lockfile with hashes, container image, dbt models with tests, a snapshot of the input table, an orchestrated run. Cost: days up front plus ongoing maintenance. It is warranted when the number is quoted outside the team, reruns on a schedule, or drives a decision someone will revisit.
Two rules of thumb. Pin the environment when the analysis outlives the sprint. Snapshot the data when the source is mutable and you cannot re-extract the same rows.
Notebooks versus scripts: notebooks are good for exploration and for showing reasoning, and bad as the artefact of record, because cell order and execution count diverge from file order. If the deliverable is a notebook, add a restart-and-run-all check in CI, or port the final path to a script and keep the notebook as the narrative.
Failure modes
- Query edited in place: the board number cannot be rebuilt. Detect: the output has no commit reference.
- Notebook run out of order: a cell depends on a variable defined three cells later. Detect: restart-and-run-all fails.
- Floating dependency: a minor bump changes a default and the total shifts in the last decimal. Detect: lockfile diff.
- Backfilled source: the same query returns different rows. Detect: row count or checksum mismatch against the recorded extraction.
- Unseeded sampling: two runs give different confidence intervals. Detect: run twice, diff.
The check that settles it
One command, run twice, diffed:
make run && cp out/churn.csv /tmp/a
make run && diff /tmp/a out/churn.csv # no output means deterministic
Then hand the repo to a colleague who has never seen it and ask them to regenerate one headline number. If they have to ask you a question, it is not reproducible yet.
Curated: · Written: · Reviewed:
Q&A-14How do you keep a 200-line analysis query readable and correct?
Assume Postgres 12+ or a warehouse that inlines CTEs (BigQuery, Snowflake). "Correct" here means two checkable things: each step's grain is what I claim, and no join fanned rows out or dropped them. Readability is how I get there, not the goal.
My default is one CTE per logical step, each named for its grain, and I check the row count of each step while building the next. That default has a cost, and there are cases where it is the wrong tool.
Why it works: a CTE gives an intermediate result a name, so you can run it alone and compare its row count to what the grain predicts. A five-level nested subquery hides where rows were lost or duplicated, because you cannot inspect level three without rewriting the query.
-- Postgres 12+
WITH paid_orders AS ( -- grain: one row per order
SELECT order_id, customer_id, amount
FROM orders
WHERE status = 'paid'
),
customer_totals AS ( -- grain: one row per customer
SELECT customer_id, SUM(amount) AS total
FROM paid_orders
GROUP BY customer_id
)
SELECT customer_id, total
FROM customer_totals
WHERE total > 1000;
Trace: orders holds 90,000 rows; the filter leaves 81,000, so paid_orders is 81,000 rows. The GROUP BY collapses to one row per distinct customer_id, giving 22,400. If customer_totals returns 22,900, the grain assumption is broken, a NULL customer_id or a duplicate, and I stop before building the next step.
| CTE | Grain | Rows |
|---|---|---|
| paid_orders | order | 81,000 |
| customer_totals | customer | 22,400 |
| final | segment | 5 |
Alternatives, and when they win:
- Nested subqueries: fine at two or three levels; past that, nobody can check an intermediate.
- Temp tables: materialize once and reuse across queries, or when a step is expensive and referenced many times. Cost is a write and a stale-data risk if the session lingers.
- dbt models: when the query is a recurring pipeline rather than a one-off. You get tests and lineage, but a build cycle per change.
The cost of many CTEs: in Postgres 12+ a CTE referenced once is inlined, so predicates push down and the plan matches the equivalent subquery. A CTE referenced more than once, or marked MATERIALIZED, becomes an optimization fence: the planner cannot push a WHERE into it and materializes the whole result. Ten CTEs also add planning time. So "one CTE per step" can turn a 200 ms query into seconds if you materialize a large intermediate.
Failure modes I watch for: an inner join that silently drops rows (row count falls), a fan-out join that multiplies them (row count rises), and grain drift where a step quietly changes from per-order to per-line.
What changes the decision: if the query is a recurring model, move it to dbt; if one CTE is expensive and reused, a temp table or materialized view; if planning time dominates, fewer, larger CTEs.
Curated: · Written: · Reviewed:
Q&A-81What can go wrong with VLOOKUP, and what would you use instead?
VLOOKUP's fourth argument is optional, and when you leave it out it defaults to TRUE: approximate match. That default, not the function itself, causes most of the damage. On a sorted first column, approximate match returns the largest key less than or equal to the value you searched for, so a key that is missing returns the previous row's value with no error at all. The second hazard is the column index: VLOOKUP names its return column by position, so inserting a column to the left of it shifts every result by one and nothing complains.
Approximate match is a binary search that assumes the first column is sorted ascending. If it is not, the result is undefined: Excel does not fall back to a linear scan, it just returns whatever the bisection lands on. Exact match (FALSE or 0) does a linear scan and returns #N/A when the key is absent, which is the behaviour you want in almost every business lookup.
Key hygiene matters as much as the match mode. VLOOKUP compares case-insensitively, so "abc" and "ABC" collide. A number stored as text ("1045") will not match the number 1045 under exact match, and under approximate match it may match something else entirely. Trailing spaces, non-breaking spaces pasted from a web export, and leading apostrophes all produce the same class of silent miss. VLOOKUP also cannot look to the left of its key column, which forces people to duplicate columns and then keep the copies in sync.
Use XLOOKUP where it exists: Excel 365 and Excel 2021 and later. Its match mode defaults to exact, it takes an if_not_found argument so you can return a sentinel instead of #N/A, and it looks in any direction. On older perpetual versions, INDEX with MATCH and a match type of 0 gives you exact match plus left lookup. In both cases, pass the exact-match argument explicitly rather than relying on a default.
A commission sheet I worked on used VLOOKUP with the default approximate match against a rep table sorted by ID. Two hundred and fourteen reps were paid another rep's rate, an overpayment of about 31,000 dollars, and the sheet showed no errors: every cell resolved to a number.
| Formula | Rep ID looked up | Result |
|---|---|---|
| VLOOKUP, default match | 1045, not in table | rate of rep 1044 |
| XLOOKUP, exact match | 1045, not in table | not found |
The first row is the whole problem: a wrong number that looks like a right one. The second row fails loudly, which is what you want.
Detection is cheap and worth building into the sheet. Count the not-found results with COUNTIF over ISNA, and compare that count against the number of keys you expected to match. Reconcile a control total, sum of looked-up rates against sum of source rates, so a shifted column index shows up as a difference rather than as plausible-looking rows. Spot-check a sample of keys against the source table, including at least one key you know is absent, to confirm the formula actually returns not found. Then standardise keys before matching: TRIM, strip non-breaking spaces, and force number and text types to agree on both sides of the join. Always pass an exact-match argument, and prefer a function whose default is exact. That is the whole answer: the default is the bug, and the fix is a lookup that fails loudly.
Curated: · Written: · Reviewed:
Q&A-82When is a spreadsheet pivot table the right tool, and when is it not?
Use a pivot table when the question is exploratory, the source is one flat table, and a human will read the answer once. Move to SQL or Python when the analysis repeats on a schedule, joins more than one source, needs row-level audit, or has to be reproducible by someone who wasn't there.
Assumptions: Excel or Google Sheets, source is a single table or named range, and the data fits in the app's memory.
The mechanism that decides most cases is the pivot cache. Creating a pivot copies the source range into a cache and stores the range address with it. Refresh re-reads that same address; it does not re-derive the range. The pivot is a snapshot of a fixed rectangle, not a query against a table. Two failure modes follow directly.
A fixed range silently drops new rows. A weekly report was built on Sheet1!$A$1:$H$50000. When the export grew past 50,000 rows, refresh kept reading the first 50,000 and the newest orders never appeared:
| Week | Source rows | Rows in pivot |
|---|---|---|
| 1 | 48,000 | 48,000 |
| 5 | 61,000 | 50,000 |
Nobody noticed for a month because the totals still looked plausible. The fix is a table (Ctrl+T) or a dynamic named range, so the address grows with the data, but that only helps if the pivot was built on it from the start.
Calculated fields aggregate, then compute. In Excel, a pivot calculated field evaluates on the sums in the cache, not on source rows. If you add a per-row margin = profit/revenue column and then average it in the pivot, small orders count as much as large ones and the result is not the business margin. Sum(profit)/Sum(revenue) is the figure you want, and it is a different number.
Detection for both: reconcile every refresh. Compare the pivot's grand total row count and sum against COUNTA and SUM over the source. If they differ, the pivot is stale or truncated. This is a two-cell check, not a test suite.
Where pivots stop being the right tool:
- Recurring refresh on a schedule: the manual steps are the audit gap.
- More than one source: a pivot cannot join; you need Power Query or SQL.
- Row-level traceability: the cache holds aggregates; you cannot drill to the contributing rows without going back to the source.
- Size: Excel caps a sheet at 1,048,576 rows, and a pivot cannot exceed its source. Anything approaching that is already the wrong tool.
The trade-off runs both ways: a pivot is zero setup and an analyst can iterate in seconds, which is why it wins for one-off exploration. SQL or Python costs a schema and a repo, and buys reproducibility, joins, and a diff when the logic changes.
A pivot is a quick answer, not a pipeline.
Curated: · Written: · Reviewed:
Q&A-76You receive a new dataset. What checks do you run before analysing it?
The 90-second answer
Two questions before I touch the data: what decision does this feed, and which system is the source of truth I can reconcile against? Then I profile against expectations I wrote down before looking, because a check invented after seeing the data always passes.
The checks, ordered by how much they catch:
- Row count and grain. Does one row mean one order, one order line, one customer-day? Count against the source system's own count.
- Date range. Min and max of every timestamp column, in the source timezone.
- Key uniqueness. Duplicates on the grain key.
- NULL and sentinel rates. Including
-1,N/A,1900-01-01: NULLs wearing a costume. - Ranges and category lists. Negative quantities, future dates, a status value that didn't exist last month.
- Join coverage. What fraction of rows survive the join to the dimension table.
A hypothetical extract, expectations written first:
| Check | Expected | Found |
|---|---|---|
| rows | 412,880 | 412,880 |
| date range | 2024-01-01 to 2024-09-10 | 2024-01-01 to 2024-06-30 |
| duplicate order ids | 0 | 0 |
| NULL customer ids | under 1% | 0.4% |
Row count and NULLs pass; the date range fails. Everything downstream of that extract is wrong, and the failure is invisible in any chart: a second-half decline that is just missing rows.
The script that produces those numbers:
import pandas as pd
df = pd.read_csv("orders_2024.csv", parse_dates=["order_date"])
print(len(df)) # 412880
print(df["order_date"].min(), df["order_date"].max()) # 2024-01-01 2024-06-30
print(df["order_id"].duplicated().sum()) # 0
print(round(df["customer_id"].isna().mean() * 100, 2)) # 0.4
Alternatives, and when each wins
- Hand-rolled script (above). ~30 lines, lives in the repo, runs in seconds. Right when you own the dataset and control the refresh.
- A validation framework: Great Expectations, dbt tests, Soda. Worth the maintenance when many consumers depend on the table, when another team owns the pipeline, or when a bad load is expensive to reverse (billing, regulatory reporting). The cost is real: a suite is another thing to maintain, and it will page you at 3am.
- Skipping checks. Fine for a one-off exploratory pull you'll throw away, where a wrong answer costs an afternoon. Not fine the moment the number leaves your laptop.
The deciding criterion is blast radius × refresh frequency. Recurring table, many consumers → framework. One-off, you're the only consumer → script. Never skip row count and date range; those two take 30 seconds and catch the most common failure.
Failure modes and how they show up
- Silent truncation: the June 30 case. Detected by reconciling max date and row count against the source.
- Timezone shift: timestamps stored UTC, compared against local expectations. Detected by checking min/max against a known event.
- Sentinel NULLs:
-1customer ids that surviveisna(). Detected by value counts on the low end of every numeric column. - Join fan-out: a non-unique dimension key multiplies rows. Detected by comparing row count before and after the join.
- Schema drift on refresh: a renamed or retyped column. Detected by asserting the column list and dtypes, not just the values.
Reconcile counts against the source system, and alert when row count lands outside a band you set from history. That single alert catches truncation, fan-out and most schema drift before anyone builds on the data.
Curated: · Written: · Reviewed:
Q&A-78How do you find and handle duplicate customer records?
Treat this as record linkage with an explicit, versioned matching rule, not a dedupe script you run once. State two assumptions up front: you have at least one strong identifier (email or phone) for most records, and a false merge costs more than a missed duplicate. If the second is false, a marketing list where a missed duplicate is just an extra email, you can be far more aggressive.
The pipeline is normalize → block → score → threshold → merge with survivorship and an audit trail.
Normalization first, because it decides what "duplicate" means. Lowercase and trim email, strip plus-tags and Gmail dots (a policy call: they route mail, they don't identify a person), parse phone to E.164 with libphonenumber, uppercase and trim postal codes. This is not cosmetic: it collapses the cheap duplicates before you pay for fuzzy matching.
| Strategy | Catches | Misses | Typical precision | Use when |
|---|---|---|---|---|
| Exact on raw email | byte-identical rows | case, whitespace, plus-tags | ~100% | cheap first pass |
| Normalized key (email or phone) | case, whitespace, plus-tags, E.164 | changed email, typos | high | default first pass |
| Deterministic multi-field (normalized email OR phone OR name+dob+zip) | changed email if phone or name is stable | shared landline, common names | high | you have a second strong identifier |
| Fuzzy (Jaro-Winkler ≥ 0.9 on name, edit distance on address) | typos, transpositions | nicknames, renames | medium | review queue, not auto-merge |
| Probabilistic (Fellegi-Sunter, e.g. Splink) | weighted evidence across many fields | needs labelled pairs | tunable | 10^6+ records and you can label a sample |
When strict beats fuzzy. If a false merge corrupts billing, consent, or a clinical record, a strict rule on normalized email + phone beats a fuzzy name match every time: "Jon Smith" and "John Smith" in the same zip code are different people often enough to matter. Fuzzy matching earns its place only when you have no strong identifier and the volume makes manual review impossible; even then, auto-merge the top band and queue the middle band for a human.
Blocking, or you will not finish. One million records is 5×10^11 pairwise comparisons. Block on normalized email domain + last-name soundex + ZIP3; blocks of ~30 records give ~1.5×10^7 comparisons, roughly four orders of magnitude less.
Verify with a sample, and report both numbers. Pull 400 pairs the rule matched and 400 it did not. If 380 of the matched pairs are truly the same person, precision is 95%. If 12 of the unmatched pairs are also true duplicates, recall is 380/392 ≈ 97%. Precision is the one that decides whether you can auto-merge; recall tells you how much you are leaving on the table.
Merge without destroying. Keep a canonical customer id, link the source rows to it, and pick each field by source priority: billing address from the billing system, email from the most recently verified record. Write an audit row per merge so unmerge is possible.
Failure modes worth alerting on:
- Merge rate per rule version spikes: usually a rule that over-matches, e.g. phone alone merging everyone on a shared landline.
- Conflicting consent state on merge: record A opted out, record B opted in. Block the merge rather than silently keeping B.
- Duplicate rate per import batch rises: ingest has no canonical id, so re-imports recreate what you just merged.
Version the rule, sample-verify each version, and make every merge reversible.
Curated: · Written: · Reviewed:
Q&A-77Fifteen percent of survey responses have no income field. What do you do?
What I'd say first
Before touching the data: what is income for? If it's a covariate in a model, 15% missing is a nuisance. If it's the outcome or the segmentation variable, it's the whole question. And "missing" has three different meanings: the question wasn't asked (a survey version change), the respondent refused, or the value failed validation. Those have different fixes, so I'd get the field's provenance from whoever owns the instrument.
Then the decision hinges on one test: is missingness related to the outcome?
The test
Add a boolean income_missing and compare it against everything else. On an illustrative 4,000-response survey:
import pandas as pd # pandas 2.2
df = pd.read_parquet("survey.parquet")
df["income_missing"] = df["income"].isna()
print(df.groupby("income_missing")["basket"].agg(["mean", "size"]))
print(df.groupby("income_missing")[["age", "tenure_months"]].mean())
| income_missing | n | mean basket |
|---|---|---|
| False | 3,400 | $48 |
| True | 600 | $91 |
If the two rows look alike on the outcome and on the covariates, missingness looks independent of what you observed (MCAR) and complete-case analysis is unbiased. You lose 15% of the sample, which widens a confidence interval by about 8% (1/√0.85 ≈ 1.08), and that's the whole cost. If missingness tracks an observed covariate (income missing more often for newer accounts, say) that's MAR, and complete-case is still unbiased provided that covariate is in the model.
Here the rows don't look alike. Dropping the 600 gives a mean of $48; the full-sample mean is 0.85×48 + 0.15×91 = $54.45. Complete-case understates it by 11.8%. The missing group isn't a random subset, it's a different population.
What to do about it
Three options, in order of preference:
- Report the missing group as its own category. For descriptive work (segment sizes, basket averages) "income not given" is a legitimate segment and the table above is the result. No imputation, no assumption.
- Model with the indicator. Keep
income_missingas a feature alongside income. The model learns the missing group's behaviour instead of you guessing it. - Impute, with a stated method and a sensitivity check. Multiple imputation (MICE) beats mean imputation, which collapses every imputed value to a point, shrinking variance and attenuating any correlation with income toward zero. Fit the imputer inside the training fold only: fitting on the full dataset leaks test rows into training. Then rerun the headline number on imputed and complete-case data and report both; if the conclusion flips, the conclusion was never about income.
Failure modes to name
- Sentinel values:
-1,0,999999stored as real income. Checkdf["income"].value_counts().head()before anything else. - Refusal isn't random. High earners refuse more, so MNAR bias runs in a predictable direction: the observed mean is too low.
- Survey version changes. If the field was added in wave 3, missingness is a function of wave, and conditioning on wave makes it MAR. Check missingness by collection date.
What I'd tell the stakeholder
"15% of responses have no income. Those respondents spend about twice as much as the rest, so any analysis that drops them understates the average by roughly 12%. I'll report them as a separate segment and flag any model that uses income."
Curated: · Written: · Reviewed:
Q&A-39You find a few extreme values in your data. How do you decide what to do with them?
Core answer
Classify before you act. For each extreme, answer two questions: is this an error or a real observation, and does the decision change the answer to the question being asked? Errors get fixed or excluded under a written rule. Real extremes stay in, and you report the result both with and without them. You never drop a point because it makes the chart ugly.
Why it matters: the leverage
Extremes dominate any statistic built on squared deviation. Take 100 orders: 99 at $500 and one at $1,000,000.
- Mean = (99 × 500 + 1,000,000) / 100 = $10,495
- Median = $500
The single record moves the mean by 21×. Drop it and the mean falls to $500: a 95% change. So "average order value" is a different number depending on a decision about one row. That is why the decision has to be explicit and reported, not silent.
The procedure
- Trace each extreme to its source record. Join back to the raw event, check units, timestamps, and any test or internal flag.
- Classify the cause.
- Act by cause, and write the rule down.
| Record | Value | Cause | Action |
|---|---|---|---|
| order 88 | $1,000,000 | test order, internal account | exclude; filter at ingestion |
| order 412 | $98,000 | genuine enterprise deal | keep; report separately |
| order 97 | $5,400 | amount stored in cents | fix to $54 at source |
Only one of the three is a real extreme. Two are defects, and both should be fixed upstream rather than filtered downstream: a filter hides a bug that will recur.
Failure modes
- Unit error left in place: a cents/dollars mix inflates a metric 100×. Detect it by comparing the max against the 99th percentile; a 100× gap is a unit bug, not a whale.
- Percentile capping as cleanup. A worked example: capping revenue at the 99th percentile to tidy a chart removed the 12 largest accounts, which were 31% of revenue. The chart looked better and the number was wrong.
- Winsorizing without documenting it changes the denominator, so downstream totals no longer reconcile.
- Test traffic not filtered at ingestion shows up as a spike every deploy.
Detection that works: row count and sum before and after every step, and an alert when the daily max jumps more than 10× day over day.
When to keep, when to transform
If the question is total revenue, the extreme is the answer: keep it. If it is typical order size, use the median and say so. For a model, the loss function decides: squared loss is dominated by extremes, so either use a robust loss (Huber) or keep the point and accept the fit. Removing it to make the model behave is a modelling choice, not data cleaning, and belongs in the write-up.
What I would show
The list of removed records, each one's share of the total, and the reason. Explain an outlier before you remove it.
Curated: · Written: · Reviewed:
Q&A-38When is the mean the wrong summary of a dataset?
The mean is the wrong summary when the quantity is skewed or heavy-tailed, when the distribution has more than one mode, or when the question is "what does a typical case look like" rather than "what is the total". The mean answers the total question; the median answers the typical-case question. Pick by which question you are actually asking, not by habit.
Why skew breaks it. The mean is sum/n, and it is the point that minimizes the sum of squared deviations. Squared error means one value far from the center moves the mean quadratically. The median minimizes absolute deviations, so an extreme value moves it by one rank position at most. Take 100 sessions: 98 last 3 minutes, 2 are left open overnight at 550 minutes.
mean = (98 x 3 + 2 x 550) / 100 = (294 + 1100) / 100 = 13.94 min
median = average of the 50th and 51st sorted values = 3 min
The mean is 4.6x the median, and no session in the sample is anywhere near 14 minutes. That is the signature: a large mean/median gap on a positive-skewed quantity.
| Statistic | Session length |
|---|---|
| mean | 13.9 min |
| median | 3 min |
| p90 | 11 min |
| sessions over 2 h | 2% |
A content plan sized for 14-minute visits is wrong for 90% of sessions.
Bimodal is worse than skewed. If 50% of requests take 10 ms and 50% take 500 ms, the mean is 255 ms and no request is anywhere near it. The median is no better here: any value between 10 and 500 is a valid median, so it lands in the empty gap too. Neither summary describes the data; report the modes and their weights instead.
When the mean is right. Totals: total revenue equals mean x count, so the mean is the only summary that multiplies back to the total. Symmetric, light-tailed data: mean and median agree and the mean uses every value. Expected load and capacity planning are means by definition. The mean is also the sufficient statistic for the normal and exponential families, so for those it loses nothing.
Failure modes to check for.
- Mean of means: averaging per-region means unweighted. Region A has 100 users with mean 10; region B has 10 users with mean 100. Unweighted mean is 55; the true mean is (1000 + 1000) / 110 = 18.2. Always weight by count.
- Ratios: the mean of per-user conversion rates is not the overall conversion rate. Sum numerators and denominators, then divide.
- Missing tail: if timeouts are dropped or sessions are capped at 30 minutes, the mean is biased low while the median may be unaffected.
- Aggregation: a mean that holds in every subgroup can reverse in the whole (Simpson's paradox) when group sizes differ.
Detection. Plot the histogram before quoting a number. Report mean, median and p90 together; a mean/median ratio above roughly 1.5 on a positive quantity means the mean is describing the tail, not the population. Count the tail events explicitly: "2% of sessions over 2 hours" is the fact that decides it.
Curated: · Written: · Reviewed:
Q&A-12Daily revenue in your report disagrees with finance by a few percent every day. What would you check first?
What I'd say first
Two clarifying questions before touching SQL: which number is authoritative (usually the finance ledger, closed at a specific cutoff), and is the gap a roughly constant percentage or does it move with volume? Then the first check: pull one day at order level from both sides and diff on order id, not on totals. That single diff sorts the cause into one of three families before you spend time on any hypothesis:
- Rows present on your side but not finance's, or vice versa → ingestion/completeness.
- Same rows, different amounts → definitional (gross vs net, tax, discount, FX).
- Same rows, different day → boundary.
The leading hypothesis, and the one I'd test first, is the day boundary: recompute that day with the day cut in the business timezone and see whether the gap closes. It's one query, and it's the most common cause of a small, persistent daily gap.
Why boundary first: a timezone bug produces a gap that is the difference between two tails. Last night's late orders land in today, tonight's land in tomorrow. On flat volume those nearly cancel, so you see a few percent, not a clean offset. That signature (small, persistent, sign flipping on low-volume days) is what separates it from the alternatives.
Alternatives and their signatures
| Hypothesis | Signature | First check |
|---|---|---|
| Boundary / timezone | gap ≈ difference of two tails; flips sign on DST and low-volume days | recompute with business tz |
| Gross vs net | gap scales with discount/fee volume, ~constant % | compare a day with no discounts |
| Refund / chargeback timing | gap spikes after refund-heavy days, lags 1–3 days | join refunds on refund date vs order date |
| FX conversion | gap moves with the rate, larger on volatile days | recompute at finance's rate and rate date |
| Partial ingestion | gap is missing rows, not shifted rows | count orders per day on both sides |
Worked example (hypothetical)
A New York business, 1,000 orders/day, $50,000/day, flat. Orders placed 20:00–24:00 ET are 8% of daily revenue. Truncating UTC timestamps puts those orders (00:00–04:00 UTC) on the next day.
| Order time UTC | Eastern | UTC date | Business date |
|---|---|---|---|
| 2026-09-02 01:30 | Sep 1 21:30 | Sep 2 | Sep 1 |
| 2026-09-02 14:00 | Sep 2 10:00 | Sep 2 | Sep 2 |
Report day = $50,000 − tonight's tail ($4,000) + last night's tail ($4,000) = $50,000. On flat volume the gap vanishes, which is exactly why you must check a day where volume moved. If yesterday was $40,000 and today $50,000, the report shows $50,000 − $4,000 + $3,200 = $49,200, a 1.6% gap. Growing volume makes the report understate; shrinking makes it overstate. That sign flip is the fingerprint.
Failure modes and detection
- DST: the boundary shifts by an hour twice a year; a fixed UTC offset breaks. Detect by reconciling the two transition days.
- Late-arriving events: if the pipeline ingests by event time but the report runs before the day closes, the tail is missing. Detect by re-running yesterday's report today.
- Refunds booked on refund date: finance may net them on the order date. Detect by joining refunds both ways.
- Duplicate events from retries: inflates your side. Detect by counting distinct order ids.
When it's not the boundary
If the order-level diff shows identical rows and identical amounts on the same day, stop looking at timezones: the gap is definitional, and the fix is a metric definition, not a query. Write the chosen definition (business timezone, gross or net, refund treatment, FX rate source and date) into the metric so the next person doesn't re-litigate it.
Curated: · Written: · Reviewed:
Q&A-79Your revenue figure differs from finance's by 4 percent. How do you resolve it?
First I'd pin down what each number actually is, because "revenue" is the disagreement more often than the arithmetic is. I'd ask finance for their definition and, if it exists, the query or report behind their figure. Then I'd reconcile on a slice small enough to check by hand, one day or one month, and quantify each difference until the gap is fully explained. A 4% gap is rarely a bug in the sum; it's a definition, a cutoff, or a scope difference.
Three ways to attack it, and when each wins
- Diff the two queries. Get finance's SQL and put it next to yours. Fastest when the gap is definitional (gross vs. net, tax in or out, a region excluded). Usually resolves in an hour.
- Rebuild their number yourself from the warehouse. Use this when finance can't hand you the query, or when you suspect the pipeline rather than the definition: late-arriving events, duplicate order IDs from retries, test accounts leaking into one table.
- Row-level reconciliation. Join the two datasets on the grain (order ID, day) and look at rows that appear on one side only. This is the expensive one; reach for it only after the first two fail to close the gap.
When a bridge is overkill. If the difference is a single known definitional delta that is stable month over month and below your materiality threshold, write the definition down once and stop. A bridge earns its cost when the gap is new, moves, or crosses a threshold someone acts on. Spending a week reconciling a stable 0.2% that nobody's decision depends on is the wrong trade.
Failure modes to check, in order: order date vs. payment date cutoffs; refunds netted in one report and not the other; sales tax or currency conversion (and which day's rate); duplicate events from retries; and scope: a region, channel, or test account present in one pipeline only. Each is detectable by the same move: isolate the slice where the two numbers diverge and see which of these explains it.
A bridge from analytics to finance, on a month I can verify by hand:
| Step | Amount |
|---|---|
| analytics gross revenue | $1,000,000 |
| minus refunds | -$34,000 |
| minus sales tax | -$6,000 |
| finance net revenue | $960,000 |
That's a 4% gap, fully explained by two lines. To find which rows disagree before I build the bridge, I'd run a full outer join on the grain:
-- checked against PostgreSQL 16
SELECT coalesce(a.day, f.day) AS day,
a.analytics_gross,
f.finance_net,
a.analytics_gross - f.finance_net AS gap
FROM analytics_daily a
FULL OUTER JOIN finance_daily f USING (day)
WHERE a.analytics_gross IS DISTINCT FROM f.finance_net
ORDER BY day;
Rows that show up here are the ones to explain; rows that don't are already agreed. I'd hand finance the bridge and the query, not just the corrected number, so the next month reconciles in minutes instead of three weeks.
Curated: · Written: · Reviewed:
Q&A-24Yesterday's numbers always look low in the morning report. Why, and what do you do?
The 60-second answer. The newest period in the report is incomplete, not low. Events carry an event time (when the order happened) and an arrival time (when the warehouse saw it), and the gap between them has a long tail: offline POS terminals, batch uploads, payment webhooks that retry, mobile clients that flush on next launch. Yesterday's partition is still filling while last week's is closed, so a raw comparison reads fill level as a trend.
Two things fix it. Compare like with like: yesterday at 08:00 against the trailing 28 days at 08:00, not against their final totals. And mark the window: anything inside the lag horizon is provisional in the report, with the horizon derived from the data rather than guessed.
Worked example. Yesterday's orders read 78,000 against a 100,000 daily baseline, a 22% drop, enough to pause a campaign. But the trailing 28 days average 78% complete at 8 hours of age. Expected final = 78,000 / 0.78 = 100,000. The drop is exactly the lag; there is no signal in it. The same-age comparison agrees: the 28-day median at 8h is 77,500 and the 5th percentile is 74,000, so 78,000 is unremarkable.
Measure the curve, don't assume it. For each event_date, count rows at fixed ages and divide by the count at 7 days:
| Age at measurement | Share of final count |
|---|---|
| 8 hours | 78% |
| 1 day | 94% |
| 3 days | 99.5% |
| 7 days | 100% (by definition) |
Postgres, age measured from the start of the event day:
WITH final AS (
SELECT event_date, count(*) AS n_final
FROM orders
WHERE event_date < current_date - 7
GROUP BY event_date
), at_age AS (
SELECT event_date, count(*) AS n_8h
FROM orders
WHERE ingested_at < event_date + interval '8 hours'
GROUP BY event_date
)
SELECT a.event_date,
round(a.n_8h::numeric / f.n_final, 4) AS completeness_8h
FROM at_age a JOIN final f USING (event_date)
ORDER BY a.event_date;
Run it per age bucket. The output is the table above, per day, so you can see whether the curve is stable or drifting.
What to do with it. Two policies, chosen by decision latency. If the decision can wait, gate publication: hold the day until completeness crosses 99%, then publish with a restatement log. If it can't, publish provisional numbers with the incomplete window shaded and the same-age comparison as the headline. A provisional number should not drive an automated action: the campaign pause is that failure mode.
Failure modes and how to catch them.
- A real drop hidden behind "it's just late data". Distinguish by shape: lag shows a deficit concentrated in the newest hours that closes over days; a real drop shows the same-age count below its historical band and stays there. Alert on the completeness ratio and on same-age count against the trailing p5, not on the raw day-over-day delta.
- Negative arrivals. Refunds, chargebacks and cancellations land late and shrink an already-closed day. Track revisions to published days; a day that only ever goes down is a different bug from one that only goes up.
- Day-boundary bugs. Event time in local time, partition keyed on UTC. A 5-hour offset moves a whole evening's orders into the next day and looks like a 20% swing. Check by bucketing the same events both ways.
Curated: · Written: · Reviewed:
Q&A-94How do you catch a wrong number before you send it?
Assume I own the number: a metric on a dashboard, a figure in a board deck, a row count in a migration report. My default is three checks, cheapest first: an expectation I write down before running anything, invariants the query enforces on itself, and a reconciliation against a system of record. Which of the three I lean on depends on what the number is worth and who else will read it.
Most wrong numbers come from a short list: join fan-out, double counting across two sources, a unit or currency mismatch, a date boundary in the wrong timezone, a filter that silently dropped rows, NULLs collapsing a count, or a stale cache. Each leaves a signature, so the check is usually about matching the result to the signature rather than eyeballing it.
A worked trace. Orders has 12,000 rows, one per order, average order value $85, so I expect roughly $1.02M of revenue. I join orders to order_items on order_id and sum the order amount. The query returns $3,060,000: exactly 3.0× my expectation. That ratio is the fan-out signature: order_items holds 3 rows per order, so the join tripled every order. The fix is to aggregate order_items to one row per order before joining, or to sum the item amounts instead of the order amount. A 3.0× multiple is not a rounding problem; it is a grain problem.
| Check | Cost | Catches | Misses |
|---|---|---|---|
| Expectation before running | minutes | order-of-magnitude and unit errors | 2× errors, small drift |
| Query invariants (unique key, not-null, parts sum to whole) | build time | fan-out, dropped filters | bad source data |
| Reconciliation vs system of record | hours, needs an owner | small drift, definition mismatch | shared bugs, stale benchmark |
That table is also the trade-off. A plausibility check is cheap and catches the errors that careful code review misses, but it will not catch a 2× error: 2× is plausible. Automated tests catch structural errors at build time and keep catching them, but they only test the invariants you thought to write. Reconciliation catches the small drift the other two miss, but it needs a trusted source and someone who owns it, and it costs hours. For a one-off number in a deck, the expectation plus a spot check is enough. For a metric that ships on a dashboard, the invariants belong in the pipeline as tests, because the next person to change the join will not re-derive the expectation.
Three failure modes I watch for. The benchmark itself is wrong: a finance total that is a month stale will flag a correct number as broken, so I date the benchmark. Both sides share the bug, if the reconciliation query uses the same join as the pipeline, it agrees with the pipeline and proves nothing; the two paths must be independent. And the tolerance is too loose: "within 10%" hides a 9% error that is real.
When a check fails, I do not adjust the number. I go to the row level and find which rows the join duplicated, or which filter dropped them, and fix the query. Then I state the number with its definition and its as-of date, so the next person can check it the same way.
Curated: · Written: · Reviewed:
Q&A-26A manager asks you to pull the number of active customers. What do you do before writing SQL?
Before writing SQL, pin down five things: the decision the number feeds, what counts as a customer, what counts as active, over what window, and who is excluded.
Start with the decision. "Active customers" for a board slide, for churn triage, and for a pricing review are three different queries. A board number wants a stable definition that won't be restated next quarter. Churn triage wants a short window that moves. Pricing wants paying accounts only.
Then the four parameters:
- Entity. In B2B a customer is usually an account, not a user or a seat. A 400-seat account that logs in from 12 users is one customer. Confirm which one the requester means.
- Qualifying event. Login, purchase, active subscription, or any tracked product event. These are not interchangeable: a churned account can still log in.
- Window. Point-in-time ("as of today") or trailing ("last 90 days"). Also rolling versus calendar-aligned: a rolling 30-day number cannot be reconstructed from month-end snapshots.
- Exclusions. Internal and test accounts, trials, free tier, and accounts inside a cancellation grace period.
Then check whether the definition already exists. Most warehouses have a metrics layer, a dbt model, or a dashboard that already answers this. Finding it first is cheaper than shipping a fourth number.
The reason to settle this before SQL: the same population splits three ways depending on the definition. Illustrative figures for a 200,000-account B2B SaaS:
| Definition | Window | Count |
|---|---|---|
| logged in | trailing 90 days | 131,000 |
| placed an order | trailing 90 days | 74,000 |
| active paid subscription | as of today | 48,000 |
A 2.7x spread between the loosest and strictest reading of the same phrase. If two teams pick different rows and both report to the same meeting, the meeting argues about the number instead of the decision.
Failure modes to name when you hand the definition back:
- Definition drift. Someone edits a dashboard filter and the number moves with no code change. Version the definition next to the model.
- Snapshot versus period. "Active customers" as a month-end snapshot and as a monthly average differ, and the average cannot be recovered from snapshots alone.
- Timezone. Day boundaries in UTC versus the customer's local time shift counts by roughly one day of activity.
- Late-arriving events. Yesterday's number changes tomorrow as events land; state the data lag.
- Trials. Including trials can inflate the count by the whole trial population, often the largest single swing.
Cheap check: reconcile your count against the billing system's count of accounts with an active subscription. If the gap is larger than the trial and grace-period population, the definition is wrong somewhere.
Trade-off: a strict definition (paid, active in 30 days) is stable and defensible but lags reality by up to a month; a loose one (any login in 90 days) moves fast but counts churned users. Match it to how often the decision is made.
Write the agreed definition, window, exclusions and source table into the request, confirm it with the requester, and only then write the query.
Curated: · Written: · Reviewed:
Q&A-27What makes a metric definition complete enough to hand to another analyst?
My starting point for defining a metric precisely is the row count before and after every step.
A complete definition states the numerator, the denominator, the unit being counted, the time window, the time zone, the filters and exclusions, and the source table. Two analysts given it should produce the same number to the unit.
Concretely, write the definition as a short spec plus a reference query, include one worked example with real counts, and version it so a changed definition is visible in the history.
The reason for that specificity is a failure I have seen: Conversion rate was defined only as purchases over visitors, and one analyst excluded internal traffic while another did not, so the same week was reported as 2.9 and 3.4 percent.
A definition someone else can rerun.
| Element | Example |
|---|---|
| numerator | distinct users with a paid order |
| denominator | distinct non-internal visitors |
| window | calendar week, US Eastern |
| source | analytics.events v3 |
| last week | 3,120 / 104,000 = 3.0% |
I would not consider it settled without evidence: Have a second analyst reproduce the number from the written definition alone and resolve any difference.
If two people cannot reproduce it, it is not defined yet.
Curated: · Written: · Reviewed:
Q&A-29Conversion went from 4 percent to 5 percent. How would you describe the change?
I would scope percentage change versus percentage points with the stakeholder before writing a single query.
The rate rose by 1 percentage point, which is a 25 percent relative increase. Reporting 1 percent or 25 percent without saying which kind of change is meant can mislead a reader by a factor of 25.
Concretely, use percentage points for differences between rates and percent for relative change, and state both with the base value when the audience will make a decision on it.
The reason for that specificity is a failure I have seen: A launch email said conversion improved by 25 percent, leadership assumed conversion was now around 29 percent, and the forecast built on that reading overstated revenue by 3.8 million dollars.
One change, two ways to state it.
| Measure | Value |
|---|---|
| before | 4.0% |
| after | 5.0% |
| absolute change | +1.0 percentage point |
| relative change | +25% |
I would not consider it settled without evidence: Write both the absolute and relative change with the starting value in any summary that goes to decision makers.
Name the kind of percentage every time.
Curated: · Written: · Reviewed:
Q&A-31You have conversion rates for 50 stores. Should you average them to get the company rate?
I would test weighted versus unweighted averages of rates on a ten-row example I can verify by hand.
A simple average of store rates gives every store equal weight, so a store with 20 visitors counts as much as one with 20,000. The company rate is total conversions divided by total visitors, which weights each store by its traffic.
Concretely, sum numerators and denominators across stores and divide once, and use the unweighted average only when the question is explicitly about the typical store.
The reason for that specificity is a failure I have seen: An operations review averaged 50 store rates to 9.8 percent, but three tiny stores with rates above 40 percent inflated it, and the traffic-weighted company rate was 6.1 percent.
Two stores, two very different weights.
| Store | Visitors | Conversions | Rate |
|---|---|---|---|
| large | 20,000 | 1,200 | 6.0% |
| small | 20 | 9 | 45.0% |
| weighted total | 20,020 | 1,209 | 6.04% |
| simple average | none | none | 25.5% |
I would not consider it settled without evidence: Compute both the weighted and unweighted averages and state which question each one answers.
The company rate is a ratio of totals.
Curated: · Written: · Reviewed:
Q&A-30Conversion improved in every region but fell overall. How is that possible?
The honest answer on Simpson's paradox and mix shift begins with what the data cannot tell us.
An overall rate is a weighted average of segment rates, and if traffic shifts toward a segment with a lower rate, the total can fall while every segment improves. This is Simpson's paradox, and it is caused by mix, not performance.
Concretely, decompose the overall change into a rate effect within segments and a mix effect from shifting volume, and report both so the audience sees which one moved.
The reason for that specificity is a failure I have seen: A marketing team was blamed for falling conversion when both regions had improved, because a new market with a 1 percent rate grew from 10 to 50 percent of traffic.
Both segments up, total down.
| Segment | Share before | Rate before | Share after | Rate after |
|---|---|---|---|---|
| Core | 90% | 5.0% | 50% | 5.5% |
| New market | 10% | 1.0% | 50% | 1.2% |
| total | 100% | 4.6% | 100% | 3.35% |
I would not consider it settled without evidence: Compute the overall rate with last period's segment mix and this period's segment rates to isolate the rate effect.
When segments and totals disagree, look at the mix.
Curated: · Written: · Reviewed:
Q&A-102Two versions of a pricing page each show 5 percent conversion overall, but conversion is higher for B in both mobile and desktop segments. How is this possible and what do you do?
This is Simpson's paradox: an overall rate is a weighted average of segment rates, and the weights differ between arms. B wins inside both segments and still ties overall because A drew the traffic mix that converts better.
Hypothetical numbers, 100,000 sessions per arm:
| Segment | A sessions | A CVR | B sessions | B CVR |
|---|---|---|---|---|
| Desktop | 80,000 | 5.50% (4,400) | 20,000 | 6.00% (1,200) |
| Mobile | 20,000 | 3.00% (600) | 80,000 | 4.75% (3,800) |
| Overall | 100,000 | 5.00% (5,000) | 100,000 | 5.00% (5,000) |
Desktop converts well above mobile in both arms (5.5% vs 3.0% for A, 6.0% vs 4.75% for B), so the mix difference swamps the within-segment lift. The same mechanism runs the other way too: a lift overall with losses in every segment.
First move is not to report either number, but to ask why the device mix differs at all. In a randomized test it shouldn't. Device is fixed before assignment, so the expected desktop share is identical in both arms; an 80/20 vs 20/80 split is not sampling noise, it is broken assignment. Common causes: a redirect or JS that fails on some mobile browsers so those users fall back to A; bot filtering applied to one arm; a CDN or cache serving one variant more to desktop; or randomizing at a layer that is not the unit you are counting. Run a sample ratio mismatch check on the per-segment counts (chi-square, flag at p < 0.001) and pull assignment logs before touching conversion.
Postgres 16:
select variant, device,
count(*) as sessions,
avg(converted::int) as cvr
from experiment_sessions
where experiment = 'pricing_page_v2'
group by 1, 2;
If the mix difference is real and assignment is intact, which is what you get when the "test" is observational, two page versions live in different periods or geos: device is a confounder and the aggregate comparison is meaningless. The stratified read is the honest one: B is better in both segments, so B is better, weighted by the traffic mix you expect going forward. Compute it as a weighted average with a common mix, or fit conversion ~ variant + device and read the variant coefficient.
If assignment is broken, neither number is trustworthy, including the segment-level ones, because the arms differ on more than device. Rerun.
Two traps. Segmenting on something measured after the variant was shown (pages viewed, time on page) creates a collider and can manufacture the reversal; device is safe because it is pre-treatment. And hunting segments until one reverses is multiple comparisons: with 20 segments you will find a reversal by chance, so pre-register the segments that matter.
Decision: do not ship on the aggregate tie, and do not ship on the segment win until assignment is verified. If the test is clean, ship B and expect a small lift: 0.5 points on desktop, 1.75 on mobile here, not the zero the headline shows.
Curated: · Written: · Reviewed: —
Q&A-35What is a north star metric, and why do you need guardrails with it?
What I'd say first
A north star metric is the single number that stands in for the value a customer gets from the product: weekly orders delivered, not weekly orders placed. It exists to align teams on one outcome instead of a dozen local ones.
Guardrails exist because any single number can be moved without delivering that value. They are the metrics that must not degrade while you push the north star: refund rate, p99 checkout latency, unsubscribe rate, support contacts per 1,000 orders.
The trade-off is the whole answer. Guardrails are cheap to add and expensive to over-add, because each one is a constraint that can block a genuinely good change. The number you carry is a budget, not a checklist.
Choosing them
Derive guardrails from the specific way your north star can be gamed, not from a standard list. Ask: if a team were told "move this number and nothing else matters," what is the cheapest dishonest way? For weekly orders delivered, it is discounting into unprofitable orders, shipping late, and counting orders that later get refunded. Those three produce refund rate, margin per order, and on-time delivery. A guardrail with no such story behind it is decoration.
When it is a component, not a guardrail
If you would be happy for the metric to move (you want fewer refunds, not merely "not more") it belongs inside the north star, not beside it. Two ways to do that: a composite (orders delivered minus refunded orders, weighted), or a paired metric where both must improve. Guardrails are for things you are willing to hold flat, not things you are trying to improve. Teams get this wrong often: activation rate goes in the guardrail list, and then nobody can tell whether a flat activation rate is a pass or a missed opportunity.
The cost of too many
Each guardrail is a hypothesis test with its own false-positive rate. At a one-sided 5% threshold, four independent guardrails give you a 1 − 0.95⁴ ≈ 18.5% chance that at least one fires on a change that is actually fine. Ten gives you 40%. Teams that add ten guardrails do not get safer; they get a stop-the-line process everyone learns to route around. Two to four is the range where a breach still means something.
Failure modes worth naming: a guardrail that is a proxy for the north star, so the two move together and it never fires independently; a guardrail with no owner, so breaches get logged and ignored; a threshold set from a quiet week, which fires constantly during a launch.
Detection: track how often each guardrail fires and what happened next. One that fires in more than roughly 10% of experiments and ships anyway is mis-thresholded, or is not a guardrail.
Worked example (hypothetical figures)
| Metric | Before | After | Threshold |
|---|---|---|---|
| weekly orders delivered | 100,000 | 106,000 | north star |
| refund rate | 2% | 7% | max 3% |
| margin per order | $4.10 | −$0.60 | min $3.00 |
The north star is up 6%. Both guardrails breached. The change stops and the discount depth gets re-tuned, because the +6% was bought at a cost that lands in next quarter's numbers rather than this one's.
Report every experiment on the north star together with each guardrail and its threshold, so the decision is visible rather than argued.
Curated: · Written: · Reviewed:
Q&A-37What is a vanity metric, and how would you replace one?
The 60-second answer. A vanity metric is one that can only move one way and doesn't change a decision: cumulative signups, total downloads, page views since launch. The test isn't whether the number is big: it's what you would do differently if it halved. If the answer is nothing, it's vanity. Replace it with something that can fall: a rate or an active count over a fixed window (WAU, MAU, revenue retained), or a cohort retention curve. Then show both side by side, because the replacement can lie in its own way.
Which replacement, and when. The choice depends on the decision, not on taste.
| Replacement | Answers | Hides | Use when |
|---|---|---|---|
| Rate over a fixed window (WAU/MAU, D7 retention) | Is the current population engaged? | Composition: new users mask decay of old ones | You need one number on a dashboard or as a goal |
| Cohort retention curve (D1/D7/D30 by signup week) | Does the product hold users over time? | Scale; a beautiful curve on 200 users is not a business | You're deciding whether to spend more on acquisition |
| Absolute count (total signups, seats, GB stored) | How much capacity, cost or contract do we carry? | Nothing about health | Capacity planning, cost drivers, contractual or regulatory counts, and as the denominator of any rate |
Why cumulative hides decline. Aggregate MAU is new + retained − churned. If acquisition grows faster than churn, MAU can sit flat while every cohort decays underneath it. A cumulative count only ever adds, so it cannot show that. Illustrative figures:
| Quarter | Cumulative signups | Monthly active users |
|---|---|---|
| Q1 | 380,000 | 60,000 |
| Q2 | 440,000 | 52,000 |
| Q3 | 500,000 | 41,000 |
Cumulative signups rose 31.6% across two quarters while MAU fell 31.7%. The top line is the one that gets quoted; the bottom line is the one that's true.
When an absolute count is the right metric. Not every count is vanity. Total seats drives licence cost, total GB stored drives storage spend, total events drives partition count: those are capacity and cost questions where a rate tells you nothing. Absolute counts are also the denominator of every rate, so you keep them regardless. The vanity version is a count that only accumulates and is reported as if it measured health.
Failure modes, and how you catch them.
- Goodhart. Once a rate is the goal, teams redefine the numerator ("active" becomes one session a month) or shrink the denominator (drop churned accounts). Freeze the definition in writing, version it, and pair it with a second metric that moves the other way.
- Shrinking denominator. A rate can rise while the base collapses: 5,000 actives out of 41,000 is a better ratio than 6,000 out of 60,000. Always print the absolute next to the rate.
- Window shopping. D7, D30 and "monthly" chosen after the numbers are in. Pick the window before you look.
- Cohort curve read as aggregate. Flat MAU with decaying cohorts means you are buying growth, not keeping it.
Detection is the same in every case: show the cumulative or absolute line and the rate or cohort line together, and check whether they tell the same story. When they disagree, the one that can fall is the one to trust.
If it can only go up, it cannot warn you.
Curated: · Written: · Reviewed:
Q&A-36Revenue is reported monthly. What would you track weekly to see problems earlier?
Track five things weekly, all upstream of revenue: trial starts, activation rate, qualified pipeline created, stage conversion, and churn/expansion dollars. Revenue stays on its monthly close; these are the inputs that move first.
Assumptions: B2B SaaS, self-serve trial plus sales-assisted close, revenue closed on the 5th business day of the following month.
| Weekly metric | Definition | Lead over revenue | Why weekly |
|---|---|---|---|
| Trial starts | signups completing signup, bot-filtered | 8–12 weeks | high-variance volume; weekly smoothing |
| Activation rate | share of a week's trial cohort hitting the first value event (first integration connected) within 7 days | 6–10 weeks | onboarding regressions surface in days |
| Qualified pipeline created | $ of SQLs created that week | 1–2 quarters | demand-gen and rep capacity |
| Stage conversion | trial→paid and SQL→closed-won, by cohort week | 4–8 weeks | funnel leaks before they hit bookings |
| Churn and expansion $ | MRR lost and gained, booked that week | 0–12 months | fastest-moving revenue input |
Why weekly and not monthly: a problem that starts January 1 is invisible until the February 5 close. 36 days. Reading last week's cohort on Monday cuts detection to 7–14 days.
Illustrative arithmetic. 4,000 trials/week, 25% activate, 20% of activated trials convert at $200/month. That is 200 new paid accounts and $40,000 new MRR per week. A 12% relative drop in activation (1,000 activated down to 880) drops paid conversions to 176 and new MRR to $35,200. The gap is $4,800/week, roughly $19,000 over four weeks. Monthly reporting shows a soft month; weekly shows a step change in one cohort.
The weekly review is one page: the five numbers, each against last week and the same week last year, with a named owner per metric. Anything outside its band gets a one-line cause and a decision: fix, investigate, or accept.
Failure modes and how to catch them:
- Vanity metric with no causal link. Site visits looked fine for two quarters while a bot surge inflated them; activation was the real signal. Check each candidate against two or more years of history before trusting it.
- Weekly noise on small cohorts. At 4,000 trials/week the standard error on a 25% activation rate is about 0.7pp, so ±1.4pp at 2σ. Alert on two consecutive weeks outside the band, not one.
- Goodhart. Pipeline created gets gamed by pushing unqualified opportunities. Pair it with stage conversion: inflated pipeline shows up as falling conversion within two weeks.
- Seasonality. Compare against the same week last year, or use a 4-week trailing average.
- Correlation decay. A pricing or packaging change can break the link. Re-fit the correlation quarterly.
I would not track revenue itself weekly, the close makes it lumpy and restated, nor NPS, which moves too slowly to act on.
Curated: · Written: · Reviewed:
Q&A-47You need to forecast next quarter's orders. What would you do first?
What I'd do first
Before any model, pin down three things, because each one changes the answer:
- What "orders" counts. Bookings, units shipped, and revenue diverge the moment there's a backlog or cancellations. If the number feeds an inventory buy, I want units by SKU; if it feeds guidance, I want revenue.
- The horizon and granularity the decision needs. One number for the quarter, or monthly by region? A quarterly total is far easier to forecast than 200 SKUs, and the right method depends on which one is actually being asked for.
- Which error is expensive. Under-forecasting inventory means stockouts; over-forecasting means working capital sitting in a warehouse. That asymmetry decides whether I forecast the mean or a high quantile, and which loss I backtest on.
Then I'd look at the series before modelling it: how many quarters of history, whether there's a stable seasonal shape, and whether there are definitional breaks (a price change, a new channel, a one-off bulk order) that make older data a different process. Quarterly data usually gives you 8–20 points, which is not enough to fit anything with many parameters.
My first forecast would be a baseline: seasonal naive, next Q1 = last Q1, optionally scaled by trailing year-over-year growth. That's the bar. I'd backtest it with a rolling origin over the last 4–8 quarters and only reach for something more complex if it beats the baseline out of sample.
Worked backtest (illustrative numbers)
Eight quarters of orders, in units:
| Quarter | Orders |
|---|---|
| 2023 Q1 | 200 |
| 2023 Q2 | 240 |
| 2023 Q3 | 260 |
| 2023 Q4 | 320 |
| 2024 Q1 | 210 |
| 2024 Q2 | 252 |
| 2024 Q3 | 273 |
| 2024 Q4 | 336 |
Hold out 2024, forecast each quarter from 2023 only.
Seasonal naive: 2024 Qk = 2023 Qk. Errors |210−200|=10, |252−240|=12, |273−260|=13, |336−320|=16. MAE = 51/4 = 12.75 units; MAPE = 4.8% (each actual is 1.05× the prior year).
Naive last-value: forecast every 2024 quarter = 320 (2023 Q4). Errors 110, 68, 47, 16. MAE = 241/4 = 60.25 units; MAPE = 25.3%.
| Method | MAE (units) | MAPE |
|---|---|---|
| Naive (last value) | 60.3 | 25.3% |
| Seasonal naive | 12.8 | 4.8% |
Seasonality dominates: the last-value baseline is ~5× worse. Any model I bring has to beat 12.75 units MAE on this holdout, not just beat the last-value number.
Failure modes and how I'd catch them
- Leakage. If I set the 5% growth factor from the 2024 actuals, the backtest is optimistic. Growth must be estimated only from data available at the forecast origin.
- Definitional breaks. A mid-2023 price increase makes 2023 and 2024 quarters incomparable; seasonal naive then compares different processes. Detect by plotting the series for a level shift, and by asking sales whether anything changed.
- One-off orders. A single bulk order inflates a quarter's seasonal index. Look at the order-size distribution, not just the total, and flag quarters where one customer is >20% of volume.
- Short history. Eight quarters is two observations per season; a seasonal index from two points is noise. Widen the interval to match.
- Metric mismatch. MAPE explodes when actuals are near zero (a new SKU) and treats over- and under-forecast as equally bad. Use WAPE, or pinball loss at the quantile you actually care about.
Interval
With four backtest folds, the residual spread is a crude interval: ±2×MAE ≈ ±26 units, or the empirical 10th–90th percentile of the four errors. Four points is thin: I'd say so rather than present a tight interval as if it were earned.
What no time series sees
Before trusting any of this, I'd ask sales ops and finance what's in the pipeline: a signed contract, a promotion, a competitor's price move. A forecast that ignores a known Q1 launch is wrong regardless of its backtest score.
Curated: · Written: · Reviewed:
Q&A-48How would you decide whether today's number is unusual?
What I'd say first
"Unusual" only means something once I've fixed three things: the metric's expected value for today, its normal spread, and the cost of a false alarm. So I'd answer with a baseline conditioned on the calendar, not a fixed threshold.
Concretely: take the same weekday over the last 8–12 weeks, compute the median and the median absolute deviation (MAD), and flag today if it lands outside median ± 3 MAD. Median and MAD rather than mean and standard deviation, because one outage inside the window inflates the mean and stddev enough to hide the next one.
Worked example: Tuesday signups, last 8 Tuesdays:
1000, 1040, 980, 1010, 1060, 990, 1020, 1030
Sorted: 980, 990, 1000, 1010, 1020, 1030, 1040, 1060 → median 1015. Absolute deviations from 1015: 35, 25, 15, 5, 5, 15, 25, 45 → median 20, so MAD = 20. Band = 1015 ± 3×20 = 955 to 1075.
Today comes in at 900. That's 115 below the median, 5.75 MADs out: alert. A fixed "10% drop" rule would need 913.5 to fire, so it also catches this one; the difference shows up elsewhere. On a metric that swings ±15% week to week, 10% is noise and fires constantly. On a flat one, 10% is so wide that a real 5% drift runs for weeks before anyone sees it.
Trade-offs
- Same-weekday MAD: 8–12 weeks of history, explainable, nothing to train. Can't handle moving holidays, and a steady 2%/week decline stays inside the band until it's large.
- STL or Prophet decomposition: models weekly and yearly seasonality plus holiday regressors and gives a residual distribution. Needs more history, and a real level shift can get absorbed into the trend component: check the trend, not just the residual.
- Change-point detection (CUSUM on residuals): answers "did something change and stay changed", not "is today odd". Use it alongside, not instead.
Failure modes and how I'd catch them
- Partial day. At 10:00 today's count isn't comparable to full days. Compare cumulative-to-same-time-of-day, or evaluate after the day closes.
- Known events. Holidays, launches, marketing sends. Calendar regressors plus a suppression list.
- Metric definition change or tracking outage. The number moves because the pipeline moved. Check upstream event volume and the deploy log before paging anyone.
- Multiple comparisons. 50 metrics each with a 1% two-sided tail gives roughly 0.5 false alerts a day. Set per-metric thresholds from the backtest, or apply a false-discovery-rate correction.
- Alert fatigue. A rule that fires 23 times a quarter gets muted. Track alerts-per-quarter and precision per rule, not just recall.
How I'd validate it
Backtest over the last 2–4 quarters: replay the rule day by day, count alerts and how many were real incidents, and compare candidates on that table.
| Rule | Alerts/quarter | Real issues caught |
|---|---|---|
| fixed 10% drop | 23 | 1 of 2 |
| same weekday, 3 MAD | 3 | 2 of 2 |
Pick the operating point where precision is high enough that people act on the alert, then re-check monthly: the metric's variance changes, and the band has to move with it.
Curated: · Written: · Reviewed:
Q&A-28Daily orders dropped 15 percent yesterday. Walk me through how you would investigate.
A 15% drop is large enough that I'd treat it as real until I can show otherwise, but I wouldn't default to an hour of pipeline archaeology. The ordering depends on what else changed yesterday, and the first move is to find the cheapest test that separates "the number is wrong" from "the number is right and something happened."
Three explanations cover almost every case: the measurement broke, an external or calendar effect moved demand, or a real product or behaviour change landed. The cheapest discriminator is a second independent source. If the warehouse order count and the app's own order-event stream both fall 15%, the drop is real. If only one moves, it's instrumentation.
That's the trade-off on ordering. Data-first is right when the metric is derived (joins, dedup, attribution windows, a pipeline deployed this week) or when the drop is suspiciously round or hits a metric that shouldn't track behaviour. Segment-first is right when the metric is a direct count and the drop is uneven. A uniform drop across every segment points at a global cause: holiday, regional outage, a tracking SDK that stopped firing. A concentrated drop points at a segment-specific cause, and that's where the explanation lives.
Worked example. Total orders fell from 10,000 to 8,500, -15%.
| Segment | Last Tuesday | Yesterday | Change |
|---|---|---|---|
| iOS | 4,100 | 4,050 | -1% |
| Android | 2,700 | 1,560 | -42% |
| Web | 3,200 | 2,890 | -10% |
| Total | 10,000 | 8,500 | -15% |
Android alone accounts for 1,140 of the 1,500 missing orders: 76%. Web adds 310 and iOS 50, so this is an Android story. The question narrows from "why did orders drop" to "why did Android orders drop," which is a question one team can answer today.
Failure modes to check before committing to a story:
- Late-arriving data. Re-run the query after the load completes; if the gap closes, there was no drop. This is the most common cause of a false alarm.
- Definition change. An order counted at payment yesterday and at shipment today moves the number without moving behaviour. Check the metric definition's history.
- Timezone or calendar boundary. A UTC cutoff against a local-time business day shifts a few hours of orders across the boundary.
- Partial outage. A 40-minute checkout failure produces a concentrated drop with a clean start and end timestamp: look for the step function, not the slope.
When I'd change the ordering: if a deploy, a holiday, or a known outage is already on the calendar for yesterday, check that first. It's a five-minute test and it's often the answer. If the metric is new or its pipeline changed this week, data-first. If the metric is stable and the drop is concentrated, segment-first.
The deliverable is a segment plus a mechanism, with the arithmetic showing that segment accounts for the gap.
Curated: · Written: · Reviewed:
Q&A-43Revenue grew 10 percent. How would you explain what drove it?
I would test revenue decomposition on a ten-row example I can verify by hand.
Revenue equals customers times orders per customer times average order value, so a change can be split into those factors. Decomposing shows whether growth came from more buyers, more frequent buying, or higher prices or mix.
Concretely, compute each factor for both periods, attribute the change to each factor in sequence or with a log decomposition so the parts add up, and investigate the largest part further.
The reason for that specificity is a failure I have seen: A 10 percent revenue rise was credited to a loyalty program, but decomposition showed order frequency was flat and the growth came from a 9 percent price increase.
Where the 10 percent came from.
| Factor | Last year | This year | Change |
|---|---|---|---|
| customers | 20,000 | 20,200 | +1.0% |
| orders per customer | 5.0 | 5.0 | 0% |
| average order value | $50.00 | $54.46 | +8.9% |
| revenue | $5.00m | $5.50m | +10% |
I would not consider it settled without evidence: Show that the product of the factors reproduces revenue in both periods and that the parts sum to the total change.
Split the total before explaining it.
Curated: · Written: · Reviewed:
Q&A-95Your analysis shows a 60 percent jump in conversion. What do you do before celebrating?
What I'd say in the first two minutes
Don't announce it. Treat a 60% jump as a claim that has to survive one independent source before it goes anywhere, and put a clock on the check: usually the same business day, because these are cheap queries, not a research project.
The trade-off cuts both ways. Holding a genuine 60% lift costs money: a day of not scaling the winning variant, not shifting spend, not shipping the change to the rest of traffic. Announcing a false one costs more: credibility with the people who act on it, and a decision made on a number that was never real. So the deciding question isn't "is this suspicious?" but "what does the next action cost if I'm wrong?"
That gives the criteria:
- Reversible next step (extend the test, add spend to a channel you can pull back, brief the team running the experiment): partial confirmation is enough. One independent source moving in the same direction and roughly the same magnitude, and you act.
- Irreversible or expensive next step (ship to 100%, reallocate a budget, put the number in a board deck): wait for full confirmation. The independent source agrees in magnitude and the segment breakdown is consistent.
- Sources disagree in direction or magnitude: that isn't partial confirmation, it's a contradiction. Stop and find out which one is broken.
The check itself
Three things, in order of how often they turn out to be the cause:
- Any tracking, tag or code release on the jump date. A tag change that stops counting one browser shrinks the denominator and inflates the rate without the numerator moving at all.
- An independent source: payment records, the order system, a warehouse table built from a different pipeline.
- Segment breakdown. A real behaviour change usually shows up across segments; a broken instrument usually concentrates in one.
Why I'm specific about this
I've watched a 60% conversion jump get announced company-wide and traced a day later to a tag change that stopped counting visits from one browser. The numerator never moved.
| Source | Change |
|---|---|
| analytics conversion rate | +60% |
| payment system orders | +1% |
| analytics visits | −37% |
The rate is up because the denominator fell 37%. Orders are flat. That's a measurement change, not a behaviour change.
What I'd do while the check runs
Tell the people who need it now (the team running the test, whoever owns the tracking) without a company-wide announcement. That's the middle path: not celebrating, not sitting on it. If the check can't resolve it inside the time box, that's a finding too: the instrumentation isn't good enough to trust the number, so it gets shared with the caveat attached or not at all.
One more failure mode worth naming: if the 60% came from slicing segments until something looked good, that's multiple comparisons, and the jump is an artefact of the search. Check whether the segment was pre-registered.
Curated: · Written: · Reviewed:
Q&A-66What is the difference between the standard deviation and the standard error?
The 60-second answer. Standard deviation (SD) describes how far individual observations sit from their mean. Standard error (SE) describes how far a sample mean sits from the population mean it is estimating. Same units, different targets: SD is a property of the data, SE is a property of the estimate. They are linked by SE = s / √n, so SE shrinks as the sample grows while the SD does not.
Why the √n. Average n independent observations and the variances add: Var(x̄) = σ²/n, so SD(x̄) = σ/√n. The central limit theorem makes x̄ approximately normal for large n whatever shape the underlying values have, which is what lets you convert SE into a confidence interval: x̄ ± 1.96·SE for 95%. Note the SE is itself estimated from one sample; it is not the observed spread of the mean across repeated samples.
Which one to report. Use SD to describe a population or a process: "order values vary with SD $60" tells you what a single customer looks like. Use SE for anything about a mean: confidence intervals, hypothesis tests, comparing group averages, error bars on a mean plot. Error bars are where this bites. SD bars show the spread of the sample; SE bars are √n times narrower (at n = 40,000, 200× narrower) so SE bars can make a trivial difference look decisive. Label which one a chart shows.
Failure mode. Overlapping SD bars tell you nothing about whether two means differ. Non-overlapping 95% CIs do imply a significant difference at α = 0.05; overlapping CIs do not imply the difference is non-significant, so never use overlap as the test: compute the CI of the difference directly.
Worked example. Two groups, 40,000 orders each, SD $60, means differing by $3.50.
| Measure | Value |
|---|---|
| SD of order value | $60 |
| SE of the mean | $0.30 |
| SE of the difference | $0.42 |
| 95% CI on the difference | [$2.67, $4.33] |
Arithmetic: SE = 60 / √40000 = 60 / 200 = $0.30. For independent groups the difference has SE = √(0.30² + 0.30²) = $0.42, so the 95% CI is 3.50 ± 1.96 × 0.42 = [$2.67, $4.33]. Zero is far outside it; the difference is real. On a $50 average that is 7%, yet the SD bars (each ±$60) overlap heavily: the exact mistake that gets a real effect dismissed as noise.
The other trap. Because SE shrinks with n, enough data makes a $0.01 difference "significant." Significance is not importance: report the effect size and its CI, not just a p-value. And SE generalises to any statistic (a proportion's is √(p(1−p)/n), a regression coefficient has its own) while SD only describes a distribution of values. (Sample SD uses the n−1 denominator; the SE formula above inherits that.)
Curated: · Written: · Reviewed:
Q&A-51Explain a p-value to a product manager.
The 90-second answer
A p-value answers one question: if the variant truly changed nothing, how often would random assignment alone produce a difference at least this large? p = 0.03 means roughly 3 in 100 no-effect experiments would show a gap this big or bigger. It is not the probability the result is due to chance, and it is not the probability the feature works. Both of those readings cause bad ships, and both are common.
So a p-value is a surprise meter, not a value meter. It tells you the signal is unlikely to be pure noise. It says nothing about whether the signal is worth acting on.
A worked example
Hypothetical A/B test on a checkout button, 200k users per arm:
| Metric | Value |
|---|---|
| Observed lift in conversion (relative) | +0.1% |
| 95% confidence interval | +0.004% to +0.2% |
| p-value | 0.04 |
p = 0.04 clears the usual 0.05 bar. The interval excludes zero, so the effect is probably real and probably positive. It is also almost certainly not worth shipping: a 0.1% relative lift on a 3% conversion rate is about 3 extra conversions per 100,000 users, which will not cover the cost of maintaining a second checkout path. "Statistically significant" and "worth doing" are different questions, and the p-value only answers the first.
Where the alternatives matter
- Bayesian framing: report the posterior probability the variant beats control, e.g. "87% chance B is better." PMs read that directly, and it stays valid when you stop early. The cost is that you must state a prior, and the number moves when you change it.
- Sequential testing: if you check results daily and stop the moment p < 0.05, your real false-positive rate is well above 5%, and it grows with the number of looks. Either fix the sample size in advance or use a method that pays for the peeking: group-sequential boundaries or always-valid confidence sequences.
- Minimum detectable effect: decide before the test the smallest lift that would change your decision, then size the test for it. A test powered for a 2% lift cannot resolve 0.1%, and a p-value from an underpowered test is noise with a decimal point.
When a significant result still should not ship
- The interval's lower bound sits below the lift you need to justify the build.
- A guardrail moved the wrong way (latency, refunds, support tickets) even though the primary metric won.
- The effect is concentrated in one segment (new users, one platform) and flat or negative everywhere else.
- You ran many metrics or many segments. At 20 comparisons, one p < 0.05 is expected by chance; correct for it or pre-register the primary metric.
- The window covered a novelty period: a holiday, a launch, a pricing change.
What would change the decision
If the interval's lower bound sat above the lift you need, the guardrails held, and the effect repeated in a second cohort, ship it. Otherwise the p-value is telling you the difference is real, not that it is large enough to matter.
Curated: · Written: · Reviewed:
Q&A-52What does a 95 percent confidence interval for a lift of 2 to 6 percent tell you?
Short answer: the interval says the effect is statistically significant and positive, zero is outside it, and the true lift is plausibly between +2% and +6%, with +4% as the point estimate. The number that should drive the decision is the lower bound, +2%, not the midpoint.
Why the bounds mean that. The true lift is a fixed number; the interval is the random thing. You build it as point estimate ± 1.96 × standard error, and that construction is calibrated so that if you ran the experiment many times, 95% of the intervals you'd produce would contain the true lift. The 2–6% you're holding is one draw from that procedure. Zero exclusion falls out of the same machinery: a 95% CI is exactly the set of effect sizes you cannot reject at α = 0.05, so if 0 sits outside [2%, 6%], "no effect" is rejected. Had the interval been [−1%, 9%], you could not claim a positive effect at all.
What it does not tell you. Significance is not size. A +2% lower bound can be real and still not worth shipping if the margin is thin. And the 95% is only honest if the test ran as designed: no peeking, no optional stopping, one primary metric. If you stopped the moment the interval cleared zero, true coverage is below 95% and the interval is too narrow.
Deciding with it. Translate to business units first. At a 20,000 orders/week baseline:
| Bound | Lift | Weekly orders |
|---|---|---|
| Lower | +2% | +400 |
| Point | +4% | +800 |
| Upper | +6% | +1,200 |
Then pick:
- Lower bound clears the ship threshold → ship.
- Lower bound below threshold, point estimate above → you can't separate a small win from noise. Extend the test if the cost of being wrong is high; ship and monitor if the change is cheap and reversible.
- Interval wide because power was low → extend. Wide because the metric is genuinely high-variance → more traffic buys little; decide on the point estimate plus a guardrail.
The interval shouldn't drive the decision when the call is reversible and cheap, or when a guardrail metric (latency, refunds) is the real constraint.
Failure modes to name. Peeking inflates false positives and shrinks the interval. Multiple comparisons across segments or metrics widen the effective error rate. A sample-ratio mismatch means the interval is computed on a broken split. And the interval is model-based: a normal approximation on a ratio metric is fine at scale, shaky for rare events.
Curated: · Written: · Reviewed:
Q&A-53A test is statistically significant but the lift is 0.2 percent. Would you ship it?
Short answer: not on this evidence. A p-value under 0.001 says the effect is probably not zero. It says nothing about whether the effect is worth what it costs to capture. I'd ship only when the lower bound of the confidence interval clears a pre-registered minimum effect and the net value is positive after build and run cost. A 0.2% point estimate whose interval reaches down to 0.05% usually fails the second test.
What I'd clarify first. Which metric, and is it the one the business is paid on: contribution margin per user, not clicks? What annualized base does the 0.2% apply to? Is the cost a one-time build or an ongoing run cost? What's the reversal cost if we're wrong? And what minimum effect did we agree was worth acting on before the test started? If there wasn't one, that's the first thing to fix.
Why significance and magnitude come apart. The standard error of a difference in means scales as 1/√n. Double the sample and the interval narrows by about 29%; with enough users, an effect far too small to pay for itself still clears p < 0.05. Significance is a statement about precision, not size. The number you underwrite is the lower bound of the interval, because that's the effect you'd still have if the estimate sits at the pessimistic end.
Worked example (hypothetical figures, real shape):
| Item | Value |
|---|---|
| Baseline | 2.0M monthly buyers × $60 contribution/yr = $120M/yr |
| Observed lift | +0.2% relative, p < 0.001, 95% CI [+0.05%, +0.35%] |
| Annualized at point estimate | 0.002 × $120M = $240k/yr |
| Annualized at CI lower bound | 0.0005 × $120M = $60k/yr |
| Ongoing cost | $90k/yr service + 0.25 FTE at $220k loaded = $145k/yr |
| One-time build | 6 engineer-weeks × $4.5k = $27k |
| Net at point estimate | $240k − $145k = +$95k/yr, payback ≈ 3.4 months |
| Net at CI lower bound | $60k − $145k = −$85k/yr |
The point estimate says ship. The lower bound says the change is an $85k/yr loss. That gap is the whole decision, and it's why I wouldn't ship on this run: the test was powered for a much larger effect than 0.2%, so it can't resolve the question it's being asked. Either extend the test until the interval's lower bound clears $145k/yr annualized: a relative lift of about 0.12%, or find an implementation that costs under $60k/yr.
Failure modes to check before trusting the lift. Novelty: plot the effect by week; if week 1 is 3× week 4, the lift is decaying and the annualized number is fiction. Segment concentration: if the lift lives in a segment that's 5% of traffic, the cost to serve it may exceed the value. Use pre-registered cuts, not post-hoc fishing. Cannibalization: a lift on one surface offset by a loss on another; only a funnel-level holdout catches it. Guardrails: latency p99, support contacts, refund rate. A margin lift that pushes p99 from 120 ms to 300 ms is a net loss. Sample ratio mismatch: chi-square the assignment counts; a 1% imbalance invalidates the read.
When I'd ship anyway. If the change is a platform investment that unblocks larger work, or if the small lift comes bundled with removing a maintenance burden, the decision is strategic and the test isn't the deciding evidence. Say that out loud rather than dressing it up as a win.
Curated: · Written: · Reviewed:
Q&A-65What are type I and type II errors in an A/B test, and which is worse?
The 60-second version. A type I error is a false positive: the test says there is an effect when there is none. A type II error is a false negative: the test says there is no effect when there is one. Neither is worse in the abstract: the worse one is whichever costs more for this decision. Shipping a change that hurts revenue is usually worse than shelving a change that would have helped, so most teams run alpha at 0.05 and accept a 20% miss rate. But if the change is cheap and reversible, a false negative is the expensive one, and you should buy power instead.
The four outcomes. Every test lands in one of these cells:
| Test says effect | Test says no effect | |
|---|---|---|
| Reality: effect | correct (power) | type II, rate β |
| Reality: no effect | type I, rate α | correct |
Alpha is the false-positive rate you set before the test: 0.05 is the convention, meaning 1 in 20 nulls comes back significant by chance. Power is 1 − β, and 80% is the common target, meaning you miss 20% of real effects of the size you designed for. The size you designed for is the MDE, the minimum detectable effect.
The arithmetic that ties them together. For a two-sided test at alpha 0.05 and 80% power, sample size per arm is roughly 16 × p(1−p) / δ², where p is the baseline rate and δ is the MDE. Baseline conversion 10%, MDE 1 percentage point: 16 × 0.09 / 0.0001 = 14,400 per arm. Tighten alpha to 0.01 and you need about 21,000 per arm: a 45% longer test. That is the real trade: type I protection is bought with sample size, and sample size is bought with time.
Weighing the two costs. Suppose a harmful ship costs $200k in churn and an abandoned winner costs $20k in delayed revenue. Run 100 experiments with no real effect: 5 false positives ship, $1M burned. Run 100 with a real 1pp lift: 20 get missed, $400k forgone. Type I is 2.5× worse here, so tighten alpha to 0.01 and eat the longer runtime. Flip the costs, a reversible feature flag where a bad ship is a one-hour rollback, and the false negative dominates, so keep alpha at 0.05 and push power to 90%.
Failure modes that break the rates. Peeking is the big one: checking daily and stopping at the first p < 0.05 inflates the real false-positive rate well above 5%. Simulations of continuous monitoring put it near 30%. Fix it with a fixed horizon or a sequential test that spends alpha properly. Multiple metrics do the same thing: 20 independent metrics at alpha 0.05 give you about one false positive per test, so pre-register one primary metric and treat the rest as exploratory. And an underpowered test does not just miss effects: it also inflates the winner's curse, because the effects that do clear the bar are the ones that got lucky, so the shipped lift is smaller than the observed one.
What I would say to the stakeholder. Name the primary metric and the MDE, set alpha and power from the cost ratio, compute the sample size, and commit to the horizon before launch. If the required sample size is not reachable in the time available, the honest answer is that the test cannot answer the question, not that you should run it anyway and read the p-value.
Curated: · Written: · Reviewed:
Q&A-64How do you choose between a t-test and a chi-square test?
The 90-second answer
The outcome variable's type picks the family; the design picks the specific test. Continuous outcome compared across two groups → t-test on the means. Two categorical variables, asking whether they are associated (conversion by variant, defect by shift) → chi-square test of independence. For a 2×2 table the two agree: the chi-square statistic is the square of the two-proportion z statistic, so a z-test for proportions and a chi-square on the same table give the same p-value. Chi-square also covers goodness-of-fit, where one categorical variable is compared against a hypothesized distribution.
Then check assumptions before trusting either.
t-test: independent observations; the sampling distribution of the mean approximately normal. The CLT does the work once n is in the tens per group unless the data are badly skewed; and equal variances. Use Welch's correction, the default in R's t.test and in most libraries, unless you have a reason to pool.
chi-square: independent observations, each subject counted once (paired binary data needs McNemar's test), and expected counts of at least 5 per cell. Cochran's rule of thumb. Below that the chi-square approximation is unreliable; use Fisher's exact test.
Where it goes wrong
The failure I've seen is a t-test on a skewed continuous outcome. Hypothetical but typical: revenue per user, 800 users per arm, three purchases of about $4,000 in one arm. The t-test works on the sum, so those three points move that arm's mean by 3 × 4000 / 800 = $15 per user: enough to flip a comparison whose medians differ by cents. The bootstrap resamples the same 800 values, so those three points land in only a fraction of resamples and the percentile interval for the difference in means straddles zero. Same data, opposite conclusions.
| Outcome | Example | Test |
|---|---|---|
| binary, two independent groups | converted or not | chi-square (2×2) or z-test for proportions |
| binary, paired | same user before/after | McNemar |
| categorical, >2 levels | plan tier vs churn | chi-square independence |
| continuous, roughly symmetric | trimmed session time | Welch's t-test |
| continuous, skewed | revenue per user | bootstrap or Mann-Whitney |
| counts, small expected cells | rare event by cohort | Fisher's exact |
Decision rule
Continuous outcome → t-test if roughly symmetric, Mann-Whitney (a rank test for a shift in distribution, not a difference in means) or a bootstrap if skewed. Categorical outcome → chi-square if every expected count is ≥ 5, Fisher's exact if not. Paired categorical → McNemar. Report an effect size alongside the p-value: Cohen's d or the mean difference with a confidence interval for the t-test, the risk difference or odds ratio for the chi-square.
Curated: · Written: · Reviewed:
Q&A-70A fraud model flags 99 percent of fraud and wrongly flags 1 percent of legitimate orders. If 0.1 percent of orders are fraud, how many flags are real?
Just over 9%: 990 of the 10,980 flags are real fraud. The other 9,990 are legitimate orders caught by the 1% false-positive rate.
Two assumptions decide that number. First, the 99% and 1% were measured on the same population you are deploying to; a model validated on a 1%-fraud sample will not reproduce this. Second, 1% is a per-order false-positive rate, not a per-flag one.
Work it through on 1,000,000 orders:
| Orders | Count | Flagged |
|---|---|---|
| Fraud (0.1%) | 1,000 | 990 (99% recall) |
| Legitimate (99.9%) | 999,000 | 9,990 (1% FPR) |
| Total flags | — | 10,980 |
| Precision | — | 990 / 10,980 = 9.0% |
The mechanism is that precision's denominator is dominated by the majority class. Fraud contributes 1,000 orders to the population; legitimate orders contribute 999,000. A 1% error on the big pool (9,990) swamps a 1% miss on the small one (10). Recall is a property of the fraud class alone; precision is not, which is why the same model scores 99% recall and 9% precision without contradiction.
Whether 9% is acceptable is a cost question, not a modelling one. Let C be the cost of investigating one flag and L the average loss on a missed fraud. Investigating is worth it when precision × L > C. At C = $2, break-even is L = 2 / 0.090 ≈ $22. If average fraud loss is $500, each flag is worth 0.09 × 500 − 2 = $43 and you investigate everything. If it is $15, each flag loses $0.65 and the queue is pure cost: you would rather absorb the fraud.
If the arithmetic says no, you have three levers, and they trade against each other:
- Raise the threshold. A stricter cut trades recall for precision. At a hypothetical operating point of 70% recall and 0.1% FPR: 700 true flags against 999 false ones, precision 41%. You catch 30% less fraud and cut the review queue by 85%.
- Raise the base rate by segmenting. Score only where fraud concentrates. In a segment running at 1% fraud, the same error rates give 9,900 true flags against 9,900 false: precision 50%. Same model, different population.
- Buy a better feature. A 0.1% FPR at 99% recall is a different model, not a different threshold.
Failure modes to name. Drift: the FPR was measured on last quarter's traffic; a new payment provider or a promo changes the legitimate-order distribution and precision moves with it, silently. Alert fatigue: at 9% precision, reviewers who clear ten false flags per real one start rubber-stamping, and recall in production falls below the offline 99%. Staffing: 10,980 flags a day is roughly 23 reviewer-hours at 30 seconds each, which is the number that actually kills the deployment.
Detect all three the same way: log confirmed-fraud-per-flag daily and alert when it drops below the break-even precision C / L. That single ratio tells you whether the queue is still paying for itself.
Curated: · Written: · Reviewed:
Q&A-62In a regression of sales on price and advertising, what does the price coefficient mean?
The 60-second answer. The price coefficient is the partial derivative of expected sales with respect to price: the change in average sales per one-unit increase in price, holding advertising fixed. With sales in units and price in dollars, it is units per dollar. It is a conditional-mean statement about the data you fit, not a causal effect, unless price moved for reasons uncorrelated with the error term.
What "holding advertising fixed" actually does. OLS picks the coefficients that minimise squared residuals. The price slope equals the slope from regressing the residual of sales on advertising against the residual of price on advertising (Frisch–Waugh–Lovell). That is why the multiple-regression slope can differ sharply from the simple one.
Worked example: five stores, sales in units, price in dollars, ads in $k:
| store | price | ads | sales |
|---|---|---|---|
| 1 | 8 | 2 | 360 |
| 2 | 9 | 1 | 330 |
| 3 | 10 | 4 | 340 |
| 4 | 11 | 3 | 310 |
| 5 | 12 | 5 | 310 |
Simple regression of sales on price alone: slope = -12 units per $1. Multiple regression on price and ads: price = -20 units per $1, ads = +10 units per $1k. The data were generated as sales = 500 - 20·price + 10·ads with no noise, so the multiple regression recovers the generating coefficients exactly; the simple slope is biased toward zero because advertising is positively correlated with price (Σ(p-p̄)(a-ā) = 8 > 0) and raises sales. That is omitted-variable bias, and it is the most common reason a price coefficient reads wrong.
Units depend on the functional form.
| Model | Price coefficient reads as |
|---|---|
| sales = a + b·price | units of sales per $1 |
| log(sales) = a + b·price | a $1 rise multiplies sales by e^b (b = 0.05 → ~5%) |
| log(sales) = a + b·log(price) | elasticity: % change in sales per 1% change in price |
| sales = a + b·log(price) | b units per unit of log price; a 1% price rise moves sales by ≈ 0.01·b units |
Failure modes and how you catch them.
- Omitted variables (seasonality, competitor price, promotion): the sign flips or the magnitude jumps when you add controls; watch the coefficient move as you add them.
- Simultaneity: price is set by the firm, often in response to demand shocks, so price correlates with the error. The coefficient then mixes the demand curve with the supply rule. Detect by instrumenting price with cost shifters or running a price experiment.
- Measurement error in price attenuates the coefficient toward zero.
- Multicollinearity with advertising inflates the standard error, not the point estimate; check the VIF.
- Heteroskedasticity or autocorrelation in time-series data makes the reported standard error wrong even when the point estimate is fine; use robust or clustered SEs.
- Extrapolation: a slope fit on $8–$12 says nothing about $25.
What to report. The coefficient with its units, its standard error or 95% CI, and the range of price in the data. A coefficient of -20 units per $1 with a CI of -26 to -14 is a claim you can defend; -20 alone is not.
Curated: · Written: · Reviewed:
Q&A-49Estimate how many coffee cups are sold in a large city each day. How do you approach it?
Pin down two things before any arithmetic. What counts as "sold": a retail transaction in the city on a weekday, not home-brewed, not supermarket beans. And who is in the city: residents plus the commuters and tourists who buy at 8am. I'd take a city of 8 million residents with a daytime population near 9.5 million, and say that out loud. Every figure below is illustrative for that city; the structure is what transfers.
Top-down.
| Step | Assumption | Running figure |
|---|---|---|
| Residents | — | 8.0M |
| Adults (18+) | 80% | 6.4M |
| Drink coffee at all | 60% | 3.84M |
| Drink coffee on a given weekday | 65% | 2.50M |
| Cups per daily drinker | 1.8 | 4.50M consumed |
| Share bought out, not home or office | 35% | 1.57M sold |
Roughly 1.5–1.6 million cups a day.
Bottom-up cross-check. Count the outlets and their throughput. A city this size runs about 4,000 places that sell coffee: chain stores, independents, kiosks, plus convenience stores and transit-station counters. Average daily throughput across that mix is around 400 cups: a busy chain store does 600–800, a small independent 150–250, and convenience-store coffee is high-volume but low-margin. 4,000 × 400 = 1.6M. The two chains land within 2% of each other, and that agreement is the only evidence you actually have.
Where it breaks. The number is most sensitive to cups per daily drinker. Move it from 1.8 to 1.3, plausible if the city skews toward one morning cup, and the top-down falls to 1.14M, a 28% swing. The "bought out" share is next: 30% instead of 35% gives 1.35M. Population and the 60% coffee-drinker share are the least sensitive, because they're the best-measured.
Three failure modes I'd watch for:
- Using resident population only. Commuters and tourists buy a disproportionate share of the morning cups. With a daytime population of 9.5M against 8M residents, you're understating the buyer pool by nearly 20%. Detect it by comparing your outlet count against transit ridership into the centre.
- Forgetting the home-brew factor. Cups consumed is not cups sold. Drop the 35% and you report 4.5M: nearly 3x too high. This is the most common error in the whole estimate.
- Anchoring. A candidate who opens with "about 2 million" and then builds a chain to reach it has produced a rationalisation, not an estimate. The chain has to run forward from the assumptions.
Close. Give the number, name the assumption it hinges on, and say what you'd check first: cups per daily drinker, ideally from a POS or loyalty-card dataset.
Curated: · Written: · Reviewed:
Q&A-71Which distributions do you expect for daily orders, time between purchases, and order value?
Three different generative stories, so three different answers, and each answer changes depending on whether the process is homogeneous.
Orders per day. If orders arrive independently at a roughly constant rate, Poisson is the right model, and the check is mean-variance equality: for a Poisson, Var = mean. Real order streams usually fail that check because the rate moves with hour of day, day of week, and promotions. When Var > mean, the negative binomial is the workhorse: it is a Poisson whose rate is itself gamma-distributed, so it keeps the count interpretation and adds one dispersion parameter. Pick Poisson when the mean-variance ratio sits near 1 and a single parameter is enough; pick negative binomial when the ratio is materially above 1 and tail probabilities matter, which is exactly the capacity-planning case. In one capacity plan I reviewed, hourly orders were assumed Poisson with mean 40, so variance 40. Observed variance was 180. Peak hours exceeded provisioned capacity about three times as often as the plan predicted, because the Poisson tail is far thinner than the negative binomial tail at the same mean.
Time between purchases. Exponential only if purchases form a Poisson process: memoryless, constant hazard. For a single customer with a steady purchase rate that is a reasonable first model, and the check is a flat empirical hazard: the fraction of customers who buy in the next day should not depend on how long it has been since the last purchase. It usually does. Heterogeneity across customers (some buy weekly, some yearly) makes the aggregate inter-purchase time a mixture of exponentials, which is heavier-tailed than any single exponential and has a decreasing hazard. Fit a Weibull or gamma and read the shape parameter; shape < 1 means decreasing hazard, so the exponential is wrong. The practical consequence is that a churn model built on a constant hazard over-predicts near-term purchases for the long-lapsed.
Order value. Positive and right-skewed, so log-normal is the default starting point: take logs and check normality on a QQ plot. Log-normal is the right choice when the tail is thin enough that mean and variance are stable across samples. For marketplaces with a few very large orders the tail is closer to Pareto and the sample mean is unstable; then model log value with a heavier-tailed distribution, or report quantiles and medians instead of means. The deciding criterion is tail weight: if the largest order in a sample of 10,000 is more than a few multiples of the 99th percentile, log-normal will understate the tail.
The general rule is to state the generative assumption and then test the implication that is cheapest to falsify: mean-variance for counts, hazard shape for waiting times, log-normality of logs for amounts. When the test fails, move to the overdispersed or heavier-tailed alternative rather than patching the summary statistic.
Curated: · Written: · Reviewed:
Q&A-67Why can you use a normal-based confidence interval for average revenue when revenue itself is skewed?
The interval is a statement about the sampling distribution of the sample mean, not about the distribution of individual revenue. Revenue per customer can be heavily right-skewed; the mean of n independent draws still converges to a normal distribution as n grows, provided the variance is finite. Skewness does not break the central limit theorem, it only sets how large n has to be before the normal approximation is usable. So you can use the normal interval when your n is large relative to the skew of your revenue distribution, and the real work is checking that rather than assuming it.
The assumptions that matter: draws are independent (or close enough), variance is finite, and n is large enough for the skew at hand. Revenue has finite variance in practice, but the tail can be heavy enough that "large enough" means thousands, not thirty.
Berry-Esseen bounds how far off the normal approximation can be: sup_x |F_n(x) − Φ(x)| ≤ C·ρ/(σ³√n), where ρ = E|X−μ|³ and C ≤ 0.4748 (Shevtsova, 2010). The ratio ρ/σ³ is a skewness-like quantity, so the error falls as 1/√n but scales linearly with the skew.
Take 60 customers, 59 paying $50/month and one enterprise account paying $30,000/month.
- Mean = (59×50 + 30,000)/60 = 32,950/60 = $549.17
- Sum of squared deviations = 59×(50 − 549.17)² + (30,000 − 549.17)² = 14,701,071 + 867,351,388 = 882,052,459
- Sample SD = √(882,052,459/59) = $3,866.5
- Standard error = 3,866.5/√60 = $499.2
- 95% normal interval = 549.17 ± 1.96×499.2 = [−$429, $1,528]
The lower bound is negative revenue. The interval is not merely wide, it is wrong. For this data ρ/σ³ ≈ 7.4, so the Berry-Esseen bound at n=60 is 0.4748×7.4/√60 ≈ 0.45, meaning the normal CDF can be off by nearly half. At n=5,000 the same bound is ≈ 0.05. Same skew, different n.
The failure mode is a small n plus one dominant account: the sample mean and the sample SD are both driven by the same point, so the interval can include impossible values. Detect it by computing the sample skewness, by comparing the normal interval against a bootstrap interval on the same data, and by checking the endpoints against the domain, since revenue cannot be negative.
With one dominant account, the bootstrap is not a rescue either. Resampling 60 draws with replacement, the probability a resample omits the enterprise account is (59/60)^60 ≈ 0.36, so roughly 36% of resamples have mean ≈ $50 and 64% have mean ≈ $549. The percentile interval is bimodal and just as untrustworthy as the normal one. The real fixes are more data, a trimmed mean or median with its own interval, or reporting the mean with the caveat that a single account dominates it.
Curated: · Written: · Reviewed:
Q&A-68How would you get a confidence interval for a median, where there is no simple formula?
Core answer
The median has no closed-form standard error. For the mean you get SE = s/√n and the CLT hands you a normal interval; for the median the sampling distribution depends on the density at the median, which you don't know. Two routes work: the bootstrap percentile interval and the distribution-free order-statistic interval. State the assumption up front: observations are iid, and you resample at the unit of independence (the user, not the event).
Bootstrap. Draw B resamples of size n with replacement from the n observations, compute the median of each, and take the 2.5th and 97.5th percentiles of the B medians. B = 10,000 is plenty; the endpoints carry Monte Carlo error of order 1/√B, so 1,000 is usually enough and 10,000 when you care about the tails.
import numpy as np
rng = np.random.default_rng(0)
def median_ci(x, B=10_000, alpha=0.05):
n = len(x)
meds = np.array([np.median(rng.choice(x, size=n, replace=True))
for _ in range(B)])
return np.quantile(meds, [alpha/2, 1 - alpha/2])
Why it works: the empirical CDF is a consistent estimate of the population CDF, and resampling from it reproduces the sampling variability of the statistic. The percentile interval is a plug-in for the sampling distribution: you read quantiles off the distribution of the estimator instead of assuming it is normal.
Order-statistic interval. Exact, no resampling. Sort the data X₍₁₎ ≤ … ≤ X₍ₙ₎. The interval [X₍ₖ₎, X₍ₙ₊₁₋ₖ₎] contains the population median with probability 1 − 2·P(Bin(n, 0.5) ≤ k−1). For n = 100: k = 40 gives [X₍₄₀₎, X₍₆₁₎] at ≈ 96%, and k = 41 gives [X₍₄₁₎, X₍₆₀₎] at ≈ 94%, so 95% sits between them. Quote this when n is small or you want a guarantee that does not depend on the bootstrap.
When the percentile bootstrap is the wrong tool. For a right-skewed distribution the sampling distribution of the median is skewed and the percentile interval undercovers: nominal 95% can be 90% in practice. BCa (bias-corrected and accelerated) adjusts the endpoints for bias and skewness and is what I reach for when n < 50 or the data are heavily skewed. The Bayesian bootstrap replaces multinomial resampling with Dirichlet weights, which avoids the ties discrete data produce.
Failure modes. Resampling the wrong unit, events instead of users, makes the interval too narrow because correlated observations are treated as independent. Below n ≈ 20 the bootstrap distribution is too coarse to read tails off. Ties in discrete data make the percentile endpoints jumpy; BCa or the order-statistic interval handles that better.
Curated: · Written: · Reviewed:
Q&A-74A new feature has 45 users and a 20 percent higher conversion rate. What can you say?
The first move is to get the counts behind the rate. "45 users, 20% higher conversion" is a ratio of two ratios, and on 45 users it is a handful of conversions. Note also that 20% higher is relative: on a 17% baseline that is a 3.4-point absolute lift, not 20 points.
Worked example, hypothetical figures, Wilson 95% intervals:
| Group | Users | Conversions | Rate | 95% interval |
|---|---|---|---|---|
| new feature | 45 | 9 | 20% | 11%–34% |
| existing | 42 | 7 | 17% | 8%–31% |
The intervals overlap heavily. The observed lift is 3.3 points, but the standard error on the difference is about 8 points, so a frequentist test is nowhere near significant (p ≈ 0.7). A Bayesian read with a uniform prior gives P(new > existing) ≈ 0.65: a lean, not a decision.
So the honest statement is that this sample cannot separate the feature from noise. That is not the same as "do not ship."
When to ship anyway:
- Cheap, reversible, no guardrail risk: ship on the directional signal and keep measuring. The cost of a false positive is a rollback; the cost of a false negative is a delayed win.
- Expensive, hard to reverse, or touching a guardrail (revenue, latency, safety): wait until the interval on the lift excludes zero.
Deciding criteria to fix before looking:
- Minimum detectable effect. If a 20% relative lift is the bar, you need roughly 2,000 users per arm to detect it at 80% power against a 17% baseline.
- Decision rule. For example: ship if the 95% interval on the lift excludes 0 and no guardrail regresses; otherwise extend or stop.
- Cost asymmetry. State the cost of a false positive against the cost of a false negative, and let that pick the rule.
Failure mode I have seen: a leadership update reported a 20% conversion lift built on 9 conversions versus 7. It vanished after 2,000 more users per arm. The fix is a pre-registered decision rule and a dashboard that shows counts and intervals next to every rate.
Curated: · Written: · Reviewed:
Q&A-57Should an A/B test randomize by user, session, or page view?
Randomize by user. The randomization unit has to be the unit that experiences the change and the unit your metric is defined on. When those three diverge, the test measures something other than what you're shipping.
Why session and page-view assignment break
Randomization assumes no interference between units: a user's outcome depends only on their own assignment. Session-level assignment violates that whenever the treatment leaves a trace: a price they remember, a setting they changed, a cached response, a learned UI. The same person then contributes observations to both arms.
Two distinct things go wrong:
- Contamination. With 50/50 session randomization and 3 sessions per user, the probability a user sees both variants is 1 − 0.5³ = 87.5%. Any carryover effect now leaks across arms, and no analysis method recovers the clean contrast.
- Dilution. Each user's exposure is a 50/50 mix, so a user-level contrast is pulled toward zero: for a linear metric, roughly halved. A real effect can read as a null.
Variance, not just bias
Even with zero carryover, analyzing user-level metrics as if sessions were independent understates the standard error. Sessions within a user correlate (ρ), and the design effect is 1 + (m − 1)ρ for m sessions per user. Hypothetical figures: 1M users, 3 sessions each, ρ = 0.2 → DEFF = 1.4, effective n = 3M / 1.4 ≈ 2.14M, and naive standard errors are √1.4 ≈ 1.18× too narrow. An 18% understatement on a nominal 5% test pushes the false-positive rate well above 5%.
When a smaller unit is correct
- Logged-out or anonymous traffic where the visitor genuinely cannot return: a paid-campaign landing page, a guest checkout.
- Per-request properties with no memory: latency, cache hit rate, error rate. Here the page view is the unit of analysis and the metric is defined on it.
- Interference between users (marketplaces, social feeds): switchback in time or cluster randomization by geography, not session.
Checks before trusting the result
- Exposure overlap. Share of users who saw more than one variant. It should be 0 under user randomization; anything else means assignment is leaking (bad bucketing, cookie churn, logged-out fallback).
- Analysis unit matches randomization unit. If you are forced to randomize by session, analyze at the user level with cluster-robust standard errors or the delta method. That repairs the variance, not the contamination.
- Pre-period A/A test on the same unit to confirm the variance model before you read a lift.
| Unit | Users exposed to both arms | Use when |
|---|---|---|
| page view | ~100% for repeat visitors | per-request metrics: latency, cache hit, error rate |
| session | 1 − 0.5^k (87.5% at k = 3) | anonymous one-shot flows with no memory |
| user | 0% | anything remembered: pricing, ranking, onboarding |
| cluster / switchback | 0% within cluster | interference between users |
If you cannot randomize by user, say so explicitly and pick the analysis method that matches the unit you did randomize, then treat the result as weaker evidence, because it is.
Curated: · Written: · Reviewed:
Q&A-58An A/B test was set to 50/50, but one group has 3 percent more users. Does it matter?
Yes, if the test has enough users, a 3% imbalance is a red flag. A real 50/50 split with large samples should differ by a fraction of a percent. If one arm has 3% more users than the other, assignment or logging is broken, and the missing users are rarely missing at random. Don't read the metric until you've checked the split.
Assume "3% more" means the larger arm has 3% more users than the smaller, so 101,500 vs 98,500 out of 200,000. If you mean 53/47, the problem is worse.
The check is a chi-square test on the counts before any outcome. A common tripwire is p < 0.001. If it fails, stop and find the cause. If it passes, you can proceed, but note the imbalance.
Sample size decides. For 1,000 users, 508 vs 492 is about 3% more, but chi-square is (8^2/500) + (8^2/500) = 0.26, p ≈ 0.61. That's chance. For 200,000 users, 101,500 vs 98,500 gives (1500^2/100000) + (1500^2/100000) = 45, p ≈ 2e-11. That is not chance. Same 3% relative difference, different verdict.
| Total users | Observed | Chi-square | p-value |
|---|---|---|---|
| 1,000 | 508 / 492 | 0.26 | 0.61 |
| 200,000 | 101,500 / 98,500 | 45 | 2e-11 |
When the p-value is above your threshold, a small mismatch is tolerable. You can analyze the metric, but you should still check whether the imbalance is concentrated in one segment, like mobile or one country. If it is, that's a clue to a logging or filtering bug, not noise.
When the p-value is below threshold, don't try to fix it by re-randomizing or by adjusting the analysis. Re-randomizing without fixing the cause will reproduce the same imbalance. Stopping the test is usually right. Then find the cause: a slow variant that loses users before they are logged, a bot filter applied to one arm, a redirect that drops users, or a logging bug that only fires on one variant. Fix it and re-run.
A common failure mode is a slow treatment. Users on the slow arm leave before the logging call fires, so treatment has fewer users and looks worse on engagement. Another is a bot filter that runs on one arm only. Detection is a daily SRM check, segmented by device, country, and entry point. If the imbalance appears only in one segment, you've found the bug.
So the split is the first thing to check; if it's off, the metric comparison is not trustworthy.
Curated: · Written: · Reviewed:
Q&A-103Before trusting an A/B test's results, what checks would you run on the experiment setup itself?
Before I read the lift, I run four checks on the setup. If any fails, the result is not a result: it's a bug report. The order is assignment, instrumentation, guardrails, then the primary metric.
| Check | What I compute | Pass bar |
|---|---|---|
| SRM | chi-square on observed vs planned split | p > 0.001 |
| A/A pre-period | metric by arm, 7 days before start | diff within CI, p > 0.05 |
| Instrumentation | exposure count vs assignment count per arm | within 1% |
| Guardrails | latency, crash, unsub, revenue/user | no guardrail beyond MDE |
1. Assignment integrity: sample-ratio mismatch. I compare the observed split to the planned one with a chi-square goodness-of-fit test. For a 50/50 test with 100,000 control and 103,000 treatment, expected is 101,500 each; chi-square = 2 × (1500² / 101,500) ≈ 44.3, p < 1e-10. That is a real SRM, not noise. I investigate the assignment hash, the eligibility filter, and any bot or internal-traffic exclusion applied to one arm. A p-value above 0.001 is my pass bar; some teams use 0.0005.
SELECT arm, COUNT(DISTINCT user_id) AS n
FROM experiment_assignments
WHERE experiment_id = 'checkout_v3'
GROUP BY arm;
2. A/A test on the pre-period. I take the 7 days before the experiment started, split users by their assigned arm, and compare the primary metric. The arms should be indistinguishable: difference within the confidence interval, p > 0.05. If they differ, the assignment is not random with respect to the metric: a logging delay, a filter applied to one arm, or a hash bug. A/A catches assignment bias and metric-computation bugs. It does not catch a bug that only affects the treatment code path: that's what instrumentation checks are for.
3. Instrumentation and event logging. The exposure event must fire for every user who could see the change, and only for them. I count distinct users with the exposure event per arm and compare to the assignment counts; they should be within 1%. A common failure: the treatment path logs exposure but the control path doesn't, so control is undercounted and the lift is inflated. I also check that the metric event (purchase, click) is logged in both arms with the same name and payload, if treatment renames the event, the join drops treatment users. And I check the assignment unit matches the analysis unit: randomizing by user but analyzing by session breaks independence.
4. Guardrail metrics. Pre-registered guardrails (p99 latency, crash rate, unsubscribe rate, revenue per user) get checked before the primary metric. If a guardrail moves beyond its minimum detectable effect, the primary lift is irrelevant. You don't ship a 2% conversion lift that costs 200 ms of p99 latency.
Two more setup checks: the experiment ran at least one full week to cover day-of-week effects, and no user appears in both arms (contamination). If all four pass, I read the lift. If any fails, I stop and fix the setup first.
Curated: · Written: · Reviewed: —
Q&A-54How do you decide how long an A/B test must run?
The 90-second answer. Duration falls out of a sample-size calculation, not a calendar. Before launch, fix four numbers: the metric's baseline rate, the smallest relative lift worth shipping, α (0.05, two-sided), and power (0.80). Compute users per arm, divide by eligible daily traffic, round up to whole weeks so every weekday is represented. If that lands beyond the window you can wait, the honest move is not to run the test short: it's to change the design or not run it.
The calculation. For a two-proportion test:
n per arm = 2 (z_{1−α/2} + z_{1−β})² p̄(1−p̄) / δ², p̄ = (p1+p2)/2, δ = p2 − p1.
At α = 0.05 and 80% power the z terms are 1.96 and 0.84.
| Baseline | Relative lift | Absolute δ | Users per arm |
|---|---|---|---|
| 2% | 5% | 0.1 pp | ~315,000 |
| 2% | 10% | 0.2 pp | ~80,600 |
| 10% | 5% | 0.5 pp | ~57,700 |
The 2% baseline row is the trap: a 5% relative lift is a 0.1-point move, and detecting it costs 315,000 users per arm.
Worked example. A checkout test on a 2% conversion baseline wanted to detect a 5% relative lift. The calculation gives ~315,000 per arm, 630,000 total. At 18,000 eligible users/day that's 35 days: five weeks. The team stopped at day 3 with ~54,000 users, saw +6%, and shipped. The lift didn't replicate. Three days of a 35-day test is 9% of the required sample; the observed effect was noise.
Alternatives, and when fixed-horizon is the wrong frame. Fixed-horizon is one design among several, and the choice is made before launch, not after.
- Peeking. Checking a fixed-horizon test repeatedly and stopping when p < 0.05 inflates the false-positive rate: five equally spaced looks at a nominal 5% give a real rate near 14%, continuous monitoring near 30%. If you need to stop early, use a design that controls this.
- Group-sequential / alpha spending. O'Brien-Fleming or Pocock boundaries let you stop at pre-planned interim looks while holding α. Cost: wider intervals and slightly more users than a fixed-horizon test at the same power.
- Always-valid / sequential (mSPRT). Valid at any stopping time, so you can monitor continuously. Cost: less power per user, so the required n is larger.
- Bayesian. Report the posterior probability that the lift exceeds a threshold and stop on a decision rule. Peeking is fine under the rule, but the answer depends on the prior, and a flat prior on a 2% baseline is a strong assumption.
- Wrong tool. If the required n exceeds a window you can hold (say 8–12 weeks), or the metric is long-horizon (retention, churn) and a short test can't see it, don't run a short A/B test. Use a switchback, a diff-in-diff or synthetic control against a matched holdout, or ship behind a flag and measure a long-running holdback.
Failure modes and detection. Underpowered tests show a wide interval that straddles zero: check the CI width against the minimum detectable effect before reading the point estimate. Novelty and primacy effects bias the first days; whole-week rounding plus a 1–2 week floor covers weekly cycles. Seasonality (holidays, paydays) means a test run in one week may not generalize: note the window.
Pre-register. Record the power calculation, the planned duration, and the stopping rule before launch. Stopping early without a pre-planned rule is the failure the calculation exists to prevent.
Curated: · Written: · Reviewed:
Q&A-55What is wrong with checking an A/B test every day and stopping when it becomes significant?
What a strong candidate says first. The test statistic isn't a fixed number: as data accumulates it traces a random path. Checking every day and stopping at the first significant reading means you're asking whether the path ever crosses the line, not whether it's across at the end. Under the null that path is a random walk, and the probability that a walk crosses a threshold at some point is much larger than the probability it sits beyond that threshold at the planned end. So the stopping rule itself selects the noisiest excursion, and the false positive rate blows past the 5% the test was designed for. Assume a two-sided 5% test, one look planned at the end, and the same metric checked daily: that's the setup the numbers below use.
Why the inflation happens. For a random walk, the reflection principle gives an exact relation: the chance of ever crossing a one-sided boundary by the end is about twice the chance of being beyond it at the end. So a one-sided 5% test monitored continuously has roughly a 10% false positive rate; a two-sided 5% test lands at about 10% too, because 2 × 2.5% doubles. Each extra look adds another chance to catch a high excursion, so the rate climbs with the number of looks and is worst when you look continuously.
| Stopping rule (two-sided 5% test) | False positive rate |
|---|---|
| one look at the planned end | 5% |
| continuous monitoring (reflection-principle bound) | ~10% |
| daily looks over a 30-day test (simulation) | ~25% |
The daily figure is a simulation result, not a closed form: it depends on how many looks, how the metric's variance behaves, and the true effect size. Treat it as "well above 5%," not a constant.
The two fixes, and when each is wrong.
Fixed horizon. Decide the sample size up front from a power calculation, then read the result once. This is the simplest correct thing and needs no new statistics. The cost is real: you cannot stop early when the effect is huge, so you burn traffic and calendar time you didn't need, and if the metric is seasonal or the effect decays, a long fixed run can dilute a real win. Don't use it when the decision genuinely can't wait (a broken checkout flow, a safety regression) because waiting for the planned end is the wrong trade there.
Sequential testing. Group-sequential boundaries (Pocock, O'Brien–Fleming) or always-valid confidence sequences are built for repeated looks: they widen the per-look threshold so the overall false positive rate stays at 5%. You get to stop early on a clear winner. The cost is complexity and a wider boundary: you need more data than a fixed-horizon test to declare the same effect, and the analysis is harder to explain to stakeholders. Don't reach for it when you'll only look once anyway; the machinery buys you nothing.
Failure mode to name. A team I've seen stopped 9 of 20 tests at the first significant reading. When those 9 features were re-run with fixed durations, 6 showed no effect: the early "wins" were the maximum of a noisy path, exactly what the stopping rule selects for. Detection: run A/A tests with the same stopping rule and count how often they declare a winner; if it's well above 5%, the rule is the problem, not the feature.
One line to hold onto. Look as often as you like for health monitoring (errors, crashes, guardrail metrics) but decide only at the planned end, or use a method designed for repeated looks.
Curated: · Written: · Reviewed:
Q&A-56An experiment tracked 20 metrics and one was significant. What do you conclude?
What I'd say first
With 20 metrics tested at α = 0.05, the chance that at least one comes back significant under a true null is 1 − 0.95²⁰ ≈ 64%. One hit out of 20 is the expected output of noise, not evidence of an effect. Default conclusion: nothing is established, and the single metric is a hypothesis for a confirmatory run rather than a ship decision.
The exception is pre-registration. If that metric was named as the primary metric before the test and the other 19 were secondary or guardrails, the 5% error rate applies to it alone and p < 0.05 is meaningful: provided the analysis was fixed in advance rather than chosen after seeing the data.
The arithmetic
| Metrics tested (independent) | P(at least one false positive) |
|---|---|
| 1 | 5% |
| 5 | 23% |
| 20 | 64% |
That is 1 − 0.95^m. The 20-metric row is the one in play here.
What a correction does to the hit
Suppose the significant metric came back at p = 0.03 and the other 19 were larger.
- Bonferroni: threshold α/m = 0.05/20 = 0.0025. 0.03 > 0.0025, so not significant.
- Benjamini–Hochberg at q = 0.05: sort p-values ascending and compare the smallest against (1/20) × 0.05 = 0.0025. 0.03 > 0.0025, so no rejections.
Under either correction the result evaporates. Bonferroni controls the family-wise error rate, the probability of any false positive, and is conservative. BH controls the false discovery rate, the expected share of false positives among rejections, and is the better default when you have many metrics and can tolerate some false leads. Experiment metrics are usually correlated, which makes both procedures conservative because the effective number of independent tests is below 20. That argues for BH over Bonferroni; it is not a reason to skip correction.
Failure mode and how it is detected
The concrete failure is shipping on the one metric that moved. Illustrative version: a redesign ships because time-on-page hit p = 0.03, the only significant result among 22 metrics, and the effect is gone in the next quarter's read. Detection is procedural, not statistical. Check the pre-registration document for a named primary metric, and check whether the hit survives the correction declared before the run. If the primary metric is flat and a secondary metric moved, the honest read is "no effect detected."
What I'd do next
Treat the hit as a hypothesis. Run a confirmatory experiment with that metric as the sole primary metric, powered for an effect smaller than the one observed: the observed effect is inflated by the winner's curse, since the metric was selected for being the largest. If it replicates, you have something. If it does not, you spent one experiment to avoid shipping noise.
Curated: · Written: · Reviewed:
Q&A-59A new feature shows a big lift in week one and almost none in week three. What is happening?
The week-one number is not the effect. It is the effect plus two contaminants that decay on their own schedule, and week three is closer to steady state but still mixes populations that shouldn't be mixed.
Three things produce the fade:
Novelty and habituation. Users click a new thing because it's new. That response decays as the feature becomes furniture: the same person, less curious, a week later. The shape is a monotone decay toward a floor.
Early-adopter composition. Week one's exposed population is whoever got the feature first: power users, novelty-seekers, people who opt into betas. They are not the marginal user. As exposure broadens, the average response falls even if every individual's response is unchanged. This is a composition shift, not a decay.
Primacy for existing users. A change that breaks muscle memory causes a temporary dip that recovers. Existing users show dip-then-recovery; new users, with no habit to break, show pure novelty decay. Calendar-week reporting averages the two and produces a single fading line that describes neither.
Slice by tenure and by exposure time to separate them. Exposure time means days since that user first saw the feature, not calendar week: calendar week 3 contains users on day 21 and users on day 1.
Hypothetical, 4-week rollout:
| Days since first exposure | New users | Existing users |
|---|---|---|
| 1-7 | +8% | -2% |
| 8-14 | +5% | +1% |
| 15-21 | +3% | +3% |
| 22-28 | +3% | +3% |
Both slices converge on +3%. The calendar-week view collapses them into one fading line, which is why the fade looks like a property of the feature rather than of the population.
Detect it by keeping a permanent holdback: 1% of users who never get the feature, so the steady-state effect stays measurable indefinitely. On 10M MAU that's 100k users; if the true lift is 3%, holding them back costs 0.03% of total engagement: cheap insurance against shipping a feature that decays to zero.
The trade-off is real in both directions. Waiting costs engineering time, blocks dependent work, and keeps the holdback running. Deciding early costs you the anecdote: a redesign shipped on a 9% week-one lift that was 1% below the old design three months later. A fading lift is still worth shipping when the steady-state estimate is positive, the feature is cheap to maintain, and it doesn't add complexity: a durable +3% is a ship. It is not worth shipping when the steady-state confidence interval includes zero and the feature carries ongoing cost.
Failure modes to name: peeking and stopping when the curve looks good; no pre-registered decision window and threshold, so the call is made on whichever week supports it; and cannibalization. The feature moves engagement from one surface to another, so the top-line metric fades while the feature is fine. Check guardrail metrics before concluding the effect is gone.
Curated: · Written: · Reviewed:
Q&A-61An analysis of customers who stayed a year shows they all used onboarding. What could be wrong?
What a strong candidate says first
The analysis conditions on the outcome. It looked at people who stayed and asked what they had in common; it never looked at the people who left. That is survivorship bias, and on its own it cannot tell you whether onboarding caused retention.
The number you need is the onboarding rate in the full starting cohort, not just among survivors. If almost everyone who signed up completed onboarding, then "all survivors onboarded" is close to guaranteed and carries almost no signal.
The full population
Illustrative figures for a 10,000-customer cohort:
| Group | Count | Used onboarding | Rate |
|---|---|---|---|
| All customers | 10,000 | 9,600 | 96% |
| Survived a year | 2,000 | 2,000 | 100% |
| Churned | 8,000 | 7,600 | 95% |
The headline gap is 100% against 95%: five points, not a hundred. And the 400 non-onboarders all sit in the churned group, which is exactly where the analysis gets dangerous: that group is not a control.
Why the comparison group is broken
Three things usually explain a gap like this, and none of them is onboarding causing retention.
Immortal time bias. If the onboarding flow takes two weeks, a customer who churns on day 3 is counted as "did not onboard", not because onboarding failed them, but because they left before they could finish. Early churners are structurally excluded from the onboarded group, so onboarding looks protective by construction. Test it by comparing onboarding completion among customers who churned before day 14 against those who churned after.
Reverse causality. Customers who were already engaged (high-intent signups, larger accounts, people who found the product themselves) are both more likely to finish onboarding and more likely to stay. Onboarding is a marker of intent, not the cause of retention.
Non-comparable controls. In the table, the 400 non-onboarders are mostly accounts provisioned by sales that never logged in, so their 0% survival is mechanical. If onboarding is a gate, you cannot use the product until you finish it, there is no control group at all, and the finding is a tautology.
What to do instead
A randomized holdout is the only clean answer: hold onboarding back from a random sample of new signups and compare 12-month retention. If you cannot randomize, stratify or match on acquisition channel, plan, and company size, then report the effect inside each stratum: the pooled association often vanishes once channel is held fixed. A survival analysis (Cox model, hazard ratio over time) uses every customer and every churn date instead of a single 12-month flag, and it handles the immortal-time problem directly.
Even a corrected comparison can be insufficient. If onboarding is mandatory, there is no counterfactual to compare against. If the confounder is unmeasured (motivation, budget, a champion inside the account) matching cannot remove it. And if the onboarding flow changed mid-cohort, you are comparing two different products.
Curated: · Written: · Reviewed:
Q&A-60Users who use feature X retain 40 percent better. Should we push everyone to use it?
The 60-second answer
First, pin down what "40% better" means: 40% relative (30% → 42% retention) or 40 percentage points (30% → 70%)? The first is plausible, the second almost never is, and the difference changes the decision. Then: no, not on this evidence. The 40% compares people who chose the feature with people who didn't, and choosing is itself a signal of engagement. Push everyone and the lift usually shrinks toward zero.
What I'd do instead: run a randomized promotion test. Randomly offer the feature to half the eligible population, then measure retention in both arms regardless of who actually used it. That's an intent-to-treat estimate of the effect of offering, which is exactly the decision you're making.
Why the naive number is inflated
Adoption is not random. Early adopters tend to be power users: more sessions, more integrations, longer tenure. Those traits predict retention on their own. So the +40% bundles the feature's effect with the selection effect, and selection is usually the bigger term.
The comparison ladder, with illustrative numbers (baseline 30-day retention 30%):
| Comparison | Retention | Gap |
|---|---|---|
| adopters vs non-adopters | 42% vs 30% | +12pp (+40% rel) |
| matched on pre-adoption engagement | 33% vs 30% | +3pp |
| randomized offer (ITT) | 31% vs 30% | +1pp |
The pattern to expect: the gap shrinks as you control for who self-selects. If it survives matching and randomization, it's real.
Sizing the test
If baseline 30-day retention is 30% and you want to detect a 2pp lift (30% → 32%) at 5% significance with 80% power, you need about 8,400 users per arm: roughly 17,000 total. That arithmetic decides whether the experiment is worth running: if you can't get that many eligible users in a reasonable window, you can't detect the effect you care about, and the honest answer is "we don't know" rather than "ship it."
Decision rule: ship if the ITT lift times the population size exceeds the cost of the intervention plus any downside risk. If the feature is cheap, reversible, and behind a flag, the experiment may not be worth the delay: ship it, keep the flag, and read the retention curve.
Failure modes
- Partial compliance: only 40% of the offered group actually uses it. ITT understates the effect per user; report a CACE/LATE if you need the per-user number, but decide on ITT.
- Short window: novelty inflates early retention. Measure at the horizon you care about (30 or 90 days), not day 7.
- Interference: if the feature is social or shared, treated users change control users' experience and the arms aren't independent.
- Peeking: stopping when the p-value first crosses 0.05 inflates false positives. Fix the horizon or use sequential testing.
When I'd skip the experiment
If the feature is a compliance or security requirement, there's no decision to randomize. If the cost of the test exceeds the value of the decision, ship behind a flag and watch. And if the feature is already live for some users, a staggered rollout with difference-in-differences is a cheaper quasi-experiment than a fresh test.
Curated: · Written: · Reviewed:
Q&A-75Why is A/B testing a marketplace or social feature harder than testing a button?
Core answer
The question is whether one user's outcome depends on another user's treatment. If it does, SUTVA: the stable unit treatment value assumption, that a unit's outcome depends only on its own assignment: is violated, and the difference in means between arms stops estimating the causal effect. That is the whole difficulty; everything else is a workaround.
Assumptions I'd state up front: the metric is a marketplace or social outcome (GMV, matches, sessions), users interact through a shared scarce resource or a shared graph, and I can randomize at a coarser level than the user.
Why it breaks
In a button test, my click doesn't change your click. In a marketplace, if I rank treated sellers higher, they take buyers that control sellers would have had. The control arm is contaminated by the treatment, so the user-level readout shows a lift that is partly a transfer, not a gain.
What to do instead
| Design | Randomize over | Estimates | Cost |
|---|---|---|---|
| User-level | users | own-treatment effect only | biased under interference |
| Cluster / geo | cities, markets | total effect including spillover | few independent units |
| Switchback | time windows in one market | total effect, temporal interference | autocorrelation, carryover |
| Ego-network | seed user + its network | effect with graph spillover | boundary contamination |
Pick by where the interference lives: spatial (delivery, local supply) → geo; temporal (pricing, dispatch) → switchback; graph (feed, invites) → ego-network. If you can't coarsen, use a counterfactual estimator: randomize the user but log the counterfactual outcome (budget-split / counterfactual logging in ad auctions) so the effect is estimable without a clean control.
The variance bill
Cluster randomization costs sample size. With intra-cluster correlation ρ and m users per cluster, the design effect is 1 + (m−1)ρ. Hypothetically: 1M users in 20 cities, m = 50,000, ρ = 0.01 → design effect ≈ 501, effective n ≈ 2,000. You need roughly 500× the users for the same power. That is why geo tests run for weeks and why the minimum detectable effect should be pre-registered.
Failure modes and detection
- Control moves with treatment share. Regress the control-arm outcome on the treated fraction; a nonzero slope is interference.
- Carryover in switchbacks. Compare the first window after each switch to later ones; a decaying effect means the washout is too short.
- Boundary contamination in ego-networks. Check whether "control" users adjacent to seeds behave like treated users.
Close
If users interact, randomize the unit that contains the interaction, and read out the total effect across clusters, not the difference between users inside one market.
Curated: · Written: · Reviewed:
Q&A-46Customers who received a promotional email spent 30 percent more. Did the email work?
No. That 30% is the difference between people you chose to email and people you didn't. Email lists are built from past engagement, so recipients already spend more. The number you need is incremental spend: what recipients spent minus what they would have spent without the email. The 30% mixes the email's effect with selection, seasonality, and whatever else differed between the groups.
Assumptions: spend is measured over a fixed window, no other campaign hits one group, and customers can be randomized.
Run a holdout. Randomly hold back 10% of the eligible audience, email the other 90%, and compare average spend over the same 14 days. Randomization makes the two groups equal in expectation on engagement, tenure, and past spend.
| Group | n | Avg spend | vs holdout |
|---|---|---|---|
| Recipients | 900,000 | $130 | +$4 (+3%) |
| Random holdout | 100,000 | $126 | — |
The email's measured lift is 3%, not 30%. If the 30% were real, the holdout would show $130 vs $100.
Cost of withholding: at a 3% lift, holding out 100,000 of 1M customers forgoes about $400,000 in spend (100,000 × $4), or $120,000 at 30% margin. At a 30% lift, it forgoes $3M. That cost buys a number you can defend. Size the holdout so the minimum detectable effect is smaller than the lift you care about; a 10% holdout on a large list often does, a 1% holdout on a small list does not.
When a holdout is impractical:
- Legal or contractual must-send (security notice, regulated disclosure). You cannot withhold.
- Tiny audience: 10% holdout leaves too few for power.
- Spillover: treated and control in the same household or social network; control gets exposed.
- Brand or broad campaign with no individual targeting.
Then use:
- Diff-in-diff: compare pre/post spend change in emailed vs not-emailed group. Requires parallel trends before the campaign. Check by plotting pre-period spend; if lines diverge before send, the estimate is biased.
- Matched controls: pair each recipient with a non-recipient on past spend, tenure, open rate. Reduces observed selection; unobserved engagement still biases.
- Geo or switchback holdout: randomize regions or time periods. Needs many regions and stable regional trends.
Failure modes:
- Contamination: holdout receives forwarded email or sees the ad. Detect by survey or by checking holdout open rates; if >0, lift is diluted.
- Peeking: stopping when p<0.05 inflates false positives. Pre-register the window.
- Novelty: a 3-day window overstates a durable effect. Use 14 days and check decay.
- Unbalanced randomization: check pre-period spend and open rate; run an A/A test before the campaign.
The 30% number is a reason to run the holdout, not a result.
Curated: · Written: · Reviewed:
Q&A-45The company raised prices 10 percent last month. How would you evaluate the result?
The 90-second answer
First I'd ask what decision this feeds (keep the increase, roll it back, or raise again) and what the counterfactual is. A before-and-after comparison can't answer it, because the month after a price change differs from the month before for reasons that have nothing to do with price.
Then I'd answer with contribution, not revenue. A 10% list-price increase almost always raises revenue per unit and almost always loses some volume; whether it was a good idea depends on whether the volume lost cost more margin than the higher price earned.
Worked example, illustrative numbers: 100,000 units at $20 with $8 variable cost. Price goes to $22, volume falls 8% to 92,000.
- Revenue: $2,000,000 → $2,024,000, +1.2%
- Contribution: 100,000 × $12 = $1,200,000 → 92,000 × $14 = $1,288,000, +7.3%
- Implied elasticity: −8% / +10% = −0.8
Revenue looks flat and the increase looks like a wash. Contribution is up 7.3%. Reporting revenue alone would have recommended rolling back a decision that made money.
Separating the price effect from everything else
The −8% volume is not the price effect. Some of it is seasonality, some is a competitor launch, some is the sales team discounting to hold deals. I'd use a control: regions, products or customer segments where the price didn't change, or the same period last year.
| Revenue | Volume | |
|---|---|---|
| Treated | +1.2% | −8.0% |
| Control | +4.0% | +3.0% |
| Difference | −2.8pp | −11.0pp |
The control grew, so the price-attributable volume loss is about 11%, not 8%, and elasticity is nearer −1.1. The direction of the conclusion holds here, but the size changes, and the size is what the next pricing decision uses.
What I'd check before trusting it
- Realized price, not list price. Compute revenue ÷ units for both periods. Grandfathering, contract terms and deeper discounting mean the realized increase is often well under 10%; if it lands at 4%, you're evaluating a different experiment than the one you announced.
- Stockpiling. Customers who know a price rise is coming buy ahead. Look at the two weeks before the change; if volume spiked, the "after" month is artificially weak and the effect is overstated.
- Mix. Revenue per unit can rise because customers traded up to premium SKUs. Check revenue per unit within SKU.
- Renewal timing. In subscription businesses the increase lands at renewal, so a one-month read captures only the slice of the base that renewed. Cohort by renewal date and read at 90 days, not 30.
- The average hides the response. Segment by tenure, size and plan. A common pattern is that most customers don't react and a small share churns hard; the mean tells you nothing about which.
- One price point is one point on the demand curve. Elasticity measured at +10% is a local slope; don't extrapolate it to the next increase without more data.
What I'd report
Contribution per customer and total contribution, net of control, with the elasticity estimate and its uncertainty, plus churn by segment. And I'd say what would change my mind: if 90-day churn in the treated group runs more than a few points above control, the increase is buying this quarter's margin with next year's revenue.
Curated: · Written: · Reviewed:
Q&A-69The worst-performing stores last quarter improved the most after coaching. Did coaching work?
Short answer: not from that comparison. "Worst last quarter improved most" is the signature of regression to the mean, not evidence of a coaching effect. The question is only answerable against a counterfactual: stores that were equally bad and were not coached.
Why the comparison is confounded
A store's quarterly number is true performance plus quarter-specific noise: a remodel, a district manager change, a snowstorm, one big local contract. Picking the bottom 20 selects on low skill and on a low noise draw. Next quarter the noise re-draws, so the group mean rises even if coaching does nothing.
Size that rise before you interpret anything. Illustrative figures: store-quarter sales with SD σ = 100 units and quarter-to-quarter correlation ρ = 0.5. The bottom 20 average 1.5σ = 150 units below the mean. Their expected position next quarter is ρ × (−150) = −75: a 75-unit improvement with zero coaching. That is the bar the observed gain has to clear, not zero.
What actually answers it
| Design | Removes | Still leaves |
|---|---|---|
| Randomize coaching within the bottom group | RTM and selection, by construction | Small n, Hawthorne effect |
| Regression discontinuity at an assignment cutoff | RTM and selection near the cutoff | Only valid just below/above the line |
| Difference-in-differences vs matched low performers | Common market trends | Residual RTM if controls were selected differently |
| This year's bottom 20 vs last year's bottom 20 | Some seasonality | Macro drift, a different noise draw |
Randomization is the only one that removes RTM by construction, because both arms were selected the same way. If coaching is assigned by a threshold, every store below a sales cutoff, regression discontinuity is nearly as good and needs no experiment. The prior-year comparison is the weakest: it assumes the market, seasonality and macro trend are unchanged, and last year's bottom 20 may have been selected on a different noise draw.
Failure modes to name
- Controls selected on a different variable. Matching on all stores, or on a noisier metric, gives the control group a different amount of RTM than the treated group, biasing the estimate in either direction.
- One-shot vs persistent. RTM is a one-quarter bounce. A real effect shows up again in Q2 and Q3; if the gap closes, it was noise.
- Gaming. Coached stores know they are measured. Watch for reclassified sales, discounting, or shifted reporting dates.
- Attrition. The worst stores get closed or restructured, which flatters the treated group.
- Headroom. Bottom stores have more room to move, so a percentage improvement is easier there than at the median.
What I'd tell the business
A prior-year comparison showing the coached bottom 20 up 15% against an uncoached bottom 20 up 13% suggests a real effect of roughly 2 points, but on 20 stores that is inside the noise. I would not expand on it. I'd want the randomized or discontinuity estimate, and I'd want the effect to hold for two more quarters. If it doesn't, the honest answer is "we can't tell yet," not "coaching worked."
Curated: · Written: · Reviewed:
Q&A-73Stores with more staff have higher sales. Does adding staff increase sales?
The 90-second answer
No, not from this data. The pattern is equally consistent with staffing driving sales and with a third factor (store size, location, local demand) driving both. The quantity that decides the decision is the marginal effect of one more staff member with those factors held fixed, and the raw correlation is an upper bound on it, not an estimate of it.
So I'd state the assumptions and then go get the number. Assume staffing is measured in hours rather than headcount, sales are annual and in one currency, and nobody has randomized staffing yet. Then stratify stores by size and traffic, compare within strata, and put the confounders in a regression. If a district or a labor-law change moved staffing for reasons unrelated to sales, that natural experiment is worth more than any amount of control.
Why the raw number is too big
Omitted-variable bias is multiplicative: bias = (effect of the confounder on sales) × (effect of the confounder on staffing). Illustrative figures: each extra 1,000 sq ft of floor space comes with 1.1 more staff and $35,000 more annual sales. That alone injects 1.1 × $35,000 = $38,500 into the raw slope. If the true within-size effect is $9,500, the raw slope reads $48,000.
| Analysis | Extra annual sales per added staff member |
|---|---|
| All stores, raw | $48,000 |
| Within similar-size, similar-traffic stores | $9,500 |
The figures are illustrative; the shape is what matters. The estimate should shrink when you compare like with like, and if it doesn't shrink at all, suspect that your strata aren't capturing the real driver.
Getting the estimate
import pandas as pd, statsmodels.formula.api as smf # statsmodels 0.14
df["size_q"] = pd.qcut(df["sq_ft"], 4, labels=False)
df["traffic_q"] = pd.qcut(df["foot_traffic"], 4, labels=False)
m = smf.ols(
"sales ~ staff_fte + C(size_q) + C(traffic_q) + C(region)", data=df
).fit()
print(m.params["staff_fte"], m.conf_int().loc["staff_fte"])
Then the decision arithmetic. At $9,500 of extra sales per staff member and a 30% gross margin, one added associate returns $2,850 of gross profit a year against a fully loaded cost around $35,000. The marginal hire doesn't pay for itself at the average store; it might at the top decile of traffic, which is where I'd test next.
Failure modes
- Controlling for a mediator. If more staff improve service and service pulls in traffic, foot traffic sits on the causal path. Conditioning on it removes part of the effect you're trying to measure.
- Reverse causality. Chains allocate hours to stores already trending up, which inflates the within-store estimate too.
- Collider bias. Conditioning on a downstream outcome such as "store hit its target" can create an association between staffing and sales where none exists.
- Extrapolation. The within-stratum estimate is local to the staffing range you observed. Going from 6 to 8 staff is not the same as going from 30 to 32.
- Simpson's paradox. Pooled positive with every stratum negative is a real pattern when strata differ in size, so print the stratum-level slopes, not just the pooled one.
What I'd say if pushed to decide now
I'd say the direction is plausible and the magnitude is unproven. I'd run a staggered pilot: add staff in a random subset of mid-traffic stores, hold the rest, and compare sales per square foot over 12 weeks. That answers the question the correlation can't.
Curated: · Written: · Reviewed:
Q&A-72Should we offer a 10 dollar discount to customers predicted to churn?
Short answer: only if the discount buys retention you would not have gotten otherwise, and the arithmetic clears break-even. Churn probability alone does not decide it.
Assumptions I would state up front: value per saved customer is margin over the remaining expected lifetime, not revenue; the offer is one-shot; the population is whoever the churn model flags. Change any of those and the numbers move.
The mechanism
Expected value per campaign is
EV = N × u × V − N × c
where N is customers targeted, u is the incremental retention rate (uplift), V is margin saved per retained customer, and c is the discount cost. Break-even uplift is u* = c / V. At c = $10 and V = $240, u* = 4.2%. Any policy whose measured uplift sits below that loses money, however high the churn score.
The trap is that a churn model ranks who is likely to leave, not who the offer changes. The highest-risk customers are often the least persuadable: they have already decided, or the cause is involuntary (failed card, relocation, price shock). Ranking by churn score sends discounts to people who would have stayed (pure cost) and to people who leave anyway (pure cost).
Worked example
A retention offer went to 20,000 predicted churners at $10 each. Measured uplift against a holdout was 2%.
| Input | Value |
|---|---|
| customers targeted | 20,000 |
| cost per discount | $10 |
| total spend | $200,000 |
| uplift in retention | 2% |
| incremental customers saved | 400 |
| margin per saved customer | $240 |
| value of retention | $96,000 |
| net value | −$104,000 |
Break-even needs 833 incremental saves, i.e. 4.2% uplift. The program was 2.2 points short.
How to measure uplift
Randomize. Split the flagged population into treatment and control, offer only to treatment, compare retention rates; the difference is u. A holdout on the whole base measures the offer's average effect, which is not the effect on the flagged segment, and the flagged segment is the decision you are making.
Better, model uplift directly: P(retain | treated) − P(retain | control), either as two models or one model with treatment as a feature and interaction terms. Then target the top decile by predicted uplift rather than by churn score. Hypothetically, if the top decile shows 6% uplift, the same $200,000 returns 1,200 × $240 = $288,000, net +$88,000: the same spend, opposite sign, purely from re-ranking.
| Policy | Uplift | Net value |
|---|---|---|
| No offer | — | $0 |
| All predicted churners | 2% | −$104,000 |
| Top decile by uplift | 6% | +$88,000 |
Failure modes and how to detect them
- Cannibalization. Discounts land on customers who would have stayed. Detect by measuring what share of treated retained customers also appear retained in control; if that share is high, u is small.
- Sleeping dogs. The offer prompts a customer to reconsider leaving. Detect as negative uplift in individual holdout cells: segment-level uplift can be negative while the average is positive.
- Involuntary churn. No discount fixes an expired card. Detect via churn reason codes and route those to dunning, not to discounts.
- Habituation. Customers learn to threaten churn at renewal. Detect rising offer acceptance or a rising renewal-date churn baseline across cycles.
- Holdout contamination. If control customers can obtain the offer through another channel, measured uplift is biased downward and you will under-invest.
When not to do it
Thin margins: small V pushes break-even uplift above anything you can achieve. Negative-margin customers: saving them destroys value. And differential pricing may be constrained by contract or regulation in your market: check before shipping.
Pay for the behaviour you change, not for the behaviour you get anyway.
Curated: · Written: · Reviewed:
Q&A-42How would you decide which marketing channel gets credit for a sale?
What I'd say in the first two minutes
Pin down the question first: credit for revenue, or credit for incremental revenue? Those need different tools. Attribution models (last-click, linear, time-decay, position-based, Markov/Shapley) are accounting rules applied to observed touchpoints. They are cheap, always-on, and fine for splitting a budget you have already decided to spend. They cannot tell you what would have happened without a channel, because the same targeting that responds to demand also decides who sees the ad. So I would use attribution to rank and diagnose, and experiments to decide.
Assumptions I would state up front: a deduplicated conversion table keyed to user or household, a channel taxonomy that does not change mid-flight, and enough volume per channel to run a holdout.
Why last-click misleads
Last-click gives 100% of credit to the final touch, which is systematically the touch closest to purchase: branded search, retargeting, direct. Those capture demand other channels created. Linear spreads credit evenly and over-credits cheap upper-funnel impressions. Neither rule is wrong; both are assumptions. The only model that measures causation is a holdout: withhold the channel from a random or matched-geo subset and compare total sales.
Worked example (illustrative figures)
| Channel | Last click | Linear | Incremental in test |
|---|---|---|---|
| branded search | 45% | 22% | 6% |
| paid social | 12% | 24% | 19% |
| 18% | 20% | 15% |
Take a quarter with 10,000 attributed sales and spend of $200k branded search, $150k paid social, $180k email. Attributed CPA versus incremental CPA:
- Branded search: 4,500 attributed sales → $44 attributed CPA; 600 incremental → $333 incremental CPA.
- Paid social: 1,200 attributed → $125; 1,900 incremental → $79.
- Email: 1,800 attributed → $100; 1,500 incremental → $120.
Decision rule: fund channels in ascending incremental CPA until the marginal iCPA crosses contribution margin per sale. Here paid social is the buy and branded search is the cut: the opposite of what last-click says.
Failure modes and how I'd catch them
- Branded-search cannibalization: holdout in matched geos; if total sales do not move, the spend was rent on demand that already existed.
- Geo spillover: check control markets for lift; use distant matched pairs and run 4–8 weeks.
- Cross-device double counting: compare platform-reported conversions against the warehouse dedup count; a 20–40% gap is normal and means platform CPAs are optimistic.
- Underpowered tests: compute the minimum detectable effect before launch; a test that cannot see a 5% lift is a coin flip.
Trade-offs
Experiments cost revenue during the holdout, take weeks, and only work for channels you can withhold. Attribution is free and daily. So: attribution for the weekly budget split, experiments for the two or three largest line items each quarter.
Curated: · Written: · Reviewed:
Q&A-63A policy launched in one region only. How would you estimate its effect without an experiment?
Answer
Assume the policy is a region-level treatment with no individual randomization, and I have panel data on the outcome for the treated region and candidate controls, monthly, over several years. The estimand is the effect on the treated region: an ATT, not a population average.
The workhorse is difference-in-differences: the treated region's before-after change minus a control's before-after change. The control stands in for the counterfactual "what the treated region would have done absent the policy," and that substitution is valid only if the two would have moved in parallel. So the first thing I do is not compute the effect: it is check the pre-period.
Take a policy that lifted orders in one region:
| Region | Before | After | Change |
|---|---|---|---|
| treated | 10,000 | 11,200 | +12% |
| control | 8,000 | 8,720 | +9% |
| DiD | +3 pts (480 orders) |
The naive before-after read gives +12%; the control's +9% is the shared trend (seasonality, a national ad push) so the policy-attributable part is 3 points, or 480 orders in levels. In regression form, with unit fixed effects α_i and time fixed effects γ_t:
Y_it = α_i + γ_t + τ·(Treated_i × Post_t) + ε_it
τ is the DiD estimate. The event-study version replaces the single Post dummy with period dummies interacted with Treated_i; the coefficients on pre-periods are placebo leads. If those leads are jointly significant, parallel trends fails and τ is biased: that is the check, not a visual "looks parallel."
When DiD is the wrong tool: if the treated region is the only plausible unit and no donor matches its pre-path, use synthetic control. Weight donor regions to reproduce the treated region's pre-treatment outcome trajectory, then compare post-treatment. If there is no usable control at all (a national policy), interrupted time series models the pre-trend and extrapolates, but it cannot separate the policy from any other shock in the same window. If the policy has an eligibility cutoff, regression discontinuity is sharper. Matched DiD sits between: match on covariates, then difference.
Failure modes and how I'd catch them:
- Non-parallel trends. Detect with the event-study leads and a placebo test: pretend the policy launched two periods earlier and check the "effect" is near zero. If it isn't, the design is broken, not the estimate.
- A confounder in the window. A second policy, a competitor launch, a price change. Detect by auditing the policy calendar for the treated and control regions over the same weeks; if one exists, add it as a covariate or find a cleaner window.
- Spillovers (SUTVA). The policy changes behavior in the control region: customers cross the border, or the control is a supply-chain neighbor. Detect by testing controls at increasing distance; if nearby controls show effects and far ones don't, use a donut-hole design that drops adjacent units.
- Compositional shift. Region boundaries redrawn or population moved. Detect by checking the denominator, not just the numerator.
- Inference with few units. One treated region means cluster-robust standard errors are unreliable; use randomization inference: reassign treatment to each control in turn and compare the actual estimate to that placebo distribution.
Operational cost: the design work is the pre-period audit and the control search, and the estimate is only as good as the donor pool. If I can't find a control whose pre-trend tracks the treated region, I report the effect as unidentified rather than quote a number.
Curated: · Written: · Reviewed:
Q&A-100An interviewer asks why revenue at one store fell this year. How do you structure your answer?
I'd refuse to name a cause until I've split revenue into its drivers and found which one moved.
Revenue = visits × conversion × average transaction value. That's the tree. I'd state it, ask for those three numbers for both years, and compute the change on each branch before offering any hypothesis. If the interviewer won't hand over data, I say what I'd pull first (point-of-sale transactions, door-counter visits, average ticket) and in what order.
Then the worked case. Suppose the numbers come back:
| Driver | Last year | This year | Change |
|---|---|---|---|
| store visits | 200,000 | 170,000 | -15% |
| conversion | 30% | 30% | 0% |
| average transaction value | $40 | $41 | +2.5% |
Last year revenue = 200,000 × 0.30 × $40 = $2.40M. This year = 170,000 × 0.30 × $41 = $2.09M, down 12.9%. Traffic fell 15%, conversion is flat, ticket is up 2.5%. Traffic is the branch that moved, so the question narrows from "why did revenue fall?" to "why did 30,000 fewer people walk in?"
Now the trade-off. The driver tree is the right first cut when the drop is broad and the aggregate numbers are clean. It is the wrong first cut when the aggregate hides a mix shift: if every segment's conversion fell but the mix moved toward a high-conversion segment, aggregate conversion can look flat and the tree points you at traffic when the real story is per-segment. The deciding criterion is whether the branches move together. If one branch dominates, drill into it. If all branches look flat but revenue fell, the aggregate is lying and you cut by segment (category, channel, daypart, new versus returning) before trusting the tree.
Failure modes I'd name, and how I'd catch each:
- Simpson's paradox. Aggregate conversion flat while every segment's conversion fell. Catch it by cutting the same metric by segment and checking whether the mix changed.
- Seasonality and partial periods. Comparing year-to-date against a full prior year, or a store that opened in March. Catch it by comparing like-for-like weeks and checking store open dates.
- Denominator error. A door counter recalibrated mid-year, or visits counted at the mall entrance rather than the store. Catch it by auditing the measurement system before believing the 15%.
- Price/volume confusion. Revenue down while units are up because of markdowns. Catch it by pulling units and average price separately instead of trusting the blended ticket.
- One-off local events. A competitor opening, a road closure, a weather event. Catch it by checking local event and competitor-opening logs against the week the drop started.
The confirming test depends on the branch. If traffic fell, I'd check whether the drop is concentrated in specific days or weeks (event-driven) or spread evenly (structural), and whether nearby stores saw the same pattern (local versus chain-wide). If conversion fell, I'd check staffing hours and queue times against the drop.
A strong answer names the driver that moved and one test that would confirm the cause, not a list of fifteen possibilities.
Curated: · Written: · Reviewed:
Q&A-91What goes into the one-paragraph summary at the top of your analysis?
The 60-second version
The summary is a decision memo, not an abstract. Five elements, in this order: the question, the answer with direction and magnitude, the uncertainty around it, the one caveat that could flip the decision, and the action you recommend. Write it last, place it first. A reader who stops after the paragraph should still make the same call you would.
What each element has to carry
| Element | Weak | Strong |
|---|---|---|
| Question | "we looked at shipping" | "Does free shipping over $30 raise order volume enough to pay for itself?" |
| Answer | "orders were significantly impacted" | "Orders +6% (3,000 → 3,180 per 100k sessions)" |
| Uncertainty | "high confidence" | "95% CI +3% to +9%" |
| Caveat | (none) | "71% of orders already clear $30, so the subsidy applies to most of the base" |
| Action | "consider rolling out" | "Do not ship: net contribution −$3,663 per 100k sessions" |
Arithmetic behind that last row (hypothetical figures, but the shape is the point): 2,130 subsidized orders × $2.10 shipping = $4,473 cost; 180 incremental orders × $4.50 margin = $810 gain; net −$3,663. The lift is real and the decision is still no, which is exactly why the action belongs in the paragraph, not in section four.
Alternatives, and when to pick one
- Decision-first (BLUF): the default for anyone who will act on it. Execs, PM, on-call. Costs the reader nothing.
- Method-first: warranted when the audience will re-run or audit the analysis and the method is the contested part. A stats review, a peer replication. Lead with the estimator and the assumption that carries it.
- Narrative/chronological: warranted when the finding contradicts a prior belief and the reader needs the path to believe it. Rare at work; usually a sign the summary is doing the analysis's job.
Deciding criterion: who reads it and what they do next. If their next action is a decision, lead with the decision. If their next action is to check your work, lead with the method.
Length: ~100 words is the working target for a decision-first summary. Go longer when the decision is irreversible or expensive (a pricing change, a schema migration) and the caveat needs its own sentence. Stay short when the decision is cheap to reverse and the reader is skimming on a phone.
Failure modes
- Number without direction: "engagement was significantly impacted". Two readers took opposite actions off that sentence. Always sign and magnitude.
- Confidence without an interval: "we're 95% confident" says nothing about the range the reader should plan for.
- Caveat buried in paragraph three: if it can flip the call, it belongs in the summary.
- Action missing: ending on the finding hands the decision to the reader, which is how the finding gets misused.
How I'd check it
Hand the paragraph to someone who hasn't seen the analysis and ask them to state the decision they'd make. If it doesn't match yours, the summary failed, not the reader.
Curated: · Written: · Reviewed:
Q&A-85How do you choose which chart to use for a finding?
The 90-second answer. Write the finding as a sentence before you touch a chart. If it doesn't contain a comparison ("search drives 41% of sessions, more than email's 39%") you don't have a finding yet, and no chart rescues that. The comparison in the sentence picks the encoding. The data type doesn't.
The mechanism is perceptual accuracy. Cleveland and McGill's 1984 ranking of graphical encodings puts position on a common scale first, then position on non-aligned scales, then length, then angle and slope, then area, then volume and colour. Pick the encoding highest on that list that can express your comparison.
| Finding sentence | Encoding | Why it wins |
|---|---|---|
| "X rose from A to B over time" | line, time on x | position on a common scale; slope carries the rate |
| "X is the largest of N categories" | sorted horizontal bar | length from a zero baseline; sorting removes the reader's search |
| "X and Y move together" | scatter | position on two scales; shows the joint distribution, not just a coefficient |
| "X is spread wider than Y" | histogram or ECDF, small multiples | overlapping densities hide the tails you care about |
| "X is 4pp above Y" | a sentence, or a two-bar chart | two numbers don't need a chart |
Two rules fall out of the encoding choice. Length encodings must start at zero; position encodings need not. A bar chart of 100 vs 95 drawn from a baseline of 90 renders as a 2:1 length ratio, (100−90)/(95−90), against a true ratio of 1.05:1. That is a 20× exaggeration of a 5% difference. A line chart of the same two points can honestly start at 90, because the reader is judging position and slope, not length.
Failure modes, and how you catch each:
- Pie charts. Angle encoding, and non-adjacent slices have to be compared by mentally rotating the circle. Fine for two or three slices; at twelve, a 4pp gap is unreadable. Detection: ask a reader to rank the top two slices.
- Dual y-axes. Two unrelated series on independent scales manufacture a correlation that isn't in the data. If you need it, plot the ratio, or index both series to 100 at a common start.
- Stacked bars. Only the bottom segment has a zero baseline, so comparing middle segments compares lengths with different origins. Use small multiples or a slope chart instead.
- Rainbow colormaps.
jetis not perceptually uniform and invents banding. Useviridisfor sequential data, and a diverging map only when there is a meaningful midpoint.
# matplotlib 3.8
import matplotlib.pyplot as plt
sessions = {"search": 412, "email": 388, "social": 121, "direct": 79}
items = sorted(sessions.items(), key=lambda kv: kv[1], reverse=True)
labels, values = zip(*items)
fig, ax = plt.subplots(figsize=(6, 3))
ax.barh(labels, values, color="#3b6ea5")
ax.set_xlim(0, max(values) * 1.15) # zero baseline, headroom for labels
for y, v in enumerate(values):
ax.text(v + 8, y, str(v), va="center")
ax.invert_yaxis() # largest category at the top
ax.set_xlabel("sessions")
The check is a reader test, not a taste test. Show the chart to someone who hasn't seen the data and ask them to state the main comparison in one sentence. If their sentence isn't yours, the chart is wrong, not the reader. Label the key number directly on the chart so they never have to read it off an axis.
Self-assessment
Key points (rubric). A strong answer (1) states the comparison as a sentence before naming a chart, (2) picks the encoding by perceptual accuracy, not by data type, (3) applies the zero-baseline rule to length encodings and explains why position encodings are exempt, (4) names at least two failure modes with a detection method, and (5) closes with the reader test as the acceptance criterion.
What interviewers look for. Whether you can state the comparison before the chart exists; whether you know why position beats area; whether you can name a failure mode and how you'd catch it; whether you'd defend a non-zero baseline on a line chart without hand-waving.
Weak-answer patterns. "It depends on the data type" (categorical → bar, time → line) with no comparison named. Defaulting to a pie or a dual-axis because the tool offers it. "Use the right chart for the data" with no mechanism. Choosing a chart first and hunting for a finding to fit it.
Follow-up questions. "Your PM wants a pie chart of 12 segments: what do you say?" "The line chart starts at 90 and an exec says it exaggerates: how do you respond?" "Two metrics on different units, one chart?" "How do you choose between a histogram and an ECDF?" "What if the comparison is a single number?"
Curated: · Written: · Reviewed:
Q&A-86When is it wrong to start a y-axis above zero?
Short answer: a non-zero baseline is wrong whenever the reader's takeaway is a ratio. "twice as many", "up 60%", "A beats B". It is defensible when the takeaway is a rate of change or a threshold crossing, and the axis is labelled with its range.
The encoding decides it, not the data. Cleveland and McGill's ranking of graphical encodings puts position on a common scale first and length and area well below it. A bar's value is carried by its length, and length is read as a ratio against the neighbouring bars. Move the baseline and you change every ratio in the chart while the numbers underneath are untouched. A line's value is carried by position and slope; the reader compares the steepness of segments, and a truncated axis shifts the intercept without touching the slope. Same data, different perceptual channel, different rule.
The arithmetic. Say a service's uptime moved from 96.1% to 96.8% and someone charts it as two bars.
| Axis start | Bar heights (units) | Visual ratio | Actual ratio |
|---|---|---|---|
| 95 | 1.1 and 1.8 | 1.64 | 1.007 |
| 0 | 96.1 and 96.8 | 1.007 | 1.007 |
Tufte's lie factor is the size of the effect shown divided by the size of the effect in the data: 1.64 / 1.007 ≈ 1.6. The target is 1.0. A 0.7-point uptime move reads as a 64% jump, and that is the version people argue about in a review.
When truncation is the right call. Series that live in a narrow band far from zero: p99 latency, CPU utilisation, temperature, a stock price. Zero-baselining a p99 chart that moves between 180 and 220 ms flattens the thing you are looking at. In order of preference: plot the delta from a stated reference ("ms above 180"), keep the truncated axis with the break marked and the full range printed, or pair it with a small multiple showing the whole range. All three keep the reader able to recover the real magnitude.
Failure modes.
- Truncated bars, columns or area marks. Always wrong; the mark's length is the value.
- Dual axes. Two independently truncated scales on one frame let you place the crossing point wherever you want it.
- Bars on a log axis. Bar length from zero is undefined on a log scale, so the bar is a lie by construction. Use dots or lines.
- Indexed charts (base = 100) with the base dropped from the label. The reader cannot tell what 100 means.
- Truncated stacked bars. Segment lengths no longer sum to the total visually, so the parts stop adding up to the whole.
How to check. Redraw at zero and see whether the sentence you would write about the chart changes. If it does, the truncated version was doing the arguing, not the data.
Curated: · Written: · Reviewed:
Q&A-87How do you present an analysis to executives who have ten minutes?
A strong candidate opens by naming the decision and the ask, then the number.
"Cut channel X. It saves $400k a year. The risk is a small dip in brand search. Everything else is in the appendix."
That is the whole ten minutes if the room agrees. The rest of the time is for the two questions an executive will actually ask: how do you know, and what happens if you're wrong.
Default shape: recommendation and impact in one sentence, two or three figures that carry it, the main uncertainty, methods in an appendix. The reason is a failure I've watched: an analyst spent 8 of 10 minutes on data sources and joins, and the meeting ended before the recommendation to cut a $400k channel was raised. The recommendation never got a decision.
But BLUF-with-appendix is not always right. Three placements, and when each wins:
| Placement | Use when | Cost |
|---|---|---|
| Methods in appendix, referenced only if asked | Decision is routine, reversible, audience trusts the team | Exec can't defend the number if challenged later |
| One methods slide up front (2 min), then recommendation | Number is surprising or contradicts a prior belief | Eats 20% of the meeting |
| Pre-read sent 24h ahead, meeting is decision-only | Decision is irreversible, regulated, or needs sign-off from several people | Requires the exec to actually read it |
When a longer methods discussion is warranted: the methodology is novel or the number is counterintuitive; the decision is hard to reverse or externally auditable; the executive is the one who has to defend the figure to a board or regulator; or a domain expert in the room will challenge the estimate. In those cases, spend the time: a decision made on a number nobody can defend is worse than a decision deferred.
Failure modes to name: methods-first (the story above); over-compression, where the exec leaves unable to answer "how do you know" and the decision stalls in the next meeting; and no explicit ask, so the ten minutes end without a yes/no.
A ten-minute deck, figures illustrative:
| Slide | Content |
|---|---|
| 1 | Cut channel X: saves $400k/yr. Risk: brand search may dip. |
| 2 | Incremental sales from X were 2% of spend |
| 3 | Sensitivity: breakeven if incremental is above 1.2% |
| appendix | data, method, joins |
Check before you walk in: does slide 1 alone state the recommendation, the impact, and the main risk? If not, the deck isn't ready.
Curated: · Written: · Reviewed:
Q&A-88How do you tell stakeholders that a result is uncertain without losing their trust?
In the first two minutes
Ask what decision the number feeds and when it has to be made. Then answer in one breath: "We expect $12.4m, range $10.8m–$13.0m. The range is driven almost entirely by Q4 renewal rate. It's safe to commit the $9m of fixed spend now; hold the $2m expansion until we see October renewals."
That's the technique: a most-likely value, a range, one named driver, and a decision that's safe across the whole range. Stakeholders keep trust when the number they act on doesn't move under them, not when the number is precise.
Why a range holds up
A point estimate is a claim about the world you can't defend. A range is a claim about your knowledge, which you can. When actuals land inside the range you stated, you were right; when they land outside, you have one specific thing to explain. False precision fails the other way: $12.4m that comes in at $10.9m reads as a miss even though the model was fine.
When a range backfires
- Anchoring on the low end. Give "$10.8m–$13.0m" and a budget owner plans against $10.8m, then treats $12.4m as upside you don't deserve credit for. Fix: state the most-likely value first and explicitly; say the low end is a tail, not the plan.
- Paralysis. A wide range reads as "we don't know," and the decision slides to a meeting that never resolves it. Fix: pair the range with the decision that's safe across it.
- Loss of accountability. A range becomes a place to hide: "somewhere between 8 and 20" is not a forecast. Fix: commit to the most-likely value and to what would move it.
When a point estimate plus a caveat is better
If the decision is cheap and reversible, or the stakeholder needs one number to act (a budget line, an SLA target, a headcount) give the point estimate and one sentence of caveat: "Plan on $12.4m; if renewals come in under 80% we'll be at $11m and I'll flag it in October." The range is still there, just not the headline. Ranges earn their cost when the decision is expensive or hard to reverse.
Worked example (hypothetical)
1,000 accounts, $14.6k average contract.
| Scenario | Renewal | Accounts | Discount | Revenue |
|---|---|---|---|---|
| Downside | 78% | 780 | 5% on renewals | 780 × 14.6k × 0.95 = $10.82m |
| Base | 85% | 850 | — | 850 × 14.6k = $12.41m |
| Upside | 88% | 880 | — | 880 × 14.6k + 10 × 14.6k = $12.99m |
Range $10.8m–$13.0m, most likely $12.4m, driver is renewal rate. The $9m fixed spend is safe at the low end; the $2m expansion is not, so it waits for October.
| Decision | Reversibility | Present as |
|---|---|---|
| Commit $9m fixed spend | hard to reverse | range + commitment safe across it |
| Hold $2m expansion | reversible | point estimate + caveat, revisit October |
| Headcount plan | cheap to adjust | point estimate + one caveat sentence |
Failure modes to name
- Range so wide it carries no information ("$5m–$20m").
- Range with no driver, so nobody knows what to watch.
- A most-likely value outside the range, or a range that's a confidence interval dressed as a forecast.
Self-assessment
Key points a strong answer hits:
- Names the decision and its deadline before the number.
- Most-likely value first, then the range.
- One named driver.
- A decision safe across the whole range.
- Commits to what would move the number and when they'd flag it.
What interviewers look for: whether you separate uncertainty in the world from uncertainty in your knowledge; whether you can price the cost of a range; whether you'd drop to a point estimate when the decision is cheap.
Weak-answer patterns: "it depends" with no number; a range with no driver; hedging that defers the decision; over-precision to look confident.
Follow-ups:
- "Your range was $10.8m–$13.0m and actuals came in at $9.9m. What now?"
- "The CFO wants one number for the board deck. What do you give them?"
- "How do you know your ranges are calibrated?": track hit rate: if your 80% intervals contain actuals about 80% of the time, they're calibrated; if they contain them 100% of the time, they're too wide.
- "What if the driver is unobservable until after the decision?"
Curated: · Written: · Reviewed:
Q&A-89A senior stakeholder rejects your analysis because it contradicts their experience. What do you do?
In the first minute
Ask what they're seeing before defending what I computed. A senior stakeholder's experience is data too: usually a sample of one, but a sample that can point straight at a gap in my analysis. So the opening move is a question, not a rebuttal: "What number were you expecting, and where does that come from?" Then check whether my analysis covers that case. If it does, I show the definitions and the query. If it doesn't, that's my bug, not their stubbornness.
The one thing I won't do is re-cut the analysis until it agrees with them. That's the failure this question is fishing for.
Why their experience can beat my dashboard
Three causes cover most of these disagreements, and all three are checkable in an afternoon:
- Scope. They're looking at a segment, region, or product line my query filtered out.
- Definition. They count bookings; I count recognized revenue. They count active users; I count monthly actives with a 30-day window.
- Timing. They're describing the last two weeks; my extract ends at month close.
A worked example
A sales director pushed back on a regional revenue analysis showing an 8% decline. The analyst held the line. The director was right: the query joined on direct-sales accounts only, and channel partners, 28% of the region's revenue, were excluded. Partners had grown hard enough to flip the sign.
| Scope | Regional revenue trend |
|---|---|
| Direct sales only (72% of revenue) | −8% |
| Including channel partners (28% of revenue) | +3% |
The arithmetic is worth doing out loud, because it tells you how big the omitted segment has to be to reverse the answer. Hypothetical figures, but the shape is real: 0.72 × (−8%) + 0.28 × (+31%) ≈ +3%. A segment holding 28% of the base only needs to grow about 31% to turn a −8% headline into +3%. That is not an exotic growth rate. It is a Tuesday.
What I actually say
"You're telling me the region is growing and I'm showing −8%. We can't both be right about the same thing, so one of us is measuring something different. What's in your number that isn't in mine?" Then I name the three candidates above and test them in order of how cheap they are to check. Definitions first: that's a five-minute conversation. Scope second: one query. Timing last.
If the analysis holds up, I say so plainly and show the definitions, not just the chart. "Here's the filter, here's the join, here's the row count. If your number comes from a different source, let's reconcile the two."
When they're wrong
Sometimes the stakeholder is defending a decision already made, or generalizing from one vivid deal. Then I don't cave and I don't stonewall. I state what the data supports, what it doesn't, and what evidence would change my mind, then move the argument off the number and onto the decision criteria, with finance or a second analyst in the room. Arguing about whose number is right is unwinnable; agreeing on what would make the decision wrong is not.
Failure modes worth naming
- Caving. Re-cutting until the number matches their expectation. The metric stops meaning anything the moment it's negotiated.
- Stonewalling. "The data says X" without checking scope. That's the channel-partner case, and it costs you the room.
- Silent disagreement. Nodding in the meeting, then shipping the original analysis unchanged. Worst of both: they think they won, and nothing was fixed.
A useful detector: if the same stakeholder objects to three analyses in a row, the problem is almost certainly definitions, not them. Fix the definition once and the objections stop.
Curated: · Written: · Reviewed:
Q&A-90You have ten data requests this week and time for four. How do you choose?
I rank by the decision each request feeds, not by who asked or when it arrived. Two things have to be true before a request enters the queue: it names a decision someone will actually make, and that decision has a date. If it can't name either, it goes to a backlog: even if it's a one-hour job.
For each request I ask the requester two questions in writing: what will you do differently depending on the answer, and by when do you need it? Then I estimate effort in half-days. Score = decision value (1–5) × deadline pressure (1–5) ÷ effort in days. The score is a sorting device, not a verdict: it makes the trade-off arguable instead of invisible.
This week's ten, scored. I have four days of ad hoc capacity; the rest of the week is recurring reporting.
| Request | Decision it feeds | Needed by | Effort | Score |
|---|---|---|---|---|
| Checkout funnel drop after Tuesday's release | roll back vs patch forward | today | 0.5 d | 4×5/0.5 = 40 |
| Pricing test readout | extend or kill the price test | Fri | 1.5 d | 5×5/1.5 = 17 |
| Activation cohort definition | ship onboarding v2 to all users | Wed | 1 d | 3×4/1 = 12 |
| Board deck metrics | board narrative | in 3 weeks | 1 d | 3×2/1 = 6 |
| Churn by region | Q3 retention budget split | next month | 2 d | 4×2/2 = 4 |
| Refund rate by plan | pricing page copy test | in 2 weeks | 1.5 d | 3×2/1.5 = 4 |
| Sales ad-hoc SQL | none stated | none | 2 h | backlog |
| Weekly active users trend | none stated | none | 1 h | backlog |
| Dashboard colour change | none | none | 1 h | backlog |
| Competitor benchmark | none stated | none | 3 d | backlog |
Top four: funnel (0.5 d), pricing (1.5 d), activation (1 d), board deck (1 d) = 4 days. Churn by region starts Monday: its deadline is a month out, so a week's slip costs nothing. The colour change and the benchmark sit in a backlog I review every Friday; the sales SQL I answer by pointing at the self-serve tool, which takes ten minutes instead of two hours.
What I tell the people I cut is not "no" but a choice: "I can give you a directional answer in two hours, or the rigorous one next Wednesday. Which do you want?" Most take the directional answer. A 60% answer delivered before the decision beats a 100% answer delivered after it.
Failure modes I watch for. Arrival order is the default and it's wrong: the team that answers in the order requests land spends the week on dashboard tweaks while a $2m pricing call gets made without data. Seniority is the second: a VP's curiosity question outranks a staff engineer's release-blocking one unless the decision test is applied to both. The third is the "quick pull" that turns into three days; I estimate in half-days before accepting, and if the estimate exceeds the slot I say so before starting. And silent deferral: if you don't tell someone they're seventh, they assume you're working on it and stop asking.
Curated: · Written: · Reviewed:
Q&A-92A team asks for a dashboard. How do you decide what goes on it?
What a strong candidate says first
Ask two questions before drawing anything: who opens this, and what do they do differently depending on what they see? A dashboard is a decision surface, not a report. If a chart can't change an action, it's decoration.
Then: list the decisions the team makes on a recurring cadence (weekly campaign review, daily ops triage) pick three to six metrics that inform those decisions, and give each one a comparison, a target, and a named action when it's off. Ship that, then check view counts after a month and cut what nobody opens.
Illustrative board (figures invented for the example):
| Metric | Comparison | Target | Action if off target |
|---|---|---|---|
| weekly orders | last 4 weeks | 25,000 | review campaigns |
| on-time delivery | last week | 95% | escalate to ops |
| refund rate | last month | under 3% | audit top reasons |
Where "small and curated" breaks
It's the right default for an operational team board, and wrong in three cases:
- Exploratory surface. A data team or PM doing open-ended investigation needs a self-serve BI tool over a governed semantic layer, not a curated board. Curation there means filing a ticket for every question.
- Executive or board review. A wider board, 10–15 metrics across functions, is warranted because the audience compares across areas rather than acting on one. Different job: alignment, not triage.
- Incident war room. Real-time, high-cardinality, no targets. During an incident you want raw signals (error rate by endpoint, queue depth, saturation) at 10-second granularity, not a weekly target.
So the real decision is whether this is a triage surface (curated, batch, targets) or an investigation surface (self-serve, real-time, no targets). Most teams need both; conflating them is why boards rot.
Curated vs self-serve, batch vs real-time
- Curated + batch: cheap, stable, forces metric definitions to be agreed once. Cost: any new question needs a change request, and hours of latency is fine for weekly decisions.
- Self-serve + real-time: answers novel questions, but you pay in query cost, inconsistent definitions, and dashboards nobody trusts. Real-time earns its cost only when the decision loop is minutes: autoscaling, on-call triage, fraud.
Criteria for going bigger: the audience spans more than one decision owner, the metrics are read for comparison rather than action, and a semantic layer exists so "revenue" means one thing everywhere. Without that last one, a bigger board multiplies disagreement instead of clarity.
Failure modes
- Vanity metrics: totals that only go up. Detect by asking what you'd do if the number halved. No answer, cut it.
- Alert fatigue: every chart with a threshold pages someone. Count pages per chart per month; a chart that pages and is never acted on trains people to ignore the channel.
- Definition drift: two teams' "active user" differ by 15%. Detect with a metric dictionary and one owner per metric; disputes surface in review.
- Dashboard rot: a 40-chart board viewed 3 times in two months while the team keeps asking analysts the same questions by message. Detect with view counts and a monthly prune.
How I'd validate
Ship the small board, instrument it, review at 30 days: views per chart, and whether the off-target actions actually happened. Cut charts with zero views and no owner. Add a metric only when a decision needs it.
Curated: · Written: · Reviewed:
Q&A-93How do you handle personal data such as emails and addresses in your analysis?
First I'd ask what the analysis actually needs, because most questions that mention emails only need a domain or a count. If the ask is "how many customers per email domain," the address itself never has to leave the warehouse. That's the assumption I'd state and then test: does any step of this pipeline require a direct identifier, or only a derived value?
The mechanism I rely on is deriving what I need in place and exporting only aggregates. In Postgres that looks like this:
SELECT lower(split_part(email, '@', 2)) AS domain,
count(*) AS customers
FROM customers
WHERE created_at >= DATE '2025-01-01'
GROUP BY 1
HAVING count(*) >= 10;
split_part('ada@example.com', '@', 2) returns example.com; lower normalises case so Ada@Example.com and ada@example.com land in the same bucket; the GROUP BY counts customers per domain; HAVING drops any domain with fewer than ten customers. The result is rows like example.com | 4123. No address is selected, so none can be exported.
There are two traps here. Hashing an email is not anonymisation: the space of real addresses is small enough to brute-force, so a plain SHA-256 of an address is reversible with a wordlist plus a domain list. If I need a stable join key, I'd use HMAC with a key held outside the analyst's environment, and treat the output as pseudonymous rather than anonymous: under GDPR Recital 26, pseudonymised data that can be attributed to a person using additional information is still personal data, so the same handling rules apply. Second, aggregation alone doesn't guarantee anonymity. Sweeney's 2000 study of US census data found 87% of the population could be uniquely identified by ZIP code, gender and date of birth. A table of city, age band and count can re-identify someone with no name in it at all.
That's why I check the quasi-identifiers, not just the direct ones, and suppress small cells. A row with count 1 in a town of 800 people is a name. Suppressing rows under 10 removes them, but the column no longer sums to the total, so I'd also suppress complementary cells or publish the suppressed total: otherwise the difference hands the value back.
I've seen this fail once. An analyst exported 250,000 customer emails to a personal spreadsheet to count domains, and the file ended up in a folder open to the whole company. The count was correct; the export was the incident. The fix isn't a policy reminder, it's making the aggregate the path of least resistance: the query above runs in the warehouse in seconds.
My default is to derive in place, aggregate before export, suppress small groups, and keep anything pseudonymous inside approved systems. If someone asks for raw addresses, I ask what decision the raw value changes. Usually nothing does.
Curated: · Written: · Reviewed:
Q&A-98Your product analytics events are unreliable. How do you work with engineering to fix them?
Two problems are tangled here: the events that are already wrong, and the process that keeps producing wrong ones. The question is about the first, so I'd start there, but I'd fix the second in the same pass or I'm back in a month.
Assumption I'd state up front: I don't yet know which events are wrong or how. So step one is bounding the damage, not proposing a tracking plan.
Diagnose against a source of truth. Analytics events are a projection of something real: orders, signups, payments. I'd reconcile event counts against that system, per day, per event, and find where they diverge and from when. I'd do this with the engineer who owns the emitting service, sitting together, not via a ticket.
-- orders is the source of truth; purchase_completed is what we're auditing
SELECT date_trunc('day', o.created_at) AS day,
count(*) AS orders,
count(e.order_id) AS purchase_events
FROM orders o
LEFT JOIN analytics.purchase_completed e USING (order_id)
WHERE o.created_at >= now() - interval '30 days'
GROUP BY 1 ORDER BY 1;
Trace: a day showing 4,000 orders and 0 events is a hard break. A rename or a dropped call. A day showing 4,000 and 3,600 is 10% loss, which points at a client-side drop or a race, not a rename. Those two need different fixes, and the query tells you which you have before anyone argues about it.
Localize. Is it one platform, one app release, one property? Segment the same query by platform and app_version. A break that starts on a single release date is a regression; one that's flat across versions is a spec problem.
Fix forward, then backfill. The engineer patches the emitter; I confirm the fix in staging with the same reconciliation query, then we backfill historical events from the source of truth where the join key exists. Where it doesn't, the data is gone and I say so rather than interpolating.
Trade-offs I'd put on the table:
- Client-side vs server-side. Server-side events from the order service can't be blocked by ad blockers and survive UI refactors, but they miss UI-only facts (which variant, which button) and couple analytics to the service. For revenue events I'd emit server-side and join client context by a shared ID; for interaction events, client-side is fine and lossy by nature.
- Blocking releases on tracking tests. Gating every release on an event test catches regressions but adds friction and slows shipping. I'd gate only a small set of contract events (revenue, signup) and monitor the rest.
- Shipping with caveats. If the fix is weeks out and the loss rate is stable, you can publish with a correction factor or a caveat. That's a call to make explicitly with the data consumer, not silently.
Failure modes and how each shows up:
| Failure | Detection |
|---|---|
| Renamed/dropped event | daily volume vs 4-week median; zero or step change |
| Duplicate events from retries | distinct order_id count < row count |
| Missing property | null-rate per property rises |
| Late/clock-skewed events | event timestamp vs server receipt time gap |
The rename case is the classic: a checkout redesign shipped purchase as purchase_completed, conversion read zero for four days. The reconciliation query above catches that on day one, because orders kept flowing while events didn't.
So: reconcile to find the break, localize it, fix and backfill, and only then argue about the tracking plan.
Curated: · Written: · Reviewed:
Q&A-99When should an analysis move out of a spreadsheet?
The rule: move when the analysis has to be reproducible, not when it gets big.
Size is a symptom. The real trigger is that someone will rerun this on new data and needs the same answer, or someone will ask how a number was produced and the honest answer is "a sequence of clicks I can't replay."
| Signal | Stay in the sheet | Move to SQL / Python |
|---|---|---|
| Rerun cadence | one-off | weekly, monthly, per-customer |
| Volume | a few thousand rows | approaching a hard limit |
| Steps | every step visible in a cell | copy/paste, hand-typed constants, sort-and-delete |
| Audience | you, once | anyone who asks how the number was made |
Any one of those is enough. The volume row is the weakest on its own: Excel caps a sheet at 1,048,576 rows × 16,384 columns (XFD), and Google Sheets caps a file at 10 million cells, so you usually hit a limit by silently losing the tail rather than by getting an error.
Worked example. A monthly revenue rollup: 12 monthly extracts at ~90,000 rows each. 12 × 90,000 = 1,080,000 rows, which is 31,424 rows past Excel's 1,048,576 limit. The analyst pastes into two sheets and pivots only the first; December is gone and nothing errors. In SQL the same rollup is one SELECT, and the row count becomes an assertion you can check: SELECT COUNT(*) FROM monthly_revenue should return 1,080,000.
Failure modes that actually bite.
- Approximate-match lookups.
=VLOOKUP(A2, Rates!$A$2:$B$100, 2, TRUE)(theTRUE, or the omitted fourth argument) tells Excel the first column is sorted ascending and to return the largest value ≤ the lookup. IfRates!Aisn't sorted, you get a plausible wrong rate with no error.XLOOKUPwithout a match mode has the same trap. - Ranges that don't grow. Insert rows above a
SUMrange and the new rows fall outside it. The total is wrong and looks fine. - Precision loss on identifiers. Excel keeps 15 significant digits, so a 16-digit account number typed into a cell has its last digit replaced with a zero. Join on that column and you silently merge or drop accounts.
- Stale cached values. A sheet that references an external workbook keeps the last value it read when the source is unavailable.
Detection, not vibes. Row counts before and after every step, plus a reconciliation total that must tie to a source system. If you can't state the expected row count at each stage, you can't tell a silent drop from a clean run.
When to stay. Ad-hoc exploration, a one-off join of two small tables, a stakeholder-facing one-pager. Spreadsheets are a good output layer and a bad pipeline. Keep the sheet as the view; move the transformation behind it: SQL or dbt if the data already lives in a warehouse, pandas or Polars if it's files on disk, a BI tool if the output is a recurring dashboard.
The test. Not "is it big" but "can I rerun this next month and get the same answer, and can someone else check my work?" If no, it has already outgrown the spreadsheet.
Curated: · Written: · Reviewed:
Q&A-96How would you describe the difference between a data analyst and a data scientist?
The cleanest line is the question and the deliverable, not the tooling. A data analyst answers questions about data you already have (what happened, why, and what to do about it) and the deliverable is a decision: a readout, a recommendation, a dashboard someone acts on. A data scientist builds something that has to generalize to inputs nobody has seen yet (a prediction, a ranking, a causal estimate) and the deliverable is a system that keeps producing outputs after the analysis is finished.
That difference in deliverable drives everything else. The analyst's output is consumed by a human decision-maker, so the hard parts are framing the question, getting the metric definition right, and communicating uncertainty honestly. The scientist's output is consumed by code or by a process, so the hard parts are sampling, leakage, validation, and drift: the model has to be right on data it was not trained on, and stay right as the world moves.
| Axis | Data analyst | Data scientist |
|---|---|---|
| Question | What happened, what should we do | What will happen, what would happen if |
| Data | Already collected, observed | Needs labels, train/serve split |
| Deliverable | Decision memo, readout, dashboard | Model, scoring service, ranking |
| Failure that matters | Wrong metric, misleading comparison | Leakage, drift, miscalibration |
| Upkeep | Re-run when the question changes | Monitor, retrain, own the pipeline |
A worked example makes the split concrete. Say monthly churn moved from 4.1% to 5.3% (hypothetical figures). The analyst pulls cohorts, finds the jump concentrated in users on the new pricing tier (n ≈ 180k), checks it isn't a tracking break, and recommends grandfathering that cohort: two weeks, no model, the output is a decision. The scientist takes the same problem and builds a churn-propensity model that scores 2M users nightly so the top decile gets a retention offer; that needs a held-out AUC around 0.8, calibration checks, a monthly retrain, and a drift alert on the feature distribution. Same business problem, different deliverable and different failure surface.
When does the line collapse? At a 15-person startup one person does both, and the title is noise. At a large company a product analyst who designs experiments and runs causal inference is doing work indistinguishable from a scientist's: except they don't own a model in production. The deciding criterion is whether the deliverable must generalize to unseen inputs and be maintained as a running system. If yes, it's scientist work. If the output is a decision on observed data, it's analyst work.
The failure modes follow from getting that wrong. Hire a scientist for decision support and you often get a model nobody uses while the team still decides from dashboards: detectable by checking whether any decision traces back to the model's output. Hire an analyst for a prediction role and you get leakage and no monitoring: detectable when offline metrics look great and production performance quietly degrades with no drift alert firing. Ask which of those two the team actually needs before you describe the roles.
Curated: · Written: · Reviewed:
Q&A-97Tell me about an analysis you did that changed a decision.
Worked example. The figures are illustrative; the shape of the reasoning is what a panel is scoring.
The answer, as you'd say it
"Six weeks into a quarter, p99 write latency on our primary Postgres had gone from 40 ms to 180 ms. There was an approved RFC to shard the database, about two engineer-quarters of work, and I'd been asked to size the shard split. I asked a different question first: what are the writes?
I pulled the top statements out of pg_stat_statements, ranked by calls × mean_exec_time, and one row dominated.
| Statement | calls/hour | mean exec | share of write time |
|---|---|---|---|
UPDATE ledger SET state=$1 WHERE id=$2 | 1.2M | 0.26 ms | 78% |
INSERT INTO events ... | 180k | 0.40 ms | 18% |
UPDATE accounts SET balance=$1 ... | 60k | 0.10 ms | 1.5% |
| everything else | — | — | 2.5% |
That top row is a single-row update issued in a loop by a background reconciliation job. 1.2 million calls an hour, 78% of all write time. I batched it to 500 rows per statement: 1.2M / 500 = 2,400 calls an hour, and p99 came back to 45 ms within a day. We cancelled the sharding project."
What the interviewer probes next
How did you rule out the obvious competing cause? Connection-pool saturation was the first hypothesis, because it produces the same symptom. I checked pool wait time, flat at about 3 ms, and pg_locks showed no long waits. The writes were slow in aggregate, not slow individually, which points at volume, not contention.
What broke when you batched? Blast radius. One failed statement now loses 500 updates instead of one. We added an idempotency key per batch and a dead-letter table, and retried per batch rather than per row. That was the part I underestimated.
What would you do differently? I spent two days sizing the shard split before questioning the premise. The right move was to ask "what are the writes" on day one. I also hard-coded the batch size; it should have been a config knob so we could tune it without a deploy.
Why this story and not another
The question is "an analysis that changed a decision," so the test is whether a decision actually moved. I have a second story, a dashboard I built that nobody used, and I don't tell it, because no decision changed and the panel can't score impact. The criteria I use to pick: a decision that was reversed or cancelled, a number before and after, and a moment where I disagreed with the plan I was handed. If a story has all three, it survives follow-up questions. If it only has a finding, it doesn't.
Failure modes in telling it
- Ending on the analysis instead of the decision. "I found the hot query" is not the answer; "we cancelled the sharding project" is.
- No before/after number. 180 ms → 45 ms is the part a panel repeats back to the hiring committee.
- Claiming credit you can't defend. If a teammate wrote the batching change, say so and describe what you owned: the analysis and the recommendation.
Rehearse it to under two minutes and stop at the decision. The follow-ups are where the depth shows, not the opening.
Curated: · Written: · Reviewed:
Q&A-50You get a take-home dataset and a vague prompt. How do you structure your submission?
Treat the submission as a decision document, not a data dump. Two or three reviewers will each spend maybe 20–30 minutes on it, against a rubric that mostly asks whether the recommendation follows from the evidence. Your job is to make the answer findable in the first paragraph and defensible in the rest.
Default structure, with illustrative time budgets for a ~6–8 hour take-home:
| Section | Budget | What goes in it |
|---|---|---|
| Recommendation | 1 paragraph, ~120 words | The answer, the headline number, your confidence |
| Data checks and assumptions | 30–45 min | Row counts, nulls, dedup, date coverage, what you assumed and why |
| Supporting analyses | 3–4 charts | Only the ones that change the recommendation |
| Limitations and next steps | 3 bullets | What would falsify the answer, what you'd do with another day |
That is a default, not a law. Two trade-offs decide the shape.
Memo vs notebook. A memo front-loads judgment; a notebook front-loads work. The deciding criterion is who reads it. If the panel is PMs and a hiring manager, lead with the memo and attach the notebook as an appendix: they will not run your cells. If the prompt says "explore" or the role is research-heavy, invert it: notebook first, one-page summary on top. What you must not do is submit a notebook with no summary; that is the most common rejection.
Breadth vs depth. More analyses feel safer and usually hurt. The criterion: does this analysis change the recommendation? If no, cut it or move it to an appendix. Three charts that each move the answer beat twelve that decorate it.
Failure modes I'd watch for in my own draft:
- No stated recommendation. A candidate I reviewed submitted 34 charts and no conclusion; the panel could not say what was recommended, and it was rejected on that alone.
- Assumptions buried. If a reviewer has to reverse-engineer your join or your date filter, they can't grade the reasoning.
- A result that contradicts the recommendation and is left unaddressed.
Worked example of the opening paragraph:
Recommendation: churn is concentrated in month-1 accounts on the self-serve plan (62% of cancellations, n=1,410). I'd test a guided onboarding for that cohort before touching pricing. Confidence: medium. The effect holds across two quarters but the sample is one product line.
That paragraph names the finding, the number, the action, and the confidence. Everything after it is support.
Before submitting, run one check: hand the first paragraph to someone who hasn't seen the data and ask them to state your recommendation back. If they can't, the structure failed regardless of how good the analysis was.
Curated: · Written: · Reviewed: