Interview guides · Health Care Service

Health Care Service Data Scientist Interview Questions & Guide 2026

Health Care Service data scientist prep: probability, sampling distributions, and stating the assumption behind every test you run.

The 60-second brief

As a Data Scientist at Health Care Service, you play a vital role in transforming complex healthcare data into actionable insights that directly influence patient care, operational efficiency, and strategic business outcomes. This position operates at the intersection of advanced analytics, machine learning, and healthcare domain expertise, requiring you to build robust predictive models and design rigorous experimentation frameworks. Your work helps leadership make data-driven decisions that impact millions of members across health insurance and related services.

Ask early whether the loop includes an asynchronous take-home or a timed live case, because the two are graded on different things. A take-home is read as an artifact: the question you decided to answer, what you did about missing or malformed records, and a conclusion stated plainly enough for someone to act on. A reviewer who cannot rerun your notebook discounts the result whatever score is printed in it. Hold to the stated time box and write down what you would have done with more of it, since the follow-up round is usually a live defence of the same work.

Focus areas

Correct for claims runout before reporting recent months
Minimise protected health information in every extract
Reconstruct drug exposure intervals from dispensing records

Overview

The role demands a balance of deep technical capability and strong product sense. You will collaborate closely with product managers, clinical operations teams, and software engineers to scope analytical initiatives, evaluate healthcare interventions, and optimize core digital products. Whether you are investigating unexpected metric drops in claims processing or designing A/B tests for member engagement platforms, your contributions will directly shape how healthcare services are delivered and scaled.

Navigating this role successfully requires patience, technical rigor, and a collaborative mindset. The interview process is thorough, reflecting the high stakes and regulatory complexity of the healthcare industry. Expect to demonstrate not only your coding and modeling skills, but also your ability to translate ambiguous business challenges into structured, measurable analytical solutions.

The Health Care Service interview process

Health Care Service candidates report 5 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

Recruiter Phone Screen

A screening call is a matching exercise run by someone who will not evaluate your statistics. They are checking that the work described on your resume is work you personally did, and that its scope matches the level the role is written for. Logistics get settled in the same half hour so nobody spends an interviewer's afternoon on a mismatch. The answer that fails is the one narrated in the plural. If every sentence is 'we built' and 'the team decided', there is nothing specific to write down about you. Name the piece that was yours, the decision you made inside it, and what changed after.

What to demonstrate

Whether the ownership implied by your resume survives one round of follow-up about who actually did which part
Whether your described scope (data size, stakeholders, what shipped) matches the seniority the role is written at
Whether timeline, location and compensation expectations make the rest of the loop worth scheduling

How to prepare

Rewrite your top three resume bullets in the first person singular, each with the decision you made and what moved afterwards, then say them out loud once so the 'we' does not return under pressure
Attach one number to each project: the baseline, the change, and the window it was measured over. Where impact was never measured, say that plainly rather than inventing a figure
Settle your compensation range before the call and give it as a range with a reason behind it, such as current total comp or a competing timeline, instead of deflecting the question twice

Technical Deep Dives

A handful of shapes account for most of what gets asked in this format: a ranking or deduplication inside groups, a running or rolling total, a period-over-period comparison, and a cohort tracked forward over time. Recognising the shape quickly is most of the speed here; deriving it from scratch while a clock runs is where the time goes. Know that a window function keeps every row while a GROUP BY collapses them, and know which one the question needs. If the exercise is in Python instead of SQL, the same shapes arrive as groupby with transform, shift and merge, and the same grain mistakes are available.

What to demonstrate

Whether you reach the right construct without a detour, such as ROW_NUMBER over a partition to deduplicate instead of a self-join against a MAX subquery
Whether you know what your window frame actually is, since adding ORDER BY inside OVER changes the default frame and silently changes a running total
Whether the thing runs. A near-miss that throws an error scores below a plainer query that returns the right rows.

How to prepare

Write each of the four shapes once from memory against a small schema and keep the working version somewhere you will reread it: dedupe with ROW_NUMBER, a running total, a month-over-month change with LAG, and a retention table
Compute one running total twice on data with tied timestamps, once on the default frame and once with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and look at where the two disagree
If Python is on the table, rebuild the dedupe and the running total with groupby and cumsum, then assert the two implementations return identical rows

Take-Home Case Study

Your submission is read asynchronously by someone who cannot ask you a clarifying question, and who will usually skim it before reading it properly. That changes what good looks like. The conclusion belongs near the top with the supporting analysis beneath it, and the code should run start to finish on a clean machine without a manual step you forgot to document. A reviewer forced to reconstruct your reasoning from the order of notebook cells is already discounting the work. The submissions that land are the ones where a busy reader gets the answer immediately and can verify it if they want to.

What to demonstrate

Whether the answer arrives before the methodology, so a reader who stops after the first page still has the recommendation
Whether the code runs end to end from the submitted files, with dependencies and data paths declared rather than assumed
Whether each chart is legible on its own, carrying axis labels and units, and exists to support a claim made in the text

How to prepare

Restructure a past analysis so the opening paragraph holds the recommendation and the number behind it, then check that nothing later in the document quietly contradicts it
Copy your own submission into an empty directory, run it in a clean environment, and fix everything that breaks; hidden local state is caught here or by the reviewer
For every chart, write the one sentence it is meant to prove, and delete the chart if you cannot write that sentence

Technical Whiteboarding

Much of what gets scored here happens out loud while you type. Nobody can see your reasoning inside a half-written query, so five silent minutes read as being stuck even when they are not. State the plan in plain language first: which tables, what grain you are aggregating to, and the one filter that defines the population. Then write it. The narration doubles as insurance, because a wrong plan gets caught early and cheaply while a wrong query gets caught at the end with no time left to redo it. A timed statistics section, where one exists, is a separate test with its own clock.

What to demonstrate

Whether the query you write matches the plan you just described
What you do with a hint, meaning whether the correction gets absorbed or the first approach gets defended
Whether you can debug your own wrong output by reading the result set and naming which part of the query produced the anomaly

How to prepare

Solve three problems while screen-sharing into a recording, then watch it back and mark every stretch longer than thirty seconds where you said nothing
Practise compressing the plan into one sentence before typing, then check afterwards whether the finished query actually matched it
Time yourself on statistics questions that carry a business reading, such as what a confidence interval does and does not claim, rather than re-reading notes without a clock

Panel Interviews

A loop is not scored one interview at a time. The people you meet compare notes afterwards, usually in a meeting you are not in, and the outcome turns on what each of them can say about you when asked. That rewards something other than survival: every room needs one specific thing worth repeating, and none of them can contradict another. The common way to lose is to tell the same project four times with different numbers in it, or to be uniformly fine in a way that leaves nobody with anything to argue for.

What to demonstrate

Whether your account of a project survives being told twice, with the same scale, the same metric definition and the same numbers each time
Whether each interviewer leaves with one concrete claim they could make on your behalf later, rather than an absence of complaints
Whether a question you already answered in an earlier room gets the same answer at the same depth, without visible impatience

How to prepare

Write a one-page fact sheet for your two or three main projects that fixes the numbers you will quote: rows of data, the metric as a single sentence, the effect you measured and how long the work took. Say them aloud from the sheet until they come out identical every time
For each kind of room you expect, decide the one sentence you want that interviewer repeating in a debrief, then check during the mock that you said it outright instead of implying it
Rehearse answering the same project question twice in one sitting, the second time as though you had not just answered it, because the thing that needs fixing is the flatness that creeps into a repeated story

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.

Treating clinical measurements as missing at random

A lab result, a vital sign, or a screening exists because someone ordered it, and ordering tracks suspicion of disease, visit frequency, and site workflow. Imputing the mean or dropping incomplete rows biases the population estimate and can flip the sign of an association, because the untested are systematically healthier or systematically disengaged. The presence indicator is often more predictive than the value, which is a warning sign rather than a feature win: a model that learns test ordering will not transfer to a site with different protocols.

Stopping an experiment the moment it crosses significance

Fix the sample size or duration before launch, or use a method built for continuous monitoring such as a sequential test, always-valid confidence intervals, or group-sequential boundaries. Repeatedly checking a fixed-horizon p-value against 0.05 pushes the real false-positive rate well above 5 percent.

Reporting a mean for a heavy-tailed metric without saying what it hides

For spend, session length or items per order, a small fraction of units carries most of the total, so the mean has a wide standard error and one account can move it. Fix the handling before you see the result: cap or winsorise at a pre-declared percentile, and report the median or the share above a threshold next to the mean. Capping changes the estimand, so say which question the capped number answers, and check how much of any difference comes from the top 0.1 percent of units.

Questions & practice

What is the difference between parametric and non-parametric testing, and when would you use each in a healthcare analysis?

Medium · statistics and probability

Approach

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

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?

What is regularization, and how does it prevent overfitting in predictive models?

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Check what information would not exist at prediction time, and exclude it.
Set a baseline first, so any model has something honest to beat.

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?

Four data-quality rules over corrected and cancelled lab results

Easy · data quality · record supersession · unit drift

lab_result has result_id, order_id, patient_id, loinc_code, value_numeric, value_text, units, reference_low, reference_high, specimen_collected_ts, resulted_ts, result_status (preliminary, final, corrected, cancelled), supersedes_result_id. Return one row per rule with a failing count, a failing share, and one example result_id, for these rules: more than one surviving row per (order_id, loinc_code) after resolving corrections and dropping cancelled; resulted_ts earlier than specimen_collected_ts; a numeric result whose unit is not the modal unit for its loinc_code; value_numeric and value_text both null on a row with result_status 'final'.

Approach

Resolve the correction chain before anything else. Drop result_status 'cancelled', then within each (order_id, loinc_code) keep the row whose result_id appears in no other row's supersedes_result_id. That terminal node is the surviving value and the rule holds even if a correction is loaded with a backdated timestamp.
Build each rule as a boolean mask over a named frame rather than as a filtered copy, so the failing share has an explicit, per-rule denominator. Rule three is eligible only on rows with a non-null value_numeric; rule four only on final rows.
For the unit rule, derive the modal unit per loinc_code from the data instead of hardcoding an expected unit. The failure being hunted is one analyte reported in two units whose values differ by a fixed factor, which no range check catches because both sets of numbers look plausible.
Emit a tidy frame with columns rule, eligible_rows, failing_rows, failing_share, example_result_id. A printed report cannot be diffed between runs; a frame can be stored and alerted on.
Order the output by failing_share descending so the run has a lead finding rather than four equal-weight lines.

Worked solution (25 min)

Drop cancelled rows, then compute the set of result_ids referenced by supersedes_result_id and keep rows not in that set.
Rule one: group the survivors by (order_id, loinc_code) and count rows above 1.
Rule two: mask on resulted_ts < specimen_collected_ts over rows where both timestamps are present.
Rule three: compute the mode of units per loinc_code over rows with non-null value_numeric, then mask rows whose unit differs from it.
Rule four: mask rows with result_status 'final' and both value columns null. Assemble the four results into one frame with counts, shares and an example id.

Expected result: A four-row frame. After the resolver, rule one should report zero failing groups on a well-formed extract; a non-zero count there means the supersession chain branches or a duplicate load occurred, and that is the finding to lead with rather than a percentage.

Verify it yourself

Running the resolver a second time on its own output is a no-op, and exactly one row survives per (order_id, loinc_code).
No result_id appearing in any supersedes_result_id survives the resolver.
Seed one row with a converted unit for an analyte otherwise reported in a single unit and confirm rule three catches exactly that row and nothing else.
Each rule's failing_share uses its own eligible_rows, so a rule with no eligible rows returns share 0 rather than a division error.

Follow-up

The modal-unit rule fires on 8 percent of one analyte. How do you decide whether that is a unit-conversion bug or a second legitimate assay?
What would you add to catch a value that is inside its reference range but physiologically impossible?
Which of these four rules should block a downstream pipeline and which should only alert, and why?

How do you select the right evaluation metrics for an imbalanced classification problem, such as predicting rare medical conditions?

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
Say which table is the grain you start from, and join outward from it.
State the window function and its partition and ordering out loud before writing it.

Follow-up

How does the query change if the join becomes one-to-many?
How would you verify this result without re-running the same query?

How do you handle missing or null values when joining large disparate tables in SQL?

Medium · sql and data manipulation

Approach

State the window function and its partition and ordering out loud before writing it.
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.

Follow-up

How does the query change if the join becomes one-to-many?
How would you verify this result without re-running the same query?

How would you identify duplicate records in a massive healthcare claims dataset using SQL?

Medium · sql and data manipulation

Approach

Check whether any join is one-to-many before aggregating, or the sums inflate.
Compute rates by summing numerator and denominator separately, never by averaging rates.
Say which table is the grain you start from, and join outward from it.

Follow-up

How would you verify this result without re-running the same query?
How does the query change if the join becomes one-to-many?

Measure ninety-day primary care contact for an enrollment cohort

Easy · cohort analysis · conversion rate · date windows

member_enrollment gives span_id, member_id, product_type, effective_date and termination_date, which is NULL while a span is active. medical_claim_line gives member_id, service_start_date, procedure_code and claim_status. The cohort is members whose earliest 2025 span has effective_date in January 2025 and who stay covered at least 90 days from it. Return, by product_type, cohort size and the share with at least one paid claim line whose procedure_code is in a supplied list of evaluation-and-management office visit codes and whose service_start_date falls in the half-open window from effective_date to effective_date plus 90 days.

Approach

Build the cohort as one row per member before touching claims. Take the earliest 2025 span per member, require its effective_date in January, and require coverage to effective_date + 90 days, which means termination_date IS NULL OR termination_date >= effective_date + 89.
Use EXISTS for the numerator rather than a join. A member with four office visits must count once; joining and counting rows inflates the numerator and can push the rate above 1.
Keep the window half-open and anchored per member. Every member has a different effective_date, so a single fixed calendar window is a different metric and will not match the cohort definition.
Aggregate numerator and denominator in one pass with a conditional sum grouped by product_type, and carry the raw counts alongside the rate so the reader can see the denominator.
State the reportability rule on the output: the January cohort is not final until 90 days plus professional-claims runout have passed, so label the paid-through date.

Worked solution (15 min)

CTE first_span: per member, the 2025 span with the minimum effective_date, filtered to January effective dates.
Add the continuous-coverage requirement to that CTE using COALESCE(termination_date, DATE '9999-12-31').
Add an EXISTS subquery on medical_claim_line correlated on member_id, restricted to the code list, claim_status = 'paid', and service_start_date >= effective_date AND < effective_date + 90.
Group by product_type: COUNT(*) as cohort_size, SUM(CASE WHEN contacted THEN 1 ELSE 0 END) as contacted, and their quotient.
Print the paid-through date used, and re-run at a 30-day window to confirm the rate falls.

Expected result: One row per product_type with cohort_size, contacted_members and contact_rate = contacted_members / cohort_size, where contacted_members never exceeds cohort_size.

Verify it yourself

contact_rate lies in [0, 1] for every product_type. Above 1 proves the numerator was counted on rows rather than members.
SUM(cohort_size) across product_types equals COUNT(DISTINCT member_id) in the cohort CTE. A larger value means a member with two January spans was counted twice.
A 0-day window returns a rate at or near zero, and a 365-day window returns a rate no lower than the 90-day one.

Follow-up

A member changes plan on day 40, closing one span and opening another. Is she still in the cohort, and which product_type does she report under?
What moves if denied lines are allowed into the numerator, and which definition is right for an access metric?
How would you show this as a cumulative curve by day since effective_date instead of one 90-day point?

A key engagement metric dropped by fifteen percent week-over-week. How would you investigate and diagnose the root cause?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
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.

Follow-up

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

How would you design a product metric to measure the success of a new member onboarding portal?

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.
State what result would change your recommendation, so the answer is falsifiable.

Follow-up

How would you detect that the metric is being gamed rather than genuinely improving?
Which segment would you cut first, and what would that rule out?

How would you measure the long-term value of a personalized health recommendation feature?

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

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?

What are some common experimentation pitfalls you encounter when running tests on healthcare web platforms?

Medium · a/b testing and experimentation

Approach

State the primary metric and the minimum effect worth shipping, then size the test.
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 do if you could not randomise at all?

Explain how you assess statistical significance and control for false discovery rates when running multiple hypothesis tests.

Medium · a/b testing and experimentation

Approach

Name the randomisation unit first; it decides the variance and what the test can detect.
Name the guardrails that would stop a launch even on a positive primary result.
Say whether units interfere with each other, and switch design if they do.

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?

What framework would you use to evaluate whether to launch a new preventive care reminder feature?

Medium

Approach

State your assumptions explicitly before working the problem.
Work from the decision backwards to the evidence you would need.
Clarify what is being asked and what a complete answer would contain.

Follow-up

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

Design a switchback when a shared review queue breaks SUTVA

Medium · interference · switchback · carryover · clustered standard errors

A prior-authorisation auto-approval rule for a set of imaging procedure codes is meant to cut turnaround time. Requests sit in one shared queue worked by a fixed nurse pool across two review sites, so auto-approving one request frees reviewer capacity and speeds up the requests that were not auto-approved. Outcomes are median hours from received_date to adjudicated_at and the first-pass denial rate. Request-level randomisation is therefore invalid. Design the test: randomisation unit, block length, effective sample size, and the analysis.

Approach

Name the violation precisely. The treatment assigned to one request changes the outcome of other requests through a shared capacity constraint, so the stable unit treatment value assumption fails and a request-level difference in means estimates neither the direct effect nor the total effect.
Randomise time rather than requests: assign the policy on or off for whole (site, time block) cells. Every request inside a block experiences one consistent queue regime, so the block-level contrast is a genuine system-level effect including the capacity spillover you care about.
Choose block length from the carryover horizon, not from convenience. Estimate how long the queue takes to return to steady state after a load shock from the historical backlog series, then set blocks well longer than that and discard a burn-in equal to the drain time at the start of each block.
Analyse at the block level. The unit of analysis is the block mean, the effective sample size is the number of analysable blocks and not the tens of thousands of requests inside them, and standard errors must be cluster-robust or HAC because adjacent blocks share staffing and seasonality.
Balance the calendar deliberately: use whole weeks, or block on day of week, so the policy is not disproportionately on during Mondays. If the shared resource can instead be partitioned (separate nurse pods with separate queues), cluster randomisation on pods is simpler, but with two sites you have two clusters, which is not a design.

Worked solution (35 min)

Estimate the queue drain time from historical backlog after a volume spike, and set block length to several multiples of it (for example weekly blocks if the queue settles within a day).
Randomise policy on or off independently per (site, week), balanced so each site gets roughly half treated weeks.
Drop the burn-in window at the start of each block, then compute one block-level summary per (site, week) for each outcome.
Estimate the effect as a block-level regression of the summary on the treatment indicator with site and calendar-period fixed effects, using cluster-robust or HAC standard errors.
Power the study from the historical block-to-block standard deviation of the outcome, not from request-level variance.

Expected result: A switchback randomised at (site, week) with a discarded burn-in, analysed on block means with site and period fixed effects and HAC or cluster-robust standard errors. With two sites over 26 weeks the effective sample is about 52 block observations, fewer after burn-in, and that is the number the MDE must be computed from.

Verify it yourself

Lag-1 autocorrelation of block means after burn-in should be near zero; if it is not, the blocks are too short for the carryover.
Treated and control blocks should be balanced across day-of-week and month.
Run the randomisation labels on historical data as an A/A and confirm a null effect with correct interval coverage.
The block-level standard deviation should be materially larger than request-level SD over sqrt(requests per block), which is the tell that clustering matters.

Follow-up

Suppose you had randomised at the request level and 30 percent of the effect leaked to the control requests. What does that do to the required sample size?
Turnaround time is right-skewed with a long tail. Would you analyse block medians, block means, or something else?
How would you detect that your chosen block length is too short for the carryover you assumed?

Adherence share collapsed the month a feed was added

Easy · pdc · interval sql · identity resolution

The share of members with proportion of days covered at or above 0.80 in one therapeutic class fell from 0.71 to 0.58 in a single month. pharmacy_claim carries fill_id, member_id, ndc_code, therapeutic_class_code, fill_date, days_supply, quantity_dispensed, refill_number, reversal_flag, reversed_fill_id and source_system_code. There is no person-level key above member_id and no enrollment table on hand, so every diagnostic has to run on pharmacy_claim alone. That month the pipeline began ingesting a second pharmacy source. Determine whether adherence actually changed, and hand back a PDC query you would defend line by line.

Approach

Compute controls that depend on neither member identity nor the PDC code: total fills in the class per month, and total days_supply dispensed in the class per month. Both are sums over rows, so neither moves when one person's fills split across two member_ids, which is exactly what a per-member control cannot promise. If both are flat while PDC falls, no dispensing behaviour changed and the computation or the keys did.
Check days_supply for nulls and zeros by source_system_code. If the new source omits days_supply and the code coalesces null to zero, covered days vanish and PDC falls even though the fills are there.
Check for member identity splitting using the columns you actually have. There is no person key above member_id, so the split has to be found by its footprint: distinct member_id in the class jumping at the break while total fills stay flat, and a rise in the share of member_ids whose earliest fill for a given ndc_code carries refill_number above zero, which is a continuation history that lost its beginning. When one person is split, each id may fall below the two-fill denominator requirement and drop out, and the ids that do survive carry artificial gaps.
Check reversals. A reversed fill is two rows, the fill and a row with reversal_flag TRUE pointing at reversed_fill_id, and both must be excluded from exposure. If the new source expresses reversals differently and reversed_fill_id does not link, phantom coverage appears, which pushes PDC up rather than down, so use this to rule the cause in or out.
Verify the denominator and window are unchanged: members with at least two fills in the class and at least 91 days of follow-up, over a stated fixed 12-month window, never a mixture of window lengths.
Write the PDC query so overlapping refills shift the next interval's start to the later of fill_date and the prior interval's end, so a day is covered at most once, and cap at one day per calendar day.

Follow-up

Why is PDC preferred to medication possession ratio when early refills are common?
You want to compare adherent and non-adherent members on hospitalisation. What is wrong with classifying on a 12-month PDC and then following from day zero?
A member switches to a different drug in the same class mid-year. How does your interval logic handle that, and is the class the right grain?

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.

Your part, not the team's

Most data work is done by groups, so an interviewer has to work out which piece was yours. An answer that runs on 'we' for several minutes gets interrupted with a question about what you personally did, and by then the answer sounds defensive even when it is true. Mark your own contribution as you go, and name the parts that belonged to someone else instead of leaving them ambiguous. Keep a few specifics back as well, like the name of the metric or who actually objected, so a probe can be answered with something you had not already said.

Describe a time you collaborated with cross-functional teams like engineering or product to ship an analytical feature under a tight deadline.

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
Name the disagreement or constraint, and how you resolved it with evidence.
Close with what you would do differently, concretely.

Follow-up

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

Defend a null result against a programme sponsor

Medium · regression to the mean · difference-in-differences · stakeholder management

A care management programme enrolled the top 1 percent of members by prior-year allowed spend. The sponsor's deck shows allowed PMPM for enrollees falling 34 percent from the year before enrollment to the year after, and asks for budget to triple the programme. You rebuild the evaluation with a concurrent comparison group selected by the identical spend rule in the same period. The difference-in-differences estimate is a 3 percent reduction with a confidence interval spanning zero. Deliverable: how you present this, to whom, in what order, and what you propose next.

Approach

Name the probe: whether you can deliver a finding that costs somebody their programme without softening it into uselessness or creating an adversary who routes around you next time.
Lead with the mechanism, not the verdict. Show the comparison group's own unadjusted drop, which will be large, because a cohort selected on an extreme of the outcome regresses toward the mean whether or not anyone intervenes. The sponsor's 34 percent is mostly that, and it is a property of the selection rule rather than a criticism of their clinicians.
Give the sponsor the finding privately before it appears in any deck their leadership sees. Being surprised in a room is what turns a methods disagreement into a political one.
State the estimate with its interval and say what it rules out as well as what it fails to establish. A 3 percent point estimate whose interval crosses zero is not evidence of no effect; it is insufficient power to separate a modest effect from none, and those are different claims.
Arrive with a design rather than only an objection. Propose a regression discontinuity at the enrollment threshold if the rule is applied sharply, or a randomised rollout across the next wave of eligible members, and state the sample size needed to detect the effect size the sponsor believes in.

Follow-up

The sponsor says withholding the programme from a comparison group is unethical. What do you propose instead?
Leadership wants one number for the board next week. What do you give them?
What result would change your mind and make you believe the 34 percent?

Disagree with a product manager about an adherence nudge

Medium · immortal time · confounding · disagreement

A product manager proposes shipping a refill-reminder feature to the whole member base, citing an internal analysis: members with 12-month proportion of days covered at or above 0.80 had 31 percent fewer admissions than members below it. The roadmap date is three weeks out and engineering is scoped. You believe the comparison builds survival and continuous coverage into the exposure definition, and is separately confounded by the kind of member who refills on time. Deliverable: what you say in the room, what you send afterwards, and the smallest study you would accept as sufficient to ship.

Approach

The probe is whether you can be specific and non-territorial at once. A disagreement that arrives as 'the analysis is flawed' costs you the room; one that names a mechanism and offers a cheaper path keeps it.
Name the two defects separately, because they have different fixes. Classifying a member as adherent over 12 months requires them to stay alive and stay covered for those 12 months, and that guaranteed event-free interval is assigned to the adherent group. Separately, members who refill on schedule differ from those who do not in ways claims never record.
Show rather than assert. Rerun the same comparison as a landmark analysis: classify adherence over days 1 to 90, start follow-up at day 91 for everyone still enrolled and event-free, and report how much of the 31 percent survives.
Separate the association claim from the intervention claim out loud. Nothing in the analysis speaks to whether a reminder changes refill behaviour, which is the actual product question and is separately testable.
Offer the shippable path: a randomised holdout on a subset with proportion of days covered as the primary endpoint, powered on the plausible effect of a reminder rather than on the 31 percent, with a stated read date.

Follow-up

The landmark rerun still shows a 20 percent difference. Do you ship?
The product manager says a holdout delays launch. How do you cost that argument?
What primary endpoint do you choose, and why not admissions?