Interview guides · Boston red sox

Boston red sox Data Scientist Interview Questions & Guide 2026

Case practice for Boston red sox data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

A Data Scientist within the Baseball Analytics department at the Boston red sox operates at the intersection of high-level quantitative research and critical organizational decision-making. You are not just building models; you are providing the empirical foundation for player acquisition, development, and on-field performance optimization. Your work directly influences how the Boston red sox identify talent and craft winning strategies.

If the team owns experimentation, expect depth past a two-sample test: minimum detectable effect and its roughly inverse-square-root dependence on sample size (holding power, significance level and variance fixed), variance reduction from pre-period covariates, interference between units, and when a sequential design is the right call.

Focus areas

Reconstruct balances from a signed ledger
Design matchmaking tests that survive interference
Audit faucet and sink flows per currency

Overview

This role is highly collaborative and requires a unique blend of technical rigor and communication finesse. You will work alongside scouts, coaches, and front-office leadership to translate complex statistical findings into actionable insights. Success in this role requires a deep passion for baseball combined with the ability to maintain the Boston red sox standard of excellence in a fast-paced, high-stakes environment where every data point contributes to the goal of building a championship-winning team.

The Boston red sox interview process

Boston red sox candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Technical Screening

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

Whether an ambiguous term becomes a specific column and filter before any computation happens
Whether you read the schema for keys and cardinality rather than only for column names
Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Deeper-Dive Rounds

Rounds outside the standard loop often open with something deliberately under-specified: a loose business problem, an open question about a product area, a dataset described in one sentence. The common failure is surveying, listing six plausible approaches and committing to none of them. The thing that separates a strong answer is scoping out loud. State what you are treating as the goal, name the metric you would move, say what you are choosing not to do and why, then take one path through to an actual answer. An interviewer can follow you down a narrow path. Nobody can grade a menu.

What to demonstrate

Whether you turn an ambiguous prompt into a stated question with a measurable outcome before doing any work
The judgement visible in what you cut, and whether you say why you cut it rather than silently dropping it
Whether you land on a concrete recommendation with its caveat attached, rather than an unranked set of options

How to prepare

Take three vague prompts, such as 'is this feature working', 'why did retention drop', and 'should we expand into a new segment'. For each, write one sentence of goal, one primary metric with its window, and two things you are explicitly not doing.
Practise giving the recommendation first and the reasoning second, in five minutes. Loosely defined rounds are usually time-boxed, and an answer that arrives last often does not arrive.
Keep a running assumption list as you talk, on paper or in the shared doc, so the interviewer can challenge one assumption instead of your whole answer.

Final Behavioral Interview

Most of the weight in this round sits on the disagreement questions. Data work routinely produces an answer someone senior did not want, and the interviewer is trying to learn what you do in that hour. Both failure modes are common: folding as soon as a director pushes back, and treating the pushback as ignorance to be corrected with a better chart. A strong answer usually contains a specific thing the other person knew that you did not, and describes how you found out whether it changed the conclusion.

What to demonstrate

Whether you can state the other side's argument accurately before you explain why you disagreed
What you treated as evidence during the disagreement, such as a rerun under their assumption or a holdout check, rather than persuasion technique
Whether you distinguish being overruled from being wrong, and can give an example of each

How to prepare

Write out one disagreement where you turned out to be wrong, and say what in the data misled you. Candidates prepare the story where they were right, and the follow-up asks for the other one.
For your main disagreement story, be ready to say what result would have made you drop your position. If no such result exists, you were not arguing from the data.
Practise stating the opposing position out loud in one sentence the stakeholder would accept, then continue the story.

Where candidates lose points

Testing revenue with a difference in means on a heavy-tailed spend distribution.

Player spend is approximately log-normal with a long Pareto-like tail, so the variance of the sample mean is dominated by a few accounts and the central limit approximation converges slowly at realistic sample sizes. A t-test on raw ARPDAU can flip sign when one account buys a large currency pack. Winsorising or capping at a pre-registered percentile, or testing conversion and conditional spend separately, fixes the variance problem, but note the cost: capping biases toward zero exactly when the true effect lives in the tail, so state the cap before you look.

Booking revenue at gross, on purchase day, for cohorts of different ages.

Revenue matures: the platform fee is known immediately, but refunds and chargebacks land over weeks, and later purchases keep accruing to the same cohort. Comparing a two-week-old cohort with a one-year-old cohort therefore compares two different maturities, and any trend chart built that way slopes in the direction of cohort age. Quote cohort revenue at a fixed age, and exclude cohorts that have not reached it rather than extrapolating them.

Treating a non-significant result as proof of no effect

Say whether the confidence interval excludes the effect sizes you would have cared about. If it does not, the honest reading is that the test was underpowered, so report the minimum detectable effect the design could have found and what sample size would resolve it.

Defining the cohort on a post-treatment condition

Ask how rows entered the table. Filtering on something that treatment itself influences, such as users who finished onboarding or accounts still active at ninety days, breaks comparability between arms; define the population at an entry point that precedes exposure and keep everyone in it.

Questions & practice

Explain the difference between frequentist and Bayesian approaches in the context of player evaluation.

Medium · statistics and probability

Approach

Say what the estimate is of, and over what population it generalises.
Write down the assumption the method needs before you use the method.
Translate the result into the decision it informs, in one plain sentence.

Follow-up

Which assumption here is most likely to be violated in practice?
How would you explain this result to someone who does not know statistics?

How do you account for survivor bias in historical baseball data?

Medium · statistics and probability

Approach

Sanity-check the answer against a simple bound or a simulated case.
Write down the assumption the method needs before you use the method.
Say what the estimate is of, and over what population it generalises.

Follow-up

What sample size would you need to detect an effect half this size?
How would you explain this result to someone who does not know statistics?

Give an example of how you have demonstrated humility and honesty when a model you built did not perform as expected.

Medium · machine learning and modelling

Approach

Check what information would not exist at prediction time, and exclude it.
Frame the prediction: the label, the moment of prediction, and the action it triggers.
Pick an evaluation metric that matches the cost of each error type, not a default.

Follow-up

How would you choose the decision threshold, and who owns that choice?
What would you monitor after launch to know the model is still valid?

Discrete hazard of churn indexed by matches played

Hard · survival analysis · censoring · churn

Given matches (player_id, started_at) for one install cohort, sessions (player_id, session_start_ts), and a data cutoff timestamp, define churn at match k as no session starting in (t_k, t_k + 14 days], where t_k is the start of that player's k-th match. Build the discrete hazard h(k) and the survival curve S(k) for k = 1 to 50. A player-index enters the risk set at k only if the player has not already churned before k and t_k is at or before cutoff minus 14 days. Return k, risk_set, churned, h and S, and name the two largest hazard spikes.

Approach

Build the match index yourself: sort matches by (player_id, started_at) and set k = groupby('player_id').cumcount() + 1. Never take a stored index without verifying it is dense per player, because one gap shifts every downstream k for that player.
Decide churn per (player, k) without a cross join. Sort each player's session timestamps once and use np.searchsorted to ask whether any session falls in the half-open window (t_k, t_k + 14d]. A merge of matches to sessions is quadratic in the busiest players and is where this exercise usually dies.
Keep observability separate from the event. A player-index is usable at k only when t_k is at or before cutoff minus 14 days; a player whose 30th match was yesterday contributes to k = 1 through 29 and then leaves. This is right-censoring on the match axis, and dropping those players outright instead of truncating them biases the entire curve toward whoever plays fastest.
Compute h(k) = churned_at_k / risk_set_k and S(k) as the cumulative product of (1 - h(j)) for j up to k. The product form is what makes the censoring handling count; a naive share of the original cohort still alive divides by a denominator containing players who could never have been observed at k.
Read the spikes against the loop rather than the calendar: a bump at a given k usually maps to a difficulty wall, the end of a tutorial reward track, or the point where a faucet stops paying. Name the k values, then name the query that would confirm each one, rather than asserting a cause.

Worked solution (40 min)

Derive t_k per player by sorting and using cumcount, then truncate each player's indices at the last k satisfying t_k at or before cutoff minus 14 days.
For each surviving (player, k), use a per-player searchsorted over session timestamps to decide whether a session falls in the 14-day window, and take each player's first churn index as their exit.
Aggregate risk_set and churned counts by k for k = 1 to 50, then compute h(k) and the cumulative-product S(k).
Rank h(k) and report the two largest, with the k values and the local shape around them.

Expected result: A 50-row frame of k, risk_set, churned, h and S, with risk_set non-increasing in k, S non-increasing in k, and every churned player counted at exactly one k.

Verify it yourself

risk_set at k = 1 equals the number of players whose first match is at least 14 days before the cutoff, which is smaller than the cohort size.
S(k) from the cumulative product agrees with a naive survivor share only when no player is censored; the divergence between the two is a direct readout of how much censoring you are handling.
Each churned player appears in exactly one k, so the churned column sums to the count of distinct churned players.
Moving the cutoff a week earlier shrinks every risk_set but leaves h(k) roughly unchanged for small k; a large shift there means the censoring rule is wrong.

Follow-up

Someone reports that players who reach match 50 retain four times better. Rewrite that as a claim you would be willing to defend, and say what it costs you to do so.
Your definition retires a player who keeps logging in but stops playing matches. Is that churn? What does including or excluding them do to the curve?
Add a time-varying covariate for whether the player was in a party at match k. What changes about the estimator, and what can you now claim that you could not before?

Using SQL window functions, how would you calculate a rolling average of a player's performance over their last 50 games?

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
Check whether any join is one-to-many before aggregating, or the sums inflate.
State the window function and its partition and ordering out loud before writing it.

Follow-up

What breaks if events arrive late or out of order?
How would you verify this result without re-running the same query?

Reconstruct running balances and find the first ledger divergence

Medium · window functions · reconciliation · ledger

fct_currency_ledger has ledger_id, player_id, occurred_at, currency_code, delta_amount (signed), balance_after and is_reversal. balance_after is the balance the game server recorded at write time. Recompute the running balance per player per currency from delta_amount alone, compare it to balance_after row by row, and return the first row where the two disagree for each affected (player_id, currency_code), with both values and their signed difference. Include reversal rows: a reversal is a real movement of currency. Assume occurred_at can tie within a single player.

Approach

Compute the running total with SUM(delta_amount) OVER (PARTITION BY player_id, currency_code ORDER BY occurred_at, ledger_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Partitioning by player alone is wrong because balance_after is tracked per player per currency.
Be clear about what the ROWS keyword is doing here, because it is less than it looks. Under the default RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the frame ends at the last peer of the current row, where peers are rows equal on every ORDER BY key. ledger_id is the table's primary key, so with (occurred_at, ledger_id) in the ORDER BY every row is its own peer group and RANGE returns exactly the same numbers as ROWS. Write ROWS anyway: it states the intent and skips peer detection. Do not present it as the thing that makes the query correct.
The frame becomes load-bearing only when you drop the tiebreak and order by occurred_at alone, which is the mistake this data is arranged to punish. Two entries written in the same second are then peers, and the default RANGE hands both of them the cumulative total as of the end of the tie group. Only the last row of a tie group is entitled to that total, so every earlier row of the tie runs ahead of its own balance_after and is reported as a divergence on a ledger that is intact. Switching to ROWS while still ordering by occurred_at alone does not fix it either: the sum advances one row at a time, but the order within the tie is unspecified, so which row carries which running total can change between runs on identical data. The unique tiebreak is the fix; the frame keyword is documentation.
Do not exclude is_reversal rows here. A reversal moves currency, so dropping it makes the recomputed balance diverge by construction. This is the opposite of the exclusion you apply when measuring faucet and sink volume, and knowing why the two differ is the point.
Flag rows where running_balance <> balance_after, then take the earliest per partition with ROW_NUMBER() OVER (PARTITION BY player_id, currency_code ORDER BY occurred_at, ledger_id) over the flagged set and keep rn = 1. Use the same ordering as the running sum, or the row you call first is not the row the sum diverged on.
Report the signed difference rather than a boolean. A constant offset from the first divergence onward points at one missing or duplicated entry; a difference that grows points at a systematic sign or unit error.

Worked solution (30 min)

Confirm ledger_id is unique before relying on it as a tiebreak, then write CTE one: the ledger columns plus the running SUM with an explicit ROWS frame and the (occurred_at, ledger_id) ordering.
CTE two: filter to running_balance <> balance_after and add ROW_NUMBER over the same partition and ordering.
Select rn = 1, outputting player_id, currency_code, ledger_id, occurred_at, running_balance, balance_after and (running_balance - balance_after) as drift.
Validate on one known-good player by selecting their full ordered history and confirming running_balance tracks balance_after to the last row.
Count affected partitions against total partitions so you can size the problem before reporting it.

Expected result: At most one row per (player_id, currency_code) that ever diverges, being the earliest such row, carrying ledger_id, occurred_at, the recomputed running_balance, the recorded balance_after and their signed drift. Partitions that reconcile cleanly return no row at all.

Verify it yourself

On any partition with no divergence, the final row's running_balance must equal its balance_after exactly.
Swapping ROWS for the default RANGE while keeping the (occurred_at, ledger_id) ordering must change nothing at all. If it does, ledger_id is not unique within the partition and the ordering is not total.
Re-run the running sum ordered by occurred_at alone under the default RANGE, against a player who has two entries sharing a timestamp: every row of that tie except its last should now surface as a false divergence, and the earliest of them is what the rn = 1 filter returns. If the output is unchanged, your sample has no ties inside a partition and the tiebreak is untested, so find or construct one before trusting the result.
The number of distinct (player_id, currency_code) partitions in the source must be far larger than the number of rows returned. A return count close to the partition count means the recomputation is wrong for everyone, not that the ledger is broken.

Follow-up

Every divergence starts at the same delta value across thousands of players. What does that pattern point at, and which column do you group by to confirm it?
How would you rewrite this to run incrementally each night against yesterday's rows instead of rescanning every player's full history?
A player's earliest ledger row has balance_after = 500 and delta_amount = 500. What assumption about starting balances does your query make, and where does that assumption break?

How do you balance long-term player potential with short-term team performance needs when designing evaluation models?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Fix the population and the time window before naming any metric.
Decompose the metric into the rates that drive it, and say which one you would check first.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
What would you do if the primary metric and the guardrail moved in opposite directions?

If we notice a sudden, unexplained drop in a specific player's performance metric, how would you investigate the root cause?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
State what result would change your recommendation, so the answer is falsifiable.
Fix the population and the time window before naming any metric.

Follow-up

What would you do if the primary metric and the guardrail moved in opposite directions?
Which segment would you cut first, and what would that rule out?

How would you design a metric to evaluate the effectiveness of a player development program?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Name one primary metric, then the guardrail that stops it being gamed.
Fix the population and the time window before naming any metric.

Follow-up

What would you do if the primary metric and the guardrail moved in opposite directions?
How would you detect that the metric is being gamed rather than genuinely improving?

How do you determine statistical significance when sample sizes are inherently limited by a 162-game season?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Say whether units interfere with each other, and switch design if they do.
Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

How would you handle interference between treated and control units?
What would you conclude if the result is positive but the test is underpowered?

How would you design an experiment to test if a specific adjustment in a pitcher's mechanics leads to improved outcomes?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Decide the analysis before seeing data, including how long it runs and when you look.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

How would you handle interference between treated and control units?
What would you conclude if the result is positive but the test is underpowered?

Trade queue time against match quality with an explicit exchange rate

Medium · guardrails · order statistics · selection bias · trade-offs

Matchmaking will widen its skill-rating tolerance in apac ranked to cut queue times. You have fct_match_participant (queued_at, started_at, matchmaking_wait_seconds, game_mode, server_region, result, disconnected, skill_rating_before, skill_rating_after). Define the primary metric and the guardrail, then state the exchange rate you would pre-register: how many seconds of p95 wait reduction justify one percentage point of abandon rate. Explain why p95 matchmaking wait is measured only over queue entries that became matches, and what that conditioning does when the change alters which entries form a match.

Approach

Refuse the framing in which wait time is the goal. The decision metric is daily core-loop players in apac ranked. Wait time and match quality are both inputs to it, and the change is worth shipping only if the net moves.
Make the operational primary p95 matchmaking_wait_seconds by server_region and game_mode over a trailing 24 hours, and pair it immediately with the queue-abandonment rate it excludes: entries that never produced a started_at. If queue entries without a formed match are not logged anywhere, say so out loud, because the primary metric is then unfalsifiable and the first deliverable is the instrumentation rather than the test.
Explain the conditioning precisely. p95 is an order statistic over queue entries that succeeded, so widening tolerance changes the composition of that population, not only its level. A configuration that lets the easy-to-match players through faster while the hardest ones time out can improve p95 and serve fewer people.
Guardrail: match abandon rate, counting rows with result = 'abandon' or disconnected = true over all rows with started_at in the window, cut by region and mode, plus a match-quality measure such as the within-match standard deviation of skill_rating_before aggregated to a daily median.
Derive the exchange rate rather than asserting it. Estimate from historical variation how much one point of abandon rate costs in next-day core-loop players, and how much one second of p95 costs in queue abandonment, then convert both into the same unit and state the threshold before the readout. A defensible answer is a number with its derivation attached.
Handle interference. Queue population is shared, so an individually randomised arm changes the pool the control arm matches into. Randomise by region and mode cluster, or switchback on time slices with a burn-in long enough to clear carryover, and compute standard errors at the slice level.

Worked solution (30 min)

Write the decision metric and the two operational metrics with their windows and cuts.
Check whether unmatched queue entries exist in the data. If fct_match_participant carries rows only for matches that formed, name the missing event and treat its absence as a blocker rather than a caveat.
Define the match-quality guardrail concretely, for example the standard deviation of skill_rating_before within a match, aggregated to a daily median by region and mode.
Derive the exchange rate: cost of one abandon-rate point in next-day core-loop players, cost of one second of p95 in queue abandonment, both from historical variation, then fix the threshold.
Choose the randomisation unit, region-mode cluster or time slice, and state the variance cost you are accepting.

Expected result: A decision metric of daily core-loop players in the affected region and mode; p95 matchmaking wait paired with queue-abandonment rate as the primary; match abandon rate and an intra-match skill-spread guardrail; a pre-registered exchange rate with its derivation; and cluster or switchback randomisation because the queue pool is shared across arms.

Verify it yourself

Your primary is stated as a pair, because an order statistic over successes alone can be improved by producing fewer successes.
Your exchange rate has units on both sides and a derivation, not just a number.
Your design does not randomise individual players into different matchmaking rules inside the same queue.

Follow-up

p95 wait falls 22 seconds, abandon rate rises 0.4 points, and daily core-loop players in apac are flat. Ship or not?
Your exchange rate came from an observational fit of abandon rate against next-day play. What confounds that fit, and how far should it move your threshold?
How long a burn-in would you set on the switchback, and what evidence sets that length?

Abandon rate spiked with a regional incident and a flag change

Hard · compound causes · logging change · segment attribution

Match abandon rate, defined as rows with result = 'abandon' or disconnected = true over all rows with started_at in the window, rose from 4.1% to 7.8% in a week. Two things happened: a two-day server incident in one region, and a client release on one platform. fct_match_participant carries match_id, player_id, started_at, result, disconnected, server_region, game_mode; fct_session carries player_id, session_start_ts, platform, app_version. Attribute the 3.7-point rise across both causes and a residual.

Approach

Split the numerator into its two disjoint terms before any segmentation: rows with result='abandon', and rows with disconnected=true and result <> 'abandon'. The metric is an OR of two signals that can move independently, and a change in flag semantics moves only one of them while a genuine outage moves both.
Isolate the regional incident with a two-dimensional cut of server_region by day. A real incident is bounded in both dimensions: it appears in one region, on the days the incident ran, and disappears afterwards. Compute its contribution as the region's share of weekly matches times its abandon-rate delta times the fraction of the week affected.
Isolate the client change by joining participants to sessions on player and time to attach platform and app_version, then cutting by version. A logging or semantics change produces a step at the version boundary and a weekly aggregate that tracks the version adoption curve, not the calendar. Compute its contribution as the platform's share of matches times the new version's share of that platform's matches times the rate delta on the new version.
Discriminate the flag change from a real disconnect problem using corroborating signals that a semantics change would not move: fct_session ended_reason='crash' rate on the new version, matchmaking wait, and the result='abandon' term on its own. If only the disconnected flag moved, the flag's meaning changed.
Add the two contributions and report the residual explicitly. A residual that is small relative to the two named terms is the honest end of the analysis; a large one means a third cause is present and should be named as unexplained rather than absorbed into either story.
Recommend the metric repair: report the result='abandon' term separately so the series stays comparable across client versions, and either backfill a version-consistent disconnected definition or mark the discontinuity on every chart built on it.

Follow-up

The two causes overlapped in time. How do you keep them from stealing each other's attribution, and what does the interaction term look like?
If the residual were 1.5 points instead of 0.3, what would you look at next?
How do you decide whether to restate the historical series or to annotate the break, and who needs to agree?

Suggested practice checklist

For someone who has spent the last year in notebooks, dashboards or modelling work and has not written raw SQL under time pressure. The first four days rebuild query fluency against a fixture you control and can verify by hand; the last three attach that fluency to the rest of the loop.

Day 1 — Build a fixture you can check answers against

Create a local Postgres or SQLite database with four tables (users, sessions, events, orders) holding roughly 200 rows you generated yourself, so you know the contents well enough to predict every result.
Deliberately seed the cases that break queries: a user with no sessions, a session with no events, two orders sharing a timestamp, a NULL in one join key, and one duplicated user row.
Before writing any SQL, hand-compute five answers on paper (how many users placed at least one order, median orders per ordering user, and three others) and save them as the ground truth for the week.

Deliverable: A one-command seed script plus a text file of five hand-computed answers to grade every later query against.

Day 2 — Joins, filters and NULL semantics

Answer "which users have no orders" three ways (LEFT JOIN with IS NULL, NOT EXISTS, NOT IN) and confirm that the NOT IN version returns zero rows once the subquery contains a NULL, because the comparison is never TRUE.
Reproduce the LEFT JOIN that silently collapses to an inner join by putting a right-table predicate in WHERE, then fix it by moving the predicate into the ON clause, and record both row counts.
Create a fan-out bug on purpose by joining orders to order_items and summing the order total, then correct it with a pre-aggregated subquery and explain in one line which table changed the grain.

Deliverable: One annotated .sql file holding the three join traps, each with the wrong result and the corrected result side by side.

Day 3 — Window functions and frames

Write three window queries against the fixture: a running order total per user, the rank of each order within its user by value, and the day gap to that user's previous order, then check each against the day-one ground truth.
Run ROW_NUMBER, RANK and DENSE_RANK over a column containing ties, print all three side by side, and write one sentence on when each is the correct choice.
Switch one query from the default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which is what you get when ORDER BY is present and no frame is written) to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and explain why the output differs only when the ORDER BY column has duplicates.

Deliverable: Three verified window queries plus a short note explaining the RANGE versus ROWS difference in your own words.

Day 4 — The four analytical query patterns

Write a monthly retention grid: first order month per user, then months-since-first as the column, and verify that month zero equals the cohort size exactly.
Sessionize the events table under a 30-minute inactivity rule using LAG plus a cumulative sum over a new-session flag.
Build a four-step funnel that counts distinct users rather than events at each step, and state the rule you applied to a user who reaches step three without ever logging step two.

Deliverable: One file with the retention, sessionization and funnel patterns, each carrying a one-line note on the assumption it bakes in.

Day 5 — Write SQL the way you will have to write it live

Set a 12-minute timer and solve three medium prompts in a plain editor with no execution and no autocomplete, then run them and tally syntax errors separately from logic errors.
Narrate one solution aloud while writing it, stating the grain of each intermediate result (one row per user, one row per user-day) before you type its body.
Rewrite your slowest solution as a CTE chain where every CTE name states its grain, and time yourself re-solving it from blank.

Deliverable: A recording of one narrated solution plus an error tally that separates syntax from logic.

Day 6 — One day for everything that is not SQL

Write the preconditions of the two-sample t-test from memory, then check them: independent observations, and a difference in means whose sampling distribution is approximately normal, which at large sample sizes follows from the central limit theorem rather than from normality of the raw values.
Write the difference between an odds ratio from logistic regression and a relative risk, and state the condition under which the two are close (low outcome prevalence).
Prepare a 90-second answer to "how would you know this model is any good" that names the metric, the baseline you would beat, and the cost of the errors you care about.

Deliverable: One page of notes covering test preconditions, the odds-ratio caveat and the model-quality answer.

Day 7 — Full loop rehearsal

Run a 45-minute mock with someone willing to interrupt: 20 minutes of SQL, 15 minutes defining a metric, 10 minutes on a past project.
Re-solve from blank the two queries you were slowest on this week and compare the times against day five.
Write a five-line answer to "walk me through a project" that puts a number in the first sentence and names the decision the work changed.

Deliverable: Mock feedback notes plus a timed project narrative you can deliver without reading it.

Ambiguous requests are the test

Most of the questions in this section reduce to one thing: can you be handed a vague request and come back with something useful? Prepare an example where the ask was underspecified, you chose an interpretation, and you said out loud which interpretation you chose. Describing how you narrowed the question matters more than the technique you eventually used.

Describe a time you had to explain a complex statistical model to a non-technical stakeholder, such as a scout or coach.

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Close with what you would do differently, concretely.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

How did you know the outcome was caused by your change?
What did you decide not to do, and why?

Retract a shipped retention claim built on survivorship

Medium · survivorship bias · retraction · stakeholder trust · hazard modelling

Three months ago you published a memo showing that players who join a guild in their first week have 2.1x the D28 retention of those who do not. A feature team has since built a guild-invite prompt on the strength of it, shipping in nine days. You now see that guild status was measured at any point during the first week, among players who were still active at day 7, so the comparison conditions on the outcome it claims to explain. Write the retraction and describe the conversation you have with the feature lead first.

Approach

Get the corrected number before you send anything. Re-anchor: restrict to players with at least one session in the day-7 elapsed-hour bracket, fix guild status as of that anchor timestamp, and measure D28 forward from it. The corrected effect is usually positive and much smaller, and 'smaller' is a very different memo from 'wrong'.
Talk to the feature lead privately, first, with the corrected estimate in hand. A retraction that reaches them as a surprise in a group channel costs you the relationship you need for the next correction.
Structure the memo in decision order: what the claim was, what was mechanically wrong in one plain sentence, what the corrected estimate and interval are, and what decision changes as a result.
Keep 'the number was wrong' separate from 'the feature is wrong'. Selection bias means the estimate is uninterpretable, not that guilds do nothing. Propose the design that would actually answer it: randomise the invite prompt and measure from assignment, which also gives the team a readout they can defend.
Offer the hazard framing as the durable fix. Model churn with guild membership as a time-varying covariate so joining is not allowed to predict the survival it required.
Add one process change, such as an anchor-point check on any adoption-versus-retention claim, so the retraction buys something beyond an apology.

Follow-up

The corrected lift is 1.3x with an interval of 1.05x to 1.6x. Does the feature still ship in nine days?
How would you design the randomised version so the prompt itself, rather than guild membership, is what gets randomised?
What would you have to see in the hazard curve for you to believe the mechanism is real rather than another selection artefact?

Defend an unpopular readout on a live-ops event

Medium · stakeholder pushback · revenue maturity · pull-forward · economy health

A two-week live-ops event closed with gross bookings up 18% against the prior week. Your readout says it added no net revenue: summed price_usd_net in fct_iap_transaction over the 28 days spanning the event is flat against a matched window one release cycle earlier, and fct_currency_ledger shows premium-currency sinks falling while faucets from source_system = 'pass_tier' rose. The event owner disputes the readout in a review with their director present. Give the argument you would make in that room, and name the one piece of evidence you would concede.

Approach

Separate the disputed claim from yours. Gross bookings up 18% is true and you are not contesting it. Restate your finding precisely: net of platform fee and of refunds recognised to date, over a window long enough to contain the pull-forward, the number does not move.
Present the decomposition rather than the aggregate, because the aggregate is what the room is already arguing about. Split the delta into active player-days, paying conversion, and net spend per converting player-day. If conversion is flat and spend per payer spiked inside the event window then fell below baseline after, that is re-timing, not new demand.
Evidence the re-timing at player grain: take the player_ids who purchased during the event and chart their purchase timing in the four weeks before and after. A pulled-forward purchase shows as a deficit on the same accounts, not as a smaller cohort.
Bring the ledger in as the second, independent signal. Premium sinks fell while pass_tier faucets rose, so granted currency was banked rather than spent. Report the sink-to-faucet ratio per currency and the balance percentile curve, and exclude is_reversal rows and cs_grant so a correction cannot masquerade as a faucet.
State the limit of the design out loud before the owner does. There was no holdout, and a matched window still aliases the release calendar. Say which part of your conclusion is robust to that and which is not.
Close on a decision and a fix: run the next instance with a region-clustered holdout or a staggered start, so the argument is settled by design rather than by seniority.

Follow-up

The event owner says the banked currency will be spent next month, so the sink drop is timing too. How would you test that claim rather than argue about it?
If you could add exactly one instrumentation change before the next event, what would it be and what question does it close?
What would you have said if the net number had been up 3% with an interval spanning zero?