Interview guides · Baylor Scott & White Health

Baylor Scott & White Health Data Scientist Interview Questions & Guide 2026

Case practice for Baylor Scott & White Health data scientist candidates: scope a vague prompt into a measurable question before you analyze it.

The 60-second brief

The role of Data Scientist at Baylor Scott & White Health is pivotal in transforming vast amounts of healthcare data into actionable insights that enhance patient care and operational efficiency. Data Scientists are responsible for analyzing complex datasets to develop predictive models and algorithms that directly impact clinical decisions, patient outcomes, and healthcare delivery processes. By leveraging data analytics, machine learning, and statistical modeling, you will help shape the strategic direction of healthcare initiatives, making this role not only critical but also incredibly rewarding.

Allocate prep to your weakest link rather than your favourite topic. Of the three things that usually gate the outcome (SQL that is correct under messy joins, sound reasoning about experiments, and structured framing of an open-ended problem), candidates tend to over-invest in modelling theory and under-invest in framing.

Focus areas

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

Overview

In a dynamic healthcare environment like Baylor Scott & White Health, the Data Scientist will work closely with cross-functional teams, including clinical staff, IT professionals, and business analysts, to address real-world healthcare challenges. You'll contribute to projects that optimize patient flow, reduce readmission rates, and improve treatment protocols. Your insights will guide stakeholders in making informed decisions that enhance the quality of care provided to patients, demonstrating the vital intersection of data science and healthcare.

Working as a Data Scientist here is not just about crunching numbers; it's about making a tangible difference in people's lives by ensuring that data-driven solutions are at the forefront of healthcare innovation. Expect to engage in a variety of exciting projects that challenge your analytical skills while also allowing you to witness the direct impact of your work on patient care and operational success.

The Baylor Scott & White Health interview process

Baylor Scott & White Health candidates report 3 rounds · ≈ 3-5 weeks. The stages below are what candidates describe, not a published process.

Initial Screening

Most candidates lose this call inside the first two minutes, during the walkthrough of their own background. The account runs chronologically, sits at the level of tools and titles, and never arrives at a decision anyone could have disagreed with. Anchor on a problem instead of a timeline: what the team could not answer, what you did about it, what happened next. Ninety seconds is enough, and stopping on time leaves room for the half of the call that belongs to you. What you ask about how work gets prioritised signals your level more reliably than the walkthrough does.

What to demonstrate

Whether your background summary has a shape (problem, decision, consequence) or is a chronological list of tools and employers
Whether you can account for gaps, short stints and the reason you are looking, unprompted and without hedging
The substance of the questions you ask back, which an experienced screener reads as a level signal

How to prepare

Time your opening walkthrough against a clock. If it runs past two minutes, compress the earliest role into a single clause and spend the recovered time on the most recent one
Write one honest sentence for every gap or short stint visible on your resume and offer it before being asked about it
Prepare questions about how work arrives and gets prioritised: who writes the request, how often priorities change, and what happens to an analysis after it is delivered

Technical Interviews

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

Team Engagement

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.

Where candidates lose points

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 the most recent months of a claims-based series as real

Claims incur before they are reported and paid, so recent incurred months are systematically undercounted until runout completes. The lag is not uniform: pharmacy adjudicates in days, professional claims in weeks, inpatient facility claims in months. That means recent data is both too low and mix-shifted toward cheap services, which reads as a cost improvement and a utilisation drop at once. The fix is to hold the last three incurred months back or apply completion factors, and to state the paid-through date on every chart.

Accepting a metric definition without asking about the denominator

Pin down the denominator, the eligibility filter and the time window before computing anything: conversion rate per session, per user, per eligible user and per new user are four different numbers with different behaviour. Restate the definition in one sentence and get agreement before you analyse.

Building features from data that postdates the prediction time

Check every feature against the timestamp at which the model would actually score, and drop anything computed from a window that includes or follows the label event. For a forecasting use case, split train and test by time rather than at random, and split by entity when the same entity recurs.

Questions & practice

How would you implement a decision tree from scratch?

Medium · machine learning and modelling

Approach

Say how the offline result would be validated online before it is trusted.
Pick an evaluation metric that matches the cost of each error type, not a default.
Check what information would not exist at prediction time, and exclude it.

Follow-up

Where could label leakage enter this setup?
How would you choose the decision threshold, and who owns that choice?

Sessionise transfer chains into episodes, then count readmissions

Hard · sessionisation · cumsum grouping · readmission

encounter has encounter_id, patient_id, facility_id, encounter_type, admit_ts, discharge_ts, admission_type, discharge_disposition, is_planned_admission, principal_diagnosis_code. Chain acute-to-acute transfers into episodes of care: an inpatient encounter discharged as transfer_acute, followed by another inpatient encounter for the same patient at a different facility admitting within 24 hours of that discharge, belongs to the same episode. Then compute the 30-day unplanned readmission rate over episodes, excluding index episodes that are planned, that end in expired, hospice or against medical advice, or that are still open. Return encounter_id to episode_id, plus the rate.

Approach

Restrict to inpatient encounters for the chaining step and sort by patient_id, admit_ts. Observation and emergency encounters are not acute inpatient stays and pooling them changes both the chain and the denominator.
Build a boolean continues flag: the previous row is the same patient, its discharge_disposition is transfer_acute, its facility_id differs from this row's, and admit_ts minus previous discharge_ts is between 0 and 24 hours. Episode id = cumsum of the negation of that flag. This is the vectorised form of a sessionisation loop and is why the pattern generalises to any event stream.
Collapse to episode grain taking first admit_ts, last discharge_ts, last discharge_disposition, and any() of is_planned_admission. The episode inherits its exit state, not its entry state, which is why a transfer chain ending at home is one home discharge rather than two transfers.
Apply index exclusions at episode grain. Dropping episodes that end in death is not optional: a dead patient cannot be readmitted, so leaving them in inflates the denominator and depresses the rate for exactly the panels caring for the sickest members.
For each surviving index episode, find the next unplanned acute inpatient episode for that patient admitting within 30 days of the index discharge_ts. Count events at episode grain, since one patient can contribute several index episodes. Report the raw rate and say plainly that it is not comparable across panels without risk adjustment, which is what the observed-over-expected form exists for.

Worked solution (45 min)

Filter to encounter_type 'inpatient', sort by patient_id and admit_ts, and shift discharge_ts, discharge_disposition, facility_id and patient_id by one row.
Compute continues as same patient AND prior disposition is transfer_acute AND facility differs AND 0 <= (admit_ts - prior discharge_ts) <= 24 hours; episode_id = (~continues).cumsum().
Aggregate to episodes: first admit_ts, last discharge_ts, last discharge_disposition, any planned flag, patient_id.
Drop index-ineligible episodes: planned, disposition in expired, hospice or ama, and null discharge_ts.
For each eligible index episode, search the same patient's later episodes for an unplanned admit_ts within 30 days of index discharge_ts; a merge_asof on patient with a forward direction and a 30-day tolerance does this without a nested loop.
Rate = index episodes with an event divided by eligible index episodes; return the encounter-to-episode map alongside it.

Expected result: Two outputs: an encounter_id to episode_id map, and one rate. Worked case: encounter A discharged transfer_acute at 10:00, encounter B admitted 14:00 the same day at a different facility and discharged home on day 5, encounter C an unplanned admission on day 20. A and B form one episode, C is a readmission of it, and the patient contributes one eligible index episode and one event, not two indexes and possibly two events.

Verify it yourself

Every closed episode ends in a disposition other than transfer_acute; an episode ending in transfer_acute means a chain was cut early.
Episode count is less than or equal to inpatient encounter count, with equality only when the sample contains no transfers.
Turning the chaining rule off raises the readmission rate. The direction of that change is the proof your chaining fired at all.
No index episode's matched readmission is an encounter already inside that same episode.

Follow-up

discharge_ts is null on an open stay in the middle of a chain. What does your flag do, and what should it do?
A patient is transferred out and then back to the originating facility within 24 hours. Should that chain, and does your facility_id condition handle it?
Is a readmission itself eligible to serve as a later index episode? Say what you chose and what the choice does to the rate.

Expand overlapping coverage spans into fractional member-months

Medium · interval expansion · denominators · vectorisation

member_enrollment has span_id, member_id, plan_id, product_type, effective_date, termination_date (NULL while active). Both dates are inclusive. Write a function returning one row per calendar month of a stated year with total member-months, where a member contributes covered days in the month divided by days in that month, capped at one month per member even when two spans overlap after a plan change or a retroactive span. Do not expand to one row per member-day: the sample has 400,000 members. Output: month, member_months.

Approach

Resolve NULL termination_date to the reporting end date and say so in a comment. An open span is not an infinite span, and clipping it at the year end is what keeps the denominator finite and auditable.
Merge overlapping and adjacent spans per member before touching months. Sort by member_id and effective_date, carry a running maximum of the end date, and open a new merged group when effective_date exceeds that running max plus one day. Adjacent means a gap of zero days, which a plan change produces constantly.
Cross join the merged spans to the 12 month boundaries rather than to days. Overlap days = (min(span_end, month_end) - max(span_start, month_start)).days + 1, clipped below at 0. That is at most 12 rows per span instead of 365.
Divide overlap days by the number of days in that month, so February and July are weighted correctly, then sum by month.
Validate on a constructed member before trusting the aggregate: a single span covering the whole year must sum to exactly 12.0.

Follow-up

Product type changes mid-year. The metric must be reported by product_type. Where does the cap now apply and what breaks?
An eligibility file arrives with a retroactive termination that shortens a span you already reported on. How do you restate?
Why member-months rather than distinct members, in one sentence, for a director who wants the simpler number?

Write a function to calculate the mean and median of a list of numbers.

Medium · sql and data manipulation

Approach

Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
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 would you verify this result without re-running the same query?
What breaks if events arrive late or out of order?

Can you provide a code snippet that demonstrates your proficiency in SQL?

Medium · sql and data manipulation

Approach

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.
Check whether any join is one-to-many before aggregating, or the sums inflate.

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?

Deduplicate claim versions before totalling allowed amounts

Easy · dedup · window functions · claim versions

medical_claim_line holds one row per service line per claim version: claim_line_id, claim_id, claim_version, frequency_code (1 original, 7 replacement, 8 void), member_id, service_start_date, procedure_code, allowed_amount, claim_status and adjudicated_at. Adjusted claims appear more than once, so summing every row double counts them. Return total allowed_amount by procedure_code for service_start_date in Q1 2025, counting only lines with claim_status 'paid' on the surviving version of each claim, which is the highest claim_version per claim_id, and dropping the claim entirely when that surviving version carries frequency_code 8. Resolve survivorship with a window function, not a self-join.

Approach

Resolve survivorship at claim_id, not claim_line_id. Carry MAX(claim_version) OVER (PARTITION BY claim_id) alongside every line and keep lines where claim_version equals it, because a replacement version can contain a different number of lines than the original.
Read the frequency_code of the surviving version only. A frequency_code 8 removes the claim outright; it does not revert payment to the prior version, so a void must delete the claim rather than promote version n-1.
Apply claim_status and the date filter after survivorship is resolved. Filtering paid lines first can strip the surviving version and silently elect a superseded one.
Group by procedure_code and sum allowed_amount, then state the paid-through date on the output because Q1 amounts keep moving until adjudication runout completes.
Sanity check the shrinkage: report how many claim_ids and how many dollars the dedup removed, so the reviewer can see the step did something.

Worked solution (20 min)

Build a CTE that adds surviving_version = MAX(claim_version) OVER (PARTITION BY claim_id) and surviving_freq = the frequency_code of the row at that version, to every line.
Filter to claim_version = surviving_version so all lines of the surviving version survive together.
Drop claims whose surviving_freq is 8, then apply claim_status = 'paid' and service_start_date between 2025-01-01 and 2025-03-31.
Group by procedure_code, sum allowed_amount, and emit the count of distinct claim_ids contributing.
Run the naive SUM over all rows beside it and report the difference as the double count that was removed.

Expected result: One row per procedure_code with summed allowed_amount over surviving, non-voided, paid Q1 2025 lines. A claim with an original and one replacement contributes only the replacement's lines; a claim whose surviving version is a void contributes nothing.

Verify it yourself

Deduped total is strictly below the naive paid-and-Q1 SUM taken across all versions whenever at least one claim carries paid Q1 lines on more than one version. If the two totals match on such a claim, the dedup did nothing.
For one claim_id known to have three versions, the line count it contributes equals the number of its paid Q1 2025 lines at its highest claim_version, not 1.
Let B be the set of claim_ids whose surviving version has at least one paid line with a Q1 2025 service_start_date. COUNT(DISTINCT claim_id) in the output equals the size of B minus the number of claims in B whose surviving version carries frequency_code 8. Comparing instead against COUNT(DISTINCT claim_id) over the whole raw table is not a void test: that gap also absorbs every claim dropped by claim_status or by service date.

Follow-up

Two rows share a claim_id and claim_version but differ on adjudicated_at. What do you do, and what does that imply about the extract?
How does the query change if the request is allowed_amount by paid_date rather than service_start_date, and which basis does a finance reconciliation want?
A void arrives for a claim already published in a closed month. How do you restate without rewriting history in the warehouse?

How do you prioritize tasks when working on multiple projects?

Medium · product sense and metrics

Approach

Name one primary metric, then the guardrail that stops it being gamed.
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.

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?

What steps would you take to ensure data integrity and quality in your analyses?

Medium · product sense and metrics

Approach

State what result would change your recommendation, so the answer is falsifiable.
Name one primary metric, then the guardrail that stops it being gamed.
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?
How would you detect that the metric is being gamed rather than genuinely improving?

Given a dataset of patient outcomes, how would you approach identifying factors that contribute to readmission rates?

Medium · product sense and metrics

Approach

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.
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?

If tasked with optimizing patient appointment scheduling, what data points would you consider?

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 an experiment to test a new treatment protocol?

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.
Name the guardrails that would stop a launch even on a positive primary result.

Follow-up

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

Explain the difference between supervised and unsupervised learning.

Medium

Approach

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.
State your assumptions explicitly before working the problem.

Follow-up

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

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?

Protocol deviations fell as new sites came online

Hard · reporting lag · denominator mix · clustering

Protocol deviations per 100 completed visits across a multi-site study fell from 4.6 to 2.9 over two months while randomisation accelerated. study_subject_visit carries study_id, site_id, subject_id, visit_number, visit_name, planned_visit_date, actual_visit_date, visit_window_days, visit_status, protocol_deviation_flag, deviation_severity, data_entry_ts and site_activation_date. Operations wants to credit a retraining rollout that began in that window. Establish whether deviation behaviour changed, and deliver a version of the series that is safe to review monthly.

Approach

Treat deviation flags as late-arriving data. Build a lag triangle of data_entry_ts minus actual_visit_date, because deviations are largely identified during monitoring review weeks after the visit, so the two most recent months are structurally undercounted in exactly the way an improvement looks.
Hold back the months that are not developed, or apply development factors fitted on fully developed months, and restate. Do this before any explanation involving training.
Attack the denominator. It counts visits with visit_status 'completed', while out_of_window and missed visits are excluded even though out-of-window attendance is itself a deviation in most protocols, so a site that pushes visits out of window lowers the rate twice over.
Standardise the visit mix. Newly activated sites contribute mostly screening and baseline visits, which carry fewer procedures and fewer opportunities to deviate than later treatment visits, so compute the rate within visit_number strata and reweight to a fixed mix before comparing months.
Split major from minor deviation_severity and report them separately. A single major deviation can remove a subject from the per-protocol analysis set while ten minor ones may not, so a pooled rate can fall while the consequential series rises.
Before attributing anything to training, model at the right level: subjects are nested in sites, so use a random intercept per site or cluster-robust standard errors. With average cluster size m and intracluster correlation rho the variance is understated by the design effect 1 + (m - 1) times rho, which at m = 60 and rho = 0.02 is about 2.2, so naive intervals are roughly 1.5 times too narrow.

Follow-up

Design a defensible evaluation of the retraining rollout, given it was deployed site by site and cannot be withheld.
Your reweighted series is flat but major deviations rose. What do you escalate and to whom?
How would you decide the holdback period, and how would you communicate a provisional number without it being quoted as final?

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.

Describe a time when you had to persuade a team to adopt your analytical recommendations.

Medium · behavioural and stakeholder questions

Approach

State the situation in two sentences and spend the rest on your reasoning.
Close with what you would do differently, concretely.
Quantify the outcome, including what you would not claim credit for.

Follow-up

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

Explain an incomplete cost series to a non-technical executive

Medium · claims runout · uncertainty · executive communication

An executive opens a dashboard showing risk-adjusted allowed PMPM down 6 percent across the last three incurred months and asks whether the cost initiative is working. You know pharmacy adjudicates within days, professional claims within weeks, and inpatient facility claims over months, so those three months are both undercounted and mix-shifted toward cheap services. There is also a genuine movement of roughly 1.5 percent in the older, complete months. Deliverable: a two-minute spoken answer, the change you make to the chart, and what you commit to telling them and when.

Approach

The probe is whether you can be honest about uncertainty without sounding evasive. Executives read hedging as not knowing, so the answer has to end in a commitment.
Separate the two claims immediately. The 6 percent is an artefact of incomplete data, and there is a smaller real movement in the months that are complete. Delivering only the debunk leaves them holding nothing.
Explain the lag in their terms: the bills for the most expensive care arrive last, so an incomplete month always looks cheap and always looks like it is improving. Keep the phrase completion factor out of the first pass.
Fix the chart instead of explaining around it. Shade or omit the incomplete months, print the paid-through date on the axis, and overlay the same series as it looked at equivalent maturity a year earlier so the shape is comparable rather than merely lower.
Close with a date and a magnitude. Commit to a restated figure once runout matures, say roughly how far you expect the 6 percent to shrink, and name what you would need to see to call the initiative working.

Follow-up

They ask for your best guess today, knowing it is provisional. What do you say?
The restated number comes back at 1 percent. How do you handle having flagged the 6 percent at all?
How do you stop this dashboard producing the same conversation next quarter?

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.