Interview guides · Alphatec Spine

Alphatec Spine Data Scientist Interview Questions & Guide 2026

Causal inference drills for Alphatec Spine data scientist prep: diff-in-diff, matching, instruments, and the limits of observed data.

The 60-second brief

As a Senior Data Scientist at Alphatec Spine, you are at the intersection of advanced computational science and life-saving surgical intervention. You will play a pivotal role in the EOS Insight platform, developing predictive algorithms and statistical shape models that directly influence how surgeons plan and execute complex spinal procedures. Your work is not just about data; it is about providing the precision necessary to improve patient outcomes in a highly regulated and high-stakes clinical environment.

Learn the economics of the product category before the loop. Marketplaces, subscription products and ad-supported products turn on different core quantities (match rate and liquidity, retention and churn, fill rate and yield) and fail in different characteristic ways.

Focus areas

Risk-adjust outcomes before comparing providers or panels
Separate censoring from events in time-to-event models
Build member-month denominators from overlapping coverage spans

Overview

This position demands a unique blend of technical rigor—specifically in linear algebra, machine learning, and optimization techniques—and the ability to translate biomechanical needs into scalable software solutions. You will be the bridge between internal stakeholders, including marketing and surgeon partners, and the engineering teams building the future of surgical technology. If you are driven by the prospect of seeing your algorithms applied in the operating room to enhance surgical precision, this role offers a rare opportunity to drive tangible clinical impact.

The Alphatec Spine interview process

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

Initial Screening

Data Scientist covers at least four different jobs: experimentation, product analytics, causal work on observational data, and applied modelling that ships into a system. A screening call is the cheapest place to find out which of them is being hired for, and doing that diagnosis openly reads as senior rather than fussy. Ask what the last few pieces of work on the team actually were, and roughly how a week splits between querying, modelling and stakeholder time. Then say which parts of that you have done and which you have not. Claiming the whole range is the fastest way to be caught one round later.

What to demonstrate

Whether you can distinguish the flavours of the role and locate your own experience inside one of them honestly
Whether you name what you have not done instead of stretching to cover every line of the posting
Whether your hard constraints (notice period, location, work authorisation, level) surface now rather than at offer stage

How to prepare

Map the last two years of your time into rough percentages across query writing, experiment design, modelling and stakeholder work, so a question about scope has a real answer
Mark every responsibility in the posting as done, adjacent or new, and prepare one sentence for each adjacent item naming the closest thing you have actually built
Decide which logistics are non-negotiable before the call so you can state them in one sentence rather than negotiating live

Technical Assessment

Before anything else, this round is a reading test. You are given a small schema and a question phrased in business language, and most of the difficulty sits in the gap between them. Who counts as an active user, does a refunded order still count as an order, is that date column an event time or a load time. Weak answers start typing immediately and compute something precise about the wrong population. Strong ones pin the definition in one sentence, name the column that encodes it, then write the query. On a timed assessment with nobody to tell, write the definition in a comment anyway.

What to demonstrate

Whether an ambiguous term becomes a specific column and filter before any computation happens
Whether you read the schema for keys and cardinality rather than only for column names
Whether the result answers the question at the grain it was asked at, per user or per session or per day

How to prepare

Take three metrics you already use and write down the exact filter and exact grain behind each, then practise stating one of them in a single sentence out loud
On a schema you have never seen, spend the first minute writing what one row of each table means and which key it is unique on, then predict which joins can duplicate rows
Rehearse a version where the definition changes halfway through, and edit the query you have instead of starting over

Cross-Functional Discussion

An added round often puts you in front of someone outside the core hiring team: a partner engineer, a product owner, a domain expert, sometimes a more senior manager. The question they are really asking is not whether you can do the work but whether they would trust a number that came from you. That changes what a good answer looks like. Lead with what the decision cost and what it changed, keep the method available but not central, and be plain about the limits of your evidence. Overstating a result is the fastest way to lose this round.

What to demonstrate

Whether you can explain a technical choice to someone who will never read your code, without either flattening it into nothing or hiding inside jargon
Honesty about evidence strength: what the analysis establishes, what it only suggests, and what it cannot say at all
How you take disagreement, specifically whether you update on a good objection, hold your position with reasons, or fold on contact

How to prepare

Write the two-sentence version of your most technical project for a non-specialist, then check that neither sentence needs a method name to make sense.
For one result you are proud of, write the strongest objection someone could raise and a response that concedes the part of it that is correct.
Prepare one decision that turned out to be wrong: how you found out, what it cost, and what you changed afterwards. A senior cross-functional interviewer asks for this more often than a technical one does.

Where candidates lose points

Immortal time in adherence, treatment, and enrolment definitions

Classifying members as adherent, treated, or programme-enrolled requires them to survive and stay covered long enough to accumulate the defining events. That guaranteed event-free interval is assigned to the exposed group, so the exposure looks protective for reasons that have nothing to do with the treatment. Adherence studies are the classic case: measuring 12-month proportion of days covered and then comparing mortality builds survival into the exposure definition. Use time-varying exposure or a landmark analysis with the classification window excluded from follow-up.

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.

Naming a model class before naming the deployment constraints

Set out the latency budget, the label delay, the retraining cadence, the interpretability requirement and the number of labelled examples, then pick the model that fits them. A boosted-tree answer to a problem where each decision must be explained to the affected user is a well-executed answer to the wrong question.

Interpreting a change before checking data quality and logging

Spend the first pass on row volume by day, null rates, duplicate keys, and whether the step change lands on a release or tracking-migration date. A discontinuity that coincides with a deploy is an instrumentation hypothesis before it is a behavioural one.

Questions & practice

How would you approach the development of a predictive algorithm for surgical planning?

Medium · machine learning and modelling

Approach

Check what information would not exist at prediction time, and exclude it.
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.

Follow-up

Where could label leakage enter this setup?
What would you monitor after launch to know the model is still valid?

Proportion of days covered with shifted, truncated refill intervals

Hard · interval merging · adherence · path dependence

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

Approach

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

Follow-up

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

Collapse claim versions before summing allowed amounts by member

Easy · claim versioning · groupby · deduplication

You are given medical_claim_line as a pandas DataFrame: 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. Return total allowed_amount per member for service dates in one stated calendar month, counting each claim once at its surviving version and excluding any claim whose surviving version is a void. Multi-line claims must keep every line of that surviving version. Output columns: member_id, allowed_amount, sorted descending. Do not deduplicate on claim_id alone.

Approach

Separate the two grains out loud before writing code: versions live at the claim level, dollars live at the line level. Every bug in this exercise comes from mixing them.
Compute the surviving version per claim with a groupby transform of max over claim_version, then keep the lines whose claim_version equals that value. A transform keeps the frame at line grain, which a sort plus head(1) does not.
Drop whole claims whose surviving version carries frequency_code 8. A void is not a zero-dollar line, it retracts the claim, so filtering rows rather than claims leaves the prior version's lines behind.
Filter to claim_status 'paid' and state the assumption: denied, pended and reversed lines carry an allowed_amount that never became a liability, so including them overstates cost.
Restrict service_start_date to the month boundaries, then groupby member_id and sum. Sort descending and return.

Worked solution (20 min)

Build a max-version lookup per claim_id with groupby('claim_id')['claim_version'].transform('max') and keep rows where claim_version equals it.
From the surviving rows, collect the claim_ids whose frequency_code is 8 and drop those claim_ids entirely.
Filter claim_status to 'paid' and service_start_date into the month window.
Group by member_id, sum allowed_amount, sort descending, reset the index.

Expected result: A two-column frame member_id, allowed_amount. On a claim with versions 1, 7, 7 only the third version's lines contribute; a claim whose latest version is frequency_code 8 appears nowhere. Total allowed is strictly below a naive sum over all rows whenever any claim in the sample was adjusted, and equal to it when none was.

Verify it yourself

The distinct claim_id count in your filtered frame equals the count of distinct claim_ids whose surviving version is not a void.
The line count you kept for each surviving claim equals that claim's line count at its max version, not 1.
A naive sum over every row is greater than or equal to your total, with equality only when no claim has claim_version above 1.

Follow-up

A claim has versions 1, 7, 7 where the second replacement has fewer lines than the first. What does your code return and is that correct?
How would you handle a claim whose lines span two calendar months at the boundary of the reporting window?
The same logic has to run over 400 million lines in a warehouse. What changes, and what stays the same?

Compute allowed PMPM by incurred month without span fan-out

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

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

Approach

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

Worked solution (45 min)

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

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

Verify it yourself

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

Follow-up

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

Report site activation to first randomization without hiding non-enrollers

Medium · grain collapse · left join · censoring

study_site holds one row per (study_id, site_id): site_activation_date, which is NULL for a site selected but never activated, and site_status. study_subject_visit holds subject_visit_id, study_id, site_id, subject_id, randomization_ts (NULL for screen failures), visit_name, visit_number, actual_visit_date and visit_status, with one row per subject per protocol visit, so randomization_ts repeats across every visit of a randomized subject and a site that has screened nobody has no rows there at all. For one study_id and a stated data cut date, return per activated site the activation date, the first randomization_ts and the days between them, with activated sites that have randomized nobody still present. Add a study-level summary giving median days to first randomization and the share of activated sites with no randomization by day 90.

Approach

Take the site universe from study_site, never from study_subject_visit. The visit table can only contain sites that have screened at least one subject, so a universe built from DISTINCT site_id there omits exactly the sites this metric exists to expose. Filter study_site to the study and to site_activation_date IS NOT NULL, then LEFT JOIN the per-site first randomization.
Collapse the grain before aggregating anything on the visit side. The table repeats randomization_ts across every visit of a randomized subject, so any count taken at row grain is inflated by visits per subject. MIN(randomization_ts) per site is safe, COUNT is not.
State the date boundary rule. randomization_ts is a timestamp and site_activation_date is a date, so a randomization at 23:40 local time can land on either side of a day boundary depending on the zone the timestamp is stored in. Cast once, deliberately, and say which zone.
Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days) for the median and be explicit that it ignores NULLs, so it is a median over enrolling sites only. That is exactly why the non-enroller share is reported beside it rather than instead of it.
Censor the day-90 denominator against the data cut. A site activated 40 days before the cut has not had 90 days to randomize, so it cannot be scored on that bar and belongs outside that denominator.

Follow-up

The same site_id appears under two studies. What breaks, and what is the correct grain for this metric?
Would you present this as a median, as a Kaplan-Meier curve of time to first randomization, or both, and what does the curve show that the median cannot?
A site randomized one subject who withdrew at visit 2. Does that site count as enrolling here, and how does that differ from a randomized-and-retained measure?

How do you ensure your code remains production-quality while working in a research-heavy environment?

Medium

Approach

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

Follow-up

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

Can you explain how you have utilized linear algebra in a previous project?

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

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

Critique a denial rate slide before it is shown

Easy · metric critique · claim versions · runout · mix shift

A weekly slide says first-pass denial rate improved from 9.1 percent to 7.4 percent month over month. It was computed from medical_claim_line as rows with claim_status 'denied' divided by all rows with a received_date in the month. You cannot query anything. List every assumption the number rests on, restate the metric so two analysts computing it independently would get the same value, and give the three questions you would ask before the slide is presented.

Approach

Work the grain first. The denominator counts all rows, so a claim resubmitted after a denial contributes its original line, its replacement (frequency_code 7) and possibly a void (8). Both numerator and denominator are inflated by exactly the claims most likely to have been denied, which makes the rate a function of resubmission behaviour rather than adjudication.
Work maturity second. A line received late in the month may still be pended at extract time, and pended lines are neither paid nor denied. If pends resolve to denials later, the most recent month is understated by construction, which looks exactly like improvement. Ask for the paid-through date and the adjudication runout hold.
Work the unit third. A denial rate on claim lines and a denial rate on claims are different numbers, and a single denied line on a twenty-line facility claim is a different operational event from a wholly denied professional claim. The spec has to pick one and say so.
Work mix fourth. Denial reasons have different owners and different volumes: eligibility denials belong to enrollment operations, coding denials to provider relations. A shift in submission mix across providers, place of service or reason code moves the blended rate with no change in any component, so the restated metric reports by denial_reason_code group.
Work precision last. A 1.7 point move is decisive on two million lines and meaningless on three thousand. The restated metric carries the denominator count, and a slide without it cannot be read.

Worked solution (15 min)

Write the assumption list as a numbered set: single version per claim, adjudication complete at extract, line grain intended, stable reason-code and provider mix, denominator large enough for the move to be real.
Restate the metric with numerator as lines denied at first adjudication, denominator as lines received in the month counted at claim_version 1, reported by received_date month, held 60 days, broken out by denial_reason_code group, and published with the denominator count.
Write the three questions in the order that would kill the slide fastest: what is the paid-through date, how many lines are in each month's denominator, and did the reason-code mix move.
State how the restated number is presented, with the most recent month marked provisional and restated at each refresh.

Expected result: A restated specification reading: numerator is claim lines whose first adjudication returns claim_status denied; denominator is lines with received_date in month M counted at claim_version 1; the month is held until 60 days of adjudication runout have passed; the rate is broken out by denial_reason_code group; and both the denominator count and the paid-through date appear on the slide, with the most recent month marked provisional. The three questions are the paid-through date, the denominator size, and whether reason-code mix shifted.

Verify it yourself

The restated denominator must be smaller than the original, since version-1 filtering can only remove rows. If it is not, the version filter is not doing anything.
Both months should be recomputable at the same paid-through age; comparing a mature month to a two-week-old month is the defect the hold is meant to prevent.
The reason-code breakout should sum back to the blended rate, which is the check that the mix decomposition is complete.

Follow-up

The rate is still down after version dedup and a 60-day hold. What is the next thing you check before anyone celebrates?
Which of these problems would also affect a paid-amount series computed the same way?
How would you present the restated number so the audience understands the prior month is provisional?

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.

How do you handle communication with non-technical stakeholders, such as surgeons or marketing teams?

Medium · behavioural and stakeholder questions

Approach

Pick a story where you drove the decision, not one where you observed it.
State the situation in two sentences and spend the rest on your reasoning.
Name the disagreement or constraint, and how you resolved it with evidence.

Follow-up

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

What is your experience with statistical shape models or similar geometric methods?

Medium · behavioural and stakeholder questions

Approach

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

Follow-up

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

Can you describe a time you had to pivot your technical approach based on user feedback?

Medium · behavioural and stakeholder questions

Approach

Quantify the outcome, including what you would not claim credit for.
State the situation in two sentences and spend the rest on your reasoning.
Pick a story where you drove the decision, not one where you observed it.

Follow-up

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