Interview guides · Children's Hospital Los Angeles

Children's Hospital Los Angeles 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

A Data Scientist at Children's Hospital Los Angeles plays a pivotal role in transforming data into actionable insights that directly impact patient care and operational efficiency. This position is essential in harnessing the power of data analytics to enhance clinical decision-making, optimize resource allocation, and improve overall health outcomes for children. You will be part of a collaborative team that engages with clinicians, researchers, and healthcare professionals to drive initiatives that support the hospital's mission of providing exceptional care.

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

Minimise protected health information in every extract
Separate censoring from events in time-to-event models
Risk-adjust outcomes before comparing providers or panels

Overview

In this role, you can expect to work on complex datasets related to patient demographics, treatment outcomes, and operational metrics. Your contributions will not only influence how data-driven decisions are made within the hospital but also support research initiatives aimed at advancing pediatric healthcare. The opportunity to work on meaningful problems in a dynamic environment makes this position both critical and intellectually rewarding.

The Children's Hospital Los Angeles interview process

Children's Hospital Los Angeles candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Initial Video Interview

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.

On-site Interview

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

Rates built on member counts rather than exposure

Members join and leave mid-period, so dividing events by distinct members mixes a person covered for 30 days with one covered for 365. New joiners also have artificially low observed utilisation because their claims have not arrived yet and because care takes time to initiate. Denominators must be member-months or member-years, and comparative quality measures usually need a continuous-enrolment requirement with an explicit allowable gap, stated in days.

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.

Ending an analysis without a recommendation or next step

Close with what you would do and what would change your mind, stated as a condition you can check later. If the evidence is genuinely inconclusive, recommend the specific next measurement and say what it costs in time or exposure.

Solving silently instead of narrating the reasoning

Say which branch you are taking and why you chose it over the alternative, for example checking the denominator first because it changes what the comparison means. A correct answer that arrives with no visible path scores below a rigorous one that needed a hint.

Questions & practice

What metrics would you use to evaluate a predictive model?

Medium · machine learning and modelling

Approach

Frame the prediction: the label, the moment of prediction, and the action it triggers.
Say how the offline result would be validated online before it is trusted.
Set a baseline first, so any model has something honest to beat.

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?

How would you approach building a model to predict patient outcomes based on historical data?

Medium · machine learning and modelling

Approach

Pick an evaluation metric that matches the cost of each error type, not a default.
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.

Follow-up

Where could label leakage enter this setup?
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?

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?

Reconstruct drug coverage intervals and compute proportion of days covered

Hard · gaps and islands · window functions · adherence

pharmacy_claim holds fill_id, member_id, ndc_code, therapeutic_class_code, fill_date, days_supply, reversal_flag and reversed_fill_id. Early refills overlap, so days_supply must be laid end to end: each fill's coverage starts at the later of its fill_date and the previous interval's end. Both rows of a reversed pair are excluded. For one therapeutic_class_code over 2025-01-01 to 2025-06-30, return per member the proportion of days covered, defined as covered days divided by days from first fill_date to window end, plus the share of members at or above 0.80. Require at least two fills and 91 days of follow-up.

Approach

Clean the fills first. Drop rows with reversal_flag TRUE and also the fills they point at through reversed_fill_id. Dropping only the reversal row leaves an original fill counted as exposure that never reached the member. Use NOT EXISTS for that second step, since reversed_fill_id is nullable and NOT IN would return nothing.
Shift, never stack. The shifted end is covered_end_i = GREATEST(fill_date_i, covered_end_{i-1}) + days_supply_i, which looks recursive but has a closed form: with cum_i the running sum of days_supply through fill i, covered_end_i = cum_i + MAX over j <= i of (fill_date_j - cum_{j-1}). That is SUM(days_supply) OVER (ORDER BY fill_date ROWS UNBOUNDED PRECEDING) plus a running MAX of an anchor column, so it is two window functions and no recursive CTE.
Derive covered_start_i as GREATEST(fill_date_i, LAG(covered_end)). The intervals are disjoint by construction, so covered days is a plain sum of lengths and never needs a distinct day grid.
Clip intervals to the window end before summing, and decide explicitly whether to clip at disenrollment as well. Supply that runs past the window must not inflate the numerator.
Use the denominator the spec names, first fill_date to window end, and hold it constant across classes. A fixed-window denominator yields a different and usually lower number, and mixing the two across classes makes the comparison meaningless.
Apply the inclusion rules at member level after the intervals are built, then compute the share at or above 0.80 with the qualifying member count beside it.

Follow-up

A member is hospitalised for twelve days mid-window, when the facility supplies medication. Should those days count as covered, and what do published adherence measures do about them?
Someone wants to classify adherence over twelve months and then compare mortality from month zero. What is wrong with that design and what fixes it?
Two different ndc_codes in the same therapeutic class overlap. Is that stacking or switching, and how does your interval logic treat each case?

Present a scenario where you had to make a data-driven recommendation. What was your process?

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.
Fix the population and the time window before naming any metric.

Follow-up

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

Explain how you would evaluate the success of a new clinical intervention based on data.

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?
Which segment would you cut first, and what would that rule out?

How do you prioritize tasks when managing multiple projects?

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.
Restate the decision this analysis has to support, and who acts on the answer.

Follow-up

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

If you were tasked with reducing hospital readmission rates, what data would you analyze?

Medium · product sense and metrics

Approach

Restate the decision this analysis has to support, and who acts on the answer.
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

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?

Describe a data science project you have worked on and your specific contributions.

Medium

Approach

Work from the decision backwards to the evidence you would need.
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.

Follow-up

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

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?

Avoidable admissions fell the quarter a programme launched

Hard · mix shift · regression to the mean · reclassification · seasonality

Ambulatory care sensitive admissions per 1,000 member-years fell 18 percent in the quarter a care-management programme launched, enrolling the top 2 percent of members by prior-year spend. You have encounter, member_enrollment including a product line added mid-quarter, and medical_claim_line. Emergency visits per 1,000 rose 9 percent over the same quarter and observation stays rose as well. Produce a decomposition that states how much of the 18 percent is attributable to the programme, and state the design you would need before claiming any of it.

Approach

Rebuild the denominator from coverage spans as member-years, counting partial coverage fractionally, and recompute the rate with and without the product line that entered mid-quarter. A newly added, structurally healthier population inflates the denominator and deflates every rate built on it without a single admission being avoided.
Freeze the numerator's code list to one version. The ambulatory care sensitive list is defined on ICD-10-CM principal diagnosis codes, and in the United States that code set is updated effective October 1, so a quarter spanning that boundary can gain or lose qualifying codes. Recompute both quarters against a single code set version.
Decompose the remaining change with a Kitagawa split across product_type and prior-year risk decile: the mix term is the population getting younger or healthier, the within term is the only place a programme effect can live.
Test reclassification directly. An inpatient stay recorded as an observation stay is not an avoided admission. Compute total acute contacts per 1,000 member-years as inpatient plus observation plus emergency, and check whether that total moved at all. If it is flat while inpatient falls, care was relabelled and deflected, not prevented.
Compare to the same quarter of the prior year rather than the adjacent quarter. Several ambulatory care sensitive conditions, including heart failure decompensation and bacterial pneumonia, are strongly seasonal, so a quarter-over-quarter comparison confounds the programme with the calendar.
Address selection last and explicitly. The enrolled cohort was chosen on an extreme of prior-year spend, so it regresses toward the mean with no intervention at all, because the selecting year captured both chronic severity and one-off events. A pre-post design on this cohort will report savings every time. The minimum credible design is a concurrent comparison group selected by the same rule in the same period, or a regression discontinuity at the 2 percent threshold, with a parallel-trends check on at least four pre-periods.

Follow-up

The parallel-trends check fails on two of the four pre-periods. What do you do, without abandoning the question?
Express the effect you can defend as an absolute risk difference and a number needed to treat, with the baseline rate stated. Why is that more useful here than a relative reduction?
The programme cannot be withheld from anyone. Sketch a rollout design that still identifies an effect, and say what secular trend does to the estimate.

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.

What the behavioural round is actually scoring

Interviewers here are not checking whether you can describe a project. They want the decision you made, why you made it under the information you had, and what changed afterwards that someone else could measure. A story that ends at 'I built a model' has no ending. Say what the model caused, or what you stopped doing because of it.

Describe a challenging problem you faced and how you approached it.

Medium · behavioural and stakeholder questions

Approach

Name the disagreement or constraint, and how you resolved it with evidence.
State the situation in two sentences and spend the rest on your reasoning.
Close with what you would do differently, concretely.

Follow-up

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

How do you handle missing data in a dataset?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

What would you do differently if you ran that project again?
What did you decide not to do, and why?

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?