Quest Diagnostics Data Scientist Interview Questions & Guide 2026

The 60-second brief

The role of a Data Scientist at Quest Diagnostics is pivotal in harnessing data to drive actionable insights that enhance public health outcomes. Data Scientists analyze vast datasets, integrating information from various healthcare sources to inform business decisions, improve patient care, and enhance operational efficiency. By translating complex data into understandable narratives, they play a crucial role in shaping the company’s strategic direction.

Most of the loop measures decision-making under uncertainty rather than recall. You are scored on whether you state your assumptions, commit to an estimate you can defend, and say explicitly what evidence would change it.

Focus areas

• Risk-adjust outcomes before comparing providers or panels
• Minimise protected health information in every extract
• Correct for claims runout before reporting recent months

Overview

At Quest Diagnostics, Data Scientists work on innovative projects that have a direct impact on the healthcare landscape. They collaborate with teams across the organization, including engineering, product management, and public health, to create solutions that optimize diagnostic services and improve patient experiences. The complexity and scale of the data, coupled with the potential for significant real-world impact, make this role both challenging and rewarding.

Candidates can expect to engage in a dynamic work environment where they will contribute to meaningful health initiatives, utilizing advanced analytical techniques and tools. This role is not just about crunching numbers; it’s about telling compelling stories that can influence healthcare decisions at every level.

The Quest Diagnostics interview process

Quest Diagnostics candidates report 2 rounds · ≈ 2-4 weeks. The stages below are what candidates describe, not a published process.

Phone Screening

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

In-depth Interviews

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

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.

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.

Reading a dozen metrics with no multiplicity control

Nominate one primary metric before launch and treat the rest as guardrails or exploratory, with Bonferroni or Benjamini-Hochberg applied when you intend to make claims from them. Twenty independent tests at 0.05 under the null produce at least one false positive about 64 percent of the time.

Answering a product-sense question with a list of features

Answer with a decision and the measurement that would settle it: the hypothesis, the primary metric, the guardrails, and the result that would make you not ship. A feature brainstorm cannot be wrong, which is exactly why it earns no points.

Questions & practice

Write a function to calculate the mean and standard deviation of a dataset.

Medium · statistics and probability

Approach

1. Write down the assumption the method needs before you use the method.
2. Quantify uncertainty explicitly rather than reporting a point estimate alone.
3. Translate the result into the decision it informs, in one plain sentence.

Follow-up

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

What machine learning algorithms are you most comfortable with, and why?

Medium · machine learning and modelling

Approach

1. Say how the offline result would be validated online before it is trusted.
2. Set a baseline first, so any model has something honest to beat.
3. 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

1. 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.
2. 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.
3. 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.
4. 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.
5. Order the output by failing_share descending so the run has a lead finding rather than four equal-weight lines.

Worked solution (25 min)

1. Drop cancelled rows, then compute the set of result_ids referenced by supersedes_result_id and keep rows not in that set.
2. Rule one: group the survivors by (order_id, loinc_code) and count rows above 1.
3. Rule two: mask on resulted_ts < specimen_collected_ts over rows where both timestamps are present.
4. 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.
5. 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 would you optimize a slow-running query in a SQL database?

Medium · sql and data manipulation

Approach

1. Say which table is the grain you start from, and join outward from it.
2. Handle the rows that do not match: a LEFT JOIN with a NULL check is usually the question.
3. Compute rates by summing numerator and denominator separately, never by averaging rates.

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?

Compute allowed PMPM by incurred month without span fan-out

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

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

Approach

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

Worked solution (45 min)

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

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

Verify it yourself

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

Follow-up

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

What steps would you take to investigate an unexpected trend in patient data?

Medium · product sense and metrics

Approach

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

Follow-up

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

Discuss the trade-offs of using different data structures in your analyses.

Medium · product sense and metrics

Approach

1. State what result would change your recommendation, so the answer is falsifiable.
2. Fix the population and the time window before naming any metric.
3. 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?

Given a dataset on patient outcomes, how would you identify key predictors of success?

Medium · product sense and metrics

Approach

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

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?

How do you prioritize competing tasks and projects?

Medium · product sense and metrics

Approach

1. Restate the decision this analysis has to support, and who acts on the answer.
2. Fix the population and the time window before naming any metric.
3. 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?
• Which segment would you cut first, and what would that rule out?

How would you approach designing an experiment to test a new diagnostic tool?

Medium · a/b testing and experimentation

Approach

1. Say whether units interfere with each other, and switch design if they do.
2. Name the guardrails that would stop a launch even on a positive primary result.
3. Decide the analysis before seeing data, including how long it runs and when you look.

Follow-up

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

Can you explain the importance of statistical significance in your analyses?

Medium · a/b testing and experimentation

Approach

1. Name the guardrails that would stop a launch even on a positive primary result.
2. Decide the analysis before seeing data, including how long it runs and when you look.
3. Say whether units interfere with each other, and switch design if they do.

Follow-up

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

How do you approach data cleaning and preprocessing?

Medium

Approach

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

Follow-up

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

Set the decision rule when cost and denial guardrails conflict

Medium · guardrail conflict · denials · non-inferiority bound · audit sampling

Utilization management is asked to cut risk-adjusted allowed PMPM, and its lever is prior authorization and first-pass denial on advanced imaging and specialty drugs. The scorecard guardrail is first-pass denial rate from medical_claim_line (claim_status, denial_reason_code, claim_version, received_date) paired with the overturn rate on appealed denials. The two move in opposite directions by construction. Write the decision rule: which metric binds, at what pre-declared threshold, who adjudicates a breach, and what happens on breach. Do not resolve the conflict by weighting both into one score.

Approach

1. Say why a composite is the wrong instrument: the weights get chosen after someone has seen which way the quarter went, and any weighted sum lets a large enough cost win purchase an unbounded increase in wrong denials. A guardrail is a constraint, not a term in an objective.
2. Sharpen what the guardrail is actually protecting. A higher denial rate is not by itself a harm, since some denied claims should be denied. The harm is denials that should not have been made, so split first-pass denials by denial_reason_code into administratively correctable ones (eligibility, coding, missing documentation) and clinical-necessity ones, and put the bound only on the clinical group.
3. Fix the measurement of the guardrail before setting its threshold: count the denominator at claim_version 1 so replacements (frequency_code 7) and voids (8) are not double counted, hold the month back for 60 days of adjudication runout, and report by received_date.
4. Get the direction of the appeal-selection bias right. Appeals are filed on the denials the appellant already believes are wrong, by the members and providers with the staffing, literacy and dollar threshold to file at all, and an appeal usually arrives carrying documentation the first adjudication never had. The overturn rate on appealed denials therefore runs well above the wrong-denial rate across all denials, often by a multiple, and it moves with filing behaviour as much as with adjudication quality. The unbiased population estimate is a blind re-review of a random sample of all denied clinical-necessity lines each quarter, judged on the record as it stood at first adjudication; the appeal series is a fast indicator read against its own history between audits, not a second estimate of the same quantity.
5. Write the rule as a bound with a consequence: risk-adjusted allowed PMPM is the primary and binds only while the audited wrong-denial rate stays within a pre-declared absolute margin of its trailing four-quarter mean. Breach stops the program's expansion rather than discounting its score, and the margin is set before the quarter opens.
6. Name the adjudicator and the cadence: a clinical owner outside the utilization function, reviewing at the stated paid-through date, with the breach consequence written down before any result is known.

Worked solution (25 min)

1. Restate the guardrail metric properly: numerator is claim lines denied at first adjudication, denominator is lines received in the month counted at claim_version 1, held 60 days, broken out by denial_reason_code group.
2. Split denial_reason_code into administratively correctable and clinical-necessity groups and say which function owns each, since the two have different fixes and different harms.
3. Write the appeal-selection argument in two sentences, including which way it runs, and specify the random blind re-review: the frame it is drawn from, namely all denied clinical-necessity lines rather than appealed ones, plus sample size, who reviews, what record they are shown, blinding, and quarterly cadence.
4. State the bound as an absolute margin on the audited wrong-denial rate against its trailing four-quarter mean, with the margin fixed before the period.
5. Write the breach consequence and the named adjudicator, and note that both are recorded before results exist.

Expected result: A written rule in which risk-adjusted allowed PMPM is primary; the binding guardrail is the audited wrong-denial rate on clinical-necessity denials, estimated from a blind random re-review of all denied clinical-necessity lines, with the appeal overturn rate kept as a between-audit indicator that runs above the audited rate and never sets the bound; the bound is a pre-declared absolute margin against a trailing four-quarter mean; a clinical owner outside utilization management adjudicates; and a breach halts expansion rather than adjusting a score.

Verify it yourself

• Recount the denominator with and without version dedup. If the two differ by more than a percent or two, the incumbent number has been counting resubmissions.
• Re-run a closed month at two extract dates 60 days apart. A moving denial rate means the runout hold is too short.
• Compare the audited wrong-denial rate to the appeal overturn rate for the same quarter. The overturn rate should be the higher of the two, usually by a multiple, because appeals are filed on the denials most likely to be wrong and often add documentation the first adjudication never saw. An audit rate at or above the overturn rate is the anomaly: check that the sample was drawn from all denied clinical-necessity lines rather than only appealed ones, and that the reviewers were blinded and shown the record as it stood at first adjudication.
• Confirm the margin, the adjudicator and the consequence are all recorded with a timestamp before the period's data was available.

Follow-up

• Denials rose and the overturn rate fell. Give the benign reading and the bad reading, and the one number that separates them.
• What happens to your bound if the appeal filing rate per denial drops 30 percent in the same quarter?
• How large a random audit sample do you need to detect a 2 percentage point rise in wrong denials off a 6 percent base, and what does that cost in clinician review hours?

Readmission rate rose while every facility improved

Medium · simpsons paradox · mix shift · risk adjustment

Quarter over quarter the 30-day unplanned readmission rate for an attributed population rose from 14.2 to 15.1 percent, yet all eleven facilities in encounter show a flat or falling rate over the same period. You have encounter (facility_id, admit_ts, discharge_ts, encounter_type, admission_type, discharge_disposition, drg_code, is_planned_admission, index_encounter_id) and twelve months of prior medical_claim_line for comorbidities. Explain the direction reversal numerically, and state what the population-level number should be reported as instead.

Approach

1. Confirm the reversal is arithmetic, not a definition difference, by recomputing both the facility rates and the pooled rate from the same query with the same index-stay exclusions.
2. Decompose the pooled change exactly with a Kitagawa split. With facility weights w and within-facility rates r, the change equals the sum over facilities of (w_1 - w_0) times (r_0 + r_1)/2, the mix term, plus the sum of (w_0 + w_1)/2 times (r_1 - r_0), the within term. If the mix term exceeds the total change and the within term is negative, the reversal is volume shifting toward facilities with higher baseline rates.
3. Ask why the weights moved. Check index-stay volume by facility, and check whether an exclusion changed behaviour: acute-to-acute transfers (discharge_disposition 'transfer_acute') must not open an index stay, and if one facility began transferring more, its denominator shrinks toward its sickest retained patients.
4. Check case mix inside the facilities too, using drg_code and comorbidities drawn from the 12 months of claims before admit_ts, excluding complications of the index stay itself. A facility rate can be flat while its case mix worsens.
5. Replace the raw pooled rate with an observed over expected ratio, where expected is the sum of predicted probabilities from a model fitted on age, sex and prior comorbidities. Raw rates are not comparable across a population whose facility and case mix is moving.

Follow-up

• Your O/E ratio is 1.04 with a 95 percent interval of 0.96 to 1.13. What does that let you say to a facility, and what does it not?
• If one facility contributes 40 percent of index stays, how does that change how you present the decomposition?
• How would you handle a member with two index stays in the quarter, where the second is also a readmission from the first?

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 project you worked on and how you overcame obstacles.

Medium · behavioural and stakeholder questions

Approach

1. Pick a story where you drove the decision, not one where you observed it.
2. State the situation in two sentences and spend the rest on your reasoning.
3. 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?

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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?

Scope a one-line request for a readmission rate

Easy · scoping · measure specification · stakeholder communication

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

Approach

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

Follow-up

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