Interview guides · Harvard Medical School

Harvard Medical School Data Scientist Interview Questions & Guide 2026

Take-home style practice for data scientist candidates: a messy dataset, a fixed time budget, and an analysis you write up and defend.

The 60-second brief

The role of a Data Scientist at Harvard Medical School is pivotal in transforming complex data into actionable insights that drive medical research and enhance patient care. As a Data Scientist, you will leverage advanced analytical techniques, machine learning algorithms, and statistical modeling to address challenging medical questions, evaluate treatment outcomes, and optimize healthcare delivery processes. Your work will directly impact the development of innovative products and solutions that can lead to significant advancements in medical science and patient health.

Scope your preparation by the data you would actually touch, because the title will not tell you. A seat that lives in event logs and weekly readouts rewards fluency in aggregation and metric definitions; a seat that owns a model in production rewards fluency in train/serve skew, retraining cadence and drift monitoring. The fastest way to find out which one you are interviewing for is to ask what the team shipped last quarter and what it gets paged about.

Focus areas

Risk-adjust outcomes before comparing providers or panels
Build member-month denominators from overlapping coverage spans
Reconstruct drug exposure intervals from dispensing records

Overview

At Harvard Medical School, you will be part of a dynamic team collaborating with physicians, researchers, and other data professionals. The projects you will engage in are often characterized by large-scale datasets and intricate variables, offering both complexity and the opportunity for strategic influence. Whether you are analyzing genomic data or predicting patient outcomes, the insights you generate will inform clinical decisions and shape the future of healthcare.

This role is not only critical for advancing research initiatives but also provides an intellectually stimulating environment where your contributions can lead to meaningful changes in healthcare practices. Expect a blend of rigorous analysis, collaborative problem-solving, and a commitment to excellence that defines the culture at.

The Harvard Medical School interview process

Harvard Medical School candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Screening Interview

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Technical Assessment

This round decides whether someone can hand you a schema and a question and trust the number that comes back. Correctness under a clock is the bar, not clever syntax. The habit that separates strong from weak answers is checking the grain: after every join, know how many rows you expect and whether the count moved. Most wrong answers in this format are not wrong logic, they are a fan-out from a key that turned out not to be unique, or a filter applied before an aggregate when it belonged after. Say what you expect before you run it.

What to demonstrate

Whether your row counts survive each join, and whether you notice on your own when they do not
Deliberate handling of rows that fail to match, including whether the question needs an inner join or a left join with the non-matches kept and counted
Whether NULLs are treated on purpose, given that a NULL compares equal to nothing and that COUNT of a column skips it
Reaching a defensible answer inside the window instead of a refined one after it

How to prepare

Take a two-table schema, write a join that fans out on purpose, then fix it by collapsing the many-side to one row per key before joining. Repeat until the fix is reflex rather than recall.
Write a funnel as one query and print the distinct user count at each stage, then confirm each stage is a subset of the one above it rather than assuming it
Do a few timed runs in a plain text box with no autocomplete and no formatter, since assessment editors often have neither

Final Round Interviews

Where a loop ends with a senior leader, that conversation is rarely another skills test. The technical signal already exists by then, so the questions tend to open up: what you would look at first, where a metric you have heard about could mislead, what you would push back on. The decision being made is scope, which in practice means level and how much you would be trusted to own unsupervised. Treating it as a formality is the usual mistake. An open question late in the day is still being scored, and a vague answer reads as someone who has not run anything themselves.

What to demonstrate

Whether your view of the business has anything specific behind it, given that you are working only from what is public and are expected to say so
Whether the scope of work you describe owning matches the scope of the role, instead of sitting a level below it
Whether you can disagree with something concrete and stay useful about it, rather than agreeing with everything said in the room
Whether your questions are ones only this person could answer, as opposed to ones the recruiter already covered

How to prepare

Build one view you could defend for two minutes using only public information: what the funnel probably looks like, which metric likely drives decisions, and where that metric could mislead. Being wrong for a stated reason survives this round; having no view does not
Write down the largest piece of work you have owned from question to decision, who else touched it, and what you decided alone, then check that it reads at the level you are interviewing for
Prepare one thing you would want changed if you joined and phrase it as a question rather than a verdict, so it opens a conversation instead of closing one

Where candidates lose points

Pre-post evaluation on a high-cost or high-risk cohort

Cohorts selected on an extreme value of the outcome regress toward the mean on their own. Members identified as the top 1 percent of spend in one year spend far less the next, whether or not anyone intervenes, because the selecting year captured both chronic severity and one-off events. A pre-post design on such a cohort will report savings every time. A concurrent comparison group selected by the same rule in the same period, or a regression discontinuity at the selection threshold, is the minimum credible design.

Ignoring clustering by provider, facility, or site

Patients within a provider panel, and subjects within a trial site, are correlated on both case mix and practice pattern. Standard errors computed as if observations were independent are too narrow by roughly the design effect, 1 + (m - 1) * ICC, where m is the average cluster size. With 50 patients per provider and an intracluster correlation of only 0.02, variance is understated by about a factor of two, which manufactures significant provider differences out of noise. Cluster-robust errors or a random intercept per provider, and cluster-level randomisation when you design the test, are the corrections.

Reading experiment results before checking the arm split

Compare observed arm counts against the intended allocation ratio, not an assumed even split, and set the alarm far below the conventional 0.05: at 0.05 roughly one healthy experiment in twenty trips it, which is why sample-ratio checks usually run at p < 0.001 or stricter. The test's power scales with sample size, so it misses a real diversion on a small experiment and fires on an imbalance too small to move the estimate on a very large one. A flag means go find the assignment or logging fault before reading any outcome, not report a mismatch.

Analysing at a different unit than the one randomised

Say out loud what was randomised (user, device, account, cluster) and make the analysis unit match, or account for the clustering with cluster-robust standard errors, the delta method, or aggregation up to the randomised unit. Randomising users and then running a test over sessions understates variance and inflates the false-positive rate.

Questions & practice

Write a code snippet to perform linear regression on a dataset.

Medium · machine learning and modelling

Approach

Set a baseline first, so any model has something honest to beat.
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

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

Bootstrap a confidence interval for a ratio-of-sums PMPM

Medium · bootstrap · ratio estimator · resampling unit

You have one row per member for a fully run-out incurred year: member_id, member_months, allowed_amount. Report allowed PMPM, defined as sum(allowed_amount) divided by sum(member_months), with a 95 percent confidence interval from a bootstrap you write yourself. No library resampler. Resample members with replacement, recompute the ratio on each of 2,000 replicates, take a percentile interval, and seed the generator so the result reproduces. In two sentences, state why resampling claim lines instead of members would be wrong.

Approach

Name the estimator precisely: PMPM is a ratio of sums, not the mean of per-member ratios. The two differ whenever exposure correlates with spend, and it does, because members who churn out mid-year are systematically different from members covered all twelve months.
Resample the unit of independence, which is the member, and recompute numerator and denominator together on the same draw. Recomputing only the numerator against a fixed denominator understates variance and is a common slip.
Pull member_months and allowed_amount into numpy arrays once, then draw indices with rng.integers(0, n, size=n) per replicate. Index draws over arrays run one to two orders of magnitude faster than DataFrame.sample, which is the difference between 2,000 replicates and 200.
Take the 2.5th and 97.5th percentiles of the replicate ratios. Expect an interval that is asymmetric around the point estimate, because healthcare spend is right skewed. That asymmetry is the method working, not a bug to centre away.
Answer the line-resampling question concretely: the many lines of one high-cost member are not independent draws, so line resampling shrinks the interval toward zero width, and member_months has no line-level analogue, so the denominator becomes undefined.

Worked solution (25 min)

Compute the point estimate as allowed.sum() / months.sum() on the full sample.
Extract both columns to float numpy arrays and create rng = np.random.default_rng(seed).
Loop 2,000 times: idx = rng.integers(0, n, n); ratio = allowed[idx].sum() / months[idx].sum(); store it.
Take np.percentile(replicates, [2.5, 97.5]) and report the point estimate with that interval and the seed.

Expected result: A point estimate plus a 95 percent percentile interval that is wider on the upper side than the lower. With tens of thousands of members and a typical commercial cost distribution the relative half-width lands on the order of a few percent; an interval a small fraction of a percent wide is the signature of having resampled rows rather than members.

Verify it yourself

Re-running with the same seed reproduces both bounds exactly.
Raising replicates from 2,000 to 8,000 moves each bound by less than its third significant figure.
The mean of the replicate ratios sits close to the point estimate; a large gap indicates a bug in the resampler rather than skew.
Replicate denominators cluster tightly around the observed total member-months, which confirms the draw size is n rather than a fixed constant.

Follow-up

Half a percent of members have a transplant or a long NICU stay. Does a percentile interval still have nominal coverage, and what would you do about the tail?
The question changes to comparing PMPM across provider panels. What becomes the resampling unit and why?
How would you use the same replicates to test whether this year's PMPM differs from last year's, given the same members appear in both?

Proportion of days covered with shifted, truncated refill intervals

Hard · interval merging · adherence · path dependence

pharmacy_claim has fill_id, member_id, therapeutic_class_code, fill_date, days_supply, reversal_flag, reversed_fill_id. For one therapeutic class and a fixed 12-month window, compute proportion of days covered per member: distinct days on which a dispensed days_supply covers the day, divided by days from the member's first in-window fill through the window end. An early refill shifts coverage forward rather than stacking, and coverage is truncated at the window end. Drop both rows of every reversed pair. Return member_id, pdc, and the share at pdc >= 0.80 among members with at least 2 fills and at least 91 days of follow-up.

Approach

Remove reversals as pairs first. Drop every row with reversal_flag true, and also drop the fill_ids those rows point at through reversed_fill_id. Dropping only the flagged row leaves a dispense that was never collected in the exposure.
Walk fills per member in fill_date order carrying a cursor: start = max(fill_date, previous_end + 1 day), end = start + days_supply - 1. The shift is path dependent, so a plain cumsum over days_supply does not reproduce it. Use itertools.accumulate or a per-member loop over numpy arrays, not a row-wise apply over the whole frame.
Truncate the last interval at the window end before measuring. Without truncation a 90-day fill dispensed on the final day pushes the covered-day count past the denominator and PDC above 1.0.
Use the denominator the definition states: first in-window fill_date through window end, inclusive. Not a flat 365, and not first fill to last fill, which is a different metric that rewards early discontinuation.
Apply the eligibility filter before computing the >= 0.80 share, and report the size of that denominator next to the share. A share without its denominator is not reviewable.

Follow-up

How does PDC differ from medication possession ratio, and which of the two can exceed 1.0?
A member switches to a different ingredient inside the same therapeutic class mid-window. Should the intervals chain, and what does that do to the class-level number?
Members who die or lose coverage mid-window get a short denominator and often a high PDC. If you then compare mortality by adherence category, what bias have you built in and how do you remove it?

Write a function to find the longest substring without repeating characters.

Medium · sql and data manipulation

Approach

Say which table is the grain you start from, and join outward from it.
Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
State the window function and its partition and ordering out loud before writing it.

Follow-up

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

Compute allowed PMPM by incurred month without span fan-out

Hard · fan-out · many-to-many · runout

Report allowed PMPM by incurred month and product_type for 2025 from medical_claim_line (member_id, claim_id, claim_version, frequency_code, service_start_date, allowed_amount, claim_status), pharmacy_claim (member_id, fill_date, allowed_amount, reversal_flag, reversed_fill_id), member_enrollment (span_id, member_id, product_type, effective_date, termination_date) and member_risk_score (member_id, score_year, prospective_risk_score), which holds at most one row per member per score year. The numerator is allowed_amount on surviving claim versions plus unreversed fills, attributed to the product_type in force on the service or fill date. The denominator is member-months. Also return a risk-adjusted PMPM for each cell: allowed PMPM divided by the member-month-weighted mean prospective_risk_score for score_year 2025 over the members making up that cell's denominator. A colleague joined claim lines to member_enrollment on member_id alone and the annual total came back 1.4 times the finance figure. Diagnose that, then produce the correct monthly series.

Approach

Name the defect precisely. member_enrollment is one row per span, so joining on member_id alone is one-to-many and duplicates every claim line once per span that member ever held. The inflation factor is the spend-weighted mean span count per member, which is why 1.4 is plausible and why it is not a constant you can divide back out.
Make the join functional: member_id AND service date between effective_date and COALESCE(termination_date, DATE '9999-12-31'). That yields at most one span per line unless spans genuinely overlap, in which case pick a documented precedence rule, most recently effective wins being the usual one, and prove uniqueness with a duplicate check rather than asserting it.
Aggregate numerator and denominator separately and join them on (month, product_type). Computing both sides across one join is the second route to inflation, because the member-month denominator fans out over claim lines just as easily as the dollars fan out over spans.
Clean each numerator source on its own terms: surviving claim versions with voids removed and claim_status 'paid' on the medical side, reversal rows and their originals removed on the pharmacy side.
Apply completion before anyone reads a trend. State the paid-through date and either withhold the three most recent incurred months or apply completion factors, since the lag differs by service type and the incomplete tail reads as a cost improvement and a utilisation drop at once.
Risk-adjust last, and weight the score the way the denominator is weighted. The divisor for a cell is SUM(member_months * prospective_risk_score) / SUM(member_months) over that cell's members, not a plain AVG over distinct members: a member covered two months must not count the same as one covered twelve. member_risk_score is member-level, so join it after the member-month grain is fixed, or it becomes a second fan-out.
State the rule for members with no 2025 member_risk_score row: keep them in member_months, leave them out of the weighted mean, and publish the scored share of member-months beside the adjusted series. A thin score table moves the adjusted number while the raw one sits still, and without that share nobody can tell the two apart.

Worked solution (45 min)

CTE med: surviving claim version per claim_id, voids dropped, claim_status 'paid', service_start_date in 2025.
CTE rx: fills with reversal_flag FALSE and not referenced by any reversed_fill_id, via NOT EXISTS, fill_date in 2025.
Attribute each numerator row to one span with the date-bounded join, after asserting that row counts before and after the join are equal.
CTE denom: member-months by month, product_type and member_id from spans alone, with fractional partial months and overlapping spans collapsed first.
Join numerator to denominator aggregated on (incurred_month, product_type) and divide for allowed_pmpm.
Join denom to member_risk_score on member_id for score_year 2025, compute the cell divisor as SUM(member_months * prospective_risk_score) / SUM(member_months) over scored members, carry the scored share of member-months, and divide allowed_pmpm by the divisor for risk_adjusted_pmpm.
Withhold the three most recent incurred months and label the paid-through date on every row.

Expected result: One row per (incurred_month, product_type) with allowed_medical, allowed_pharmacy, member_months, allowed_pmpm, mean_risk_score, scored_member_month_share and risk_adjusted_pmpm, covering January through September when the paid-through date is year end and three months are withheld. Row count of the claim-line set is identical before and after the enrollment join.

Verify it yourself

Claim-line row count before and after the span join is unchanged. An increase is fan-out; a decrease is a service date with no covering span, which is a real finding about eligibility gaps.
SUM(allowed_amount) across product_types for a month equals the ungrouped monthly total computed without touching member_enrollment at all.
member_months for a month never exceeds the count of distinct members covered at any point in that month.
mean_risk_score for a cell lies between the minimum and maximum prospective_risk_score among its scored members, and risk_adjusted_pmpm equals allowed_pmpm exactly when that mean is 1.0. If the weighted mean equals a plain AVG over distinct scored members in every cell, the member-month weights were never applied.
A downward step in the last reported months means the runout holdback was not applied; the withheld tail should not be visible in the series at all.

Follow-up

A member holds two overlapping spans under different product_types on the service date. Which one gets the dollar, and how do you keep the member-month denominator consistent with that choice?
Write the check that proves the span join is one-to-one rather than assuming it.
Finance reports on paid_date and you reported on service_start_date. Reconcile one month between the two bases.

If you were tasked with predicting the occurrence of a particular disease, how would you approach this analysis?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.
Restate the decision this analysis has to support, and who acts on the answer.

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?

How do you optimize your code for speed and efficiency?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
Decompose the metric into the rates that drive it, and say which one you would check first.
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 would you prioritize features for a new healthcare analytics tool?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
Decompose the metric into the rates that drive it, and say which one you would check first.
Name one primary metric, then the guardrail that stops it being gamed.

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?

Discuss a challenging data problem you faced and how you resolved it.

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Decompose the metric into the rates that drive it, and say which one you would check first.
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?

Describe how you would design an experiment to test a new treatment's effectiveness.

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
Name the randomisation unit first; it decides the variance and what the test can detect.
Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

How would you handle interference between treated and control units?
What would you do if you could not randomise at all?

Can you explain the concept of A/B testing?

Medium · a/b testing and experimentation

Approach

Decide the analysis before seeing data, including how long it runs and when you look.
Name the randomisation unit first; it decides the variance and what the test can detect.
State the primary metric and the minimum effect worth shipping, then size the test.

Follow-up

What would you do if you could not randomise at all?
How would you handle interference between treated and control units?

Explain the differences between supervised and unsupervised learning.

Medium

Approach

Clarify what is being asked and what a complete answer would contain.
Say what you would check first and why it is the highest-information step.
Work from the decision backwards to the evidence you would need.

Follow-up

How would you know your answer was wrong?
What assumption would you test first?

Plan interim looks and multiplicity control for a year-long trial

Hard · sequential testing · alpha spending · multiple comparisons · novelty effects

A 12-month cluster-randomised care-management trial has one primary endpoint (ambulatory care sensitive admissions per 1,000 member-years), three secondary endpoints, and five pre-specified subgroups. The steering group wants a read every month. At month 2 the primary is nominally significant at p = 0.03 with an effect roughly three times the powered assumption. Write the monitoring and analysis plan that should have been in place, and say precisely what you tell the steering group about month 2.

Approach

Quantify the peeking cost rather than asserting it. Testing accumulating data repeatedly at a fixed nominal 0.05 raises the overall type I error to roughly 0.14 at five equally spaced looks and roughly 0.19 at ten; a monthly read for a year is worse than either.
Replace fixed-threshold looks with a Lan-DeMets alpha-spending plan on a pre-specified number of looks. An O'Brien-Fleming spending function is right here because it spends almost nothing early (a first look needs a nominal p on the order of 1e-5) and preserves close to 0.05 for the final analysis; Pocock's constant boundary (about 0.0158 nominal at each of five looks) buys early stopping power at the cost of a stricter final look. Monitor the safety guardrail on a separate, non-binding harm boundary, because stopping for harm is a different decision from stopping for efficacy.
Control the endpoint family with a gatekeeping structure: test the primary at full alpha, and only on success test the three secondaries in a pre-specified fixed sequence or with Holm. Declare the five subgroups exploratory, test them as treatment-by-subgroup interactions rather than as within-subgroup significance, and report them with FDR control and that label attached.
Separate the three distinct reasons the month-2 effect is inflated. Alpha inflation from the unplanned look is one. Early-exposure inflation is another, since the first clusters to go live are the enthusiastic ones and their staff are running a new process attentively, which decays as it becomes routine. Claims incompleteness is the third and is specific to this outcome: inpatient facility claims run out over months, so month 2 admission counts are undercounted, and the undercount does not have to be equal across arms.
Tell the steering group that month 2 is not a result: p = 0.03 is nowhere near any defensible first-look boundary, the recent incurred months are incomplete at the stated paid-through date, and estimates taken at a crossing are conditionally biased upward, so even a real stop would require a bias-adjusted effect estimate rather than the raw one.

Worked solution (40 min)

State the fixed number of interim analyses and the information fractions at which they occur, then pick the spending function and derive nominal boundaries so the total spend equals 0.05.
Write the gatekeeping rule for the three secondaries and the exploratory label plus FDR rule for the five subgroups.
Specify that the primary is computed only at a stated paid-through date, with the three most recent incurred months either held back or completion-factored.
Add an exposure-time analysis: index every cluster on its own go-live date and report the effect by cluster-months since activation, so habituation and staggered rollout are visible rather than pooled away.
Write the month-2 statement: the boundary was not crossed, the data are incomplete, and no effect estimate will be circulated.

Expected result: A Lan-DeMets alpha-spending plan with a pre-specified look schedule and an O'Brien-Fleming shape for the primary, a separate non-binding harm boundary for the guardrail, fixed-sequence or Holm gatekeeping across the three secondaries, and the five subgroups as FDR-controlled exploratory interaction tests. Month 2 is reported as not a result on three independent grounds: boundary not crossed, claims incomplete, and early estimates conditionally inflated.

Verify it yourself

Confirm the naive repeated-testing error rate: about 0.14 at five looks and about 0.19 at ten, against a nominal 0.05.
Confirm the subgroup family-wise rate without correction: 1 - 0.95^5 = 0.23 under independence.
Confirm the chosen boundaries' cumulative spend equals the nominal 0.05.
Confirm every reported figure carries a paid-through date and that the last three incurred months are held back or completion-factored.

Follow-up

The steering group insists on monthly visibility. What can you show them every month that does not spend alpha?
How would you tell a genuine effect apart from an exposure-time artefact when clusters went live on a staggered schedule?
If the trial does cross an efficacy boundary at month 8, what effect estimate do you publish and why is it not the raw one?

Pooled persistency fell but no one lost coverage

Medium · interval sql · mix shift · cohort seasonality

Pooled 12-month enrollment persistency fell from 0.78 to 0.66 for the cohort anchored in a single month. member_enrollment carries span_id, member_id, plan_id, product_type, effective_date, termination_date, termination_reason_code, dual_eligible_flag and region_code. The metric allows at most one gap of 45 days or fewer. Product mix in that anchor month was not stable. Determine how much of the fall is real disenrollment rather than span bookkeeping or mix, and deliver a persistency series a contract owner could act on.

Approach

Inspect the query's unit of analysis first. Spans are not members, and a benefit or plan change closes one span and opens the next on the same day, so any logic that treats a non-null termination_date as loss of coverage counts an administrative event as churn.
Rebuild member-level coverage by unioning all spans per member and merging adjacent or overlapping ones, then apply the 45-day gap rule to the merged intervals rather than per span.
Cut the terminations by termination_reason_code. plan_change and eligibility_redetermination have entirely different meanings for a contract, and death is not churn you can address.
Split by product_type and stop pooling. Commercial open enrollment and managed medicaid redetermination produce structurally different churn on different calendars, and the pooled average of the two is a number that describes nobody.
Compare the anchor month to the same month in the prior year rather than to the adjacent month, because enrollment is strongly seasonal and an anchor month sitting on an open-enrollment or redetermination boundary is not comparable to its neighbour.
Apply the Kitagawa decomposition across product_type to separate how much of the 12 point fall is mix, meaning the anchor cohort simply contained more of a high-churn product, from how much is within-product deterioration.

Follow-up

Why 45 days, and what would change if the allowable gap were 30 or 60?
A member terminates and re-enrolls 90 days later under a different plan_id. Are they retained? Defend your answer against the opposite one.
What guardrail would catch someone improving persistency by declining to enroll members likely to churn?

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.

The project you argued against

Saying no well is a senior skill and it is rarely rehearsed. Think of a time you told someone their analysis was not worth doing, or that the experiment could not answer their question at the sample size available. Explain what you offered instead. Refusal without an alternative reads as obstruction rather than judgement.

If given a dataset with conflicting information, how would you proceed?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
Close with what you would do differently, concretely.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

What would you do differently if you ran that project again?
How did you know the outcome was caused by your change?

Scope a one-line request for a readmission rate

Easy · scoping · measure specification · stakeholder communication

A clinical operations lead messages you: 'What is our readmission rate? The committee meets Friday.' You have encounter (encounter_id, admit_ts, discharge_ts, encounter_type, admission_type, discharge_disposition, drg_code, index_encounter_id) and member_enrollment coverage spans. Do not open a query editor yet. Deliverable: the three to five questions you send back, ranked by how much the answer moves the number; the default specification you will build if nobody replies by Wednesday; and the one line you will place under the figure so the committee does not read it against a published benchmark.

Approach

Read the probe: this tests whether you convert ambiguity into a specification without either stalling or guessing silently. Both failure modes are common, and the silent guess is worse because nobody can see it.
Rank your questions by leverage on the number rather than by curiosity. Whether the numerator is all-cause or unplanned, and whether the clock starts at discharge_ts, move the result far more than a tie-break rule on same-day returns.
Ask about the comparison first. A number going to a committee will be compared to something, and whether that something is last quarter, another panel, or a national figure decides whether you owe them a raw rate, a risk-adjusted rate, or an observed-over-expected ratio.
Commit to a default in writing so silence does not block you: unplanned acute inpatient readmission within 30 days of discharge_ts, index stays excluding planned admissions, acute-to-acute transfers, discharges against medical advice, and dispositions of expired or hospice, denominator of eligible index discharges, paid-through date stated.
Write the caveat as a restriction on use rather than a hedge: name the one comparison the figure supports and the one it does not.

Follow-up

They reply that they want it 'the way the benchmark does it'. What do you ask next?
The committee wants it split by attending provider. What changes in the specification, and what do you refuse to show?
How does your answer change if the meeting is tomorrow rather than Friday?

Own an analysis that shipped wrong and was acted on

Easy · retrospective · ownership · data quality

Describe an analysis you delivered that was wrong, where someone acted on it before the error surfaced. Pick something with a mechanism you can name, not a typo. Cover what the number claimed, what was actually true, how much time passed before it was caught, who caught it, and what you told the people who had already acted. Deliverable: a five-minute account that ends with the specific control you added afterwards, plus one occasion since when that control has fired.

Approach

Read the probe: the interviewer is testing whether you detect your own errors and whether the correction was structural. Pick an error with a nameable mechanism (a denominator built from distinct members instead of member-months, a series read before claims runout completed, a cohort selected on the outcome) so the story has a diagnosis rather than a mood.
Open with the decision that was made on your number, not with the bug. The severity of an analytic error is measured in the action it caused: a rate filing, a staffing plan, a programme expansion.
State the counterfactual number. 'The true figure was 14.1 admissions per 1,000 member-years, not 9.8' is a fact; 'it was off by a lot' is not.
Describe the detection path honestly, including whether you found it or somebody else did. Say how long it took, because time to detection is the part you actually control next time.
Close on the control, and make it something that protects a reader who has never met you: a unit test on the denominator, a paid-through date stamped on every chart, a required exclusions block in the spec template. Name one later date it caught something.

Follow-up

What would have had to be true for you to catch this before delivery rather than after?
Did the stakeholder change how much they trust your work, and did you want them to?
Give an example of a control you added that turned out to be the wrong control.