Bayer Data Scientist Interview Questions & Guide 2026

The 60-second brief

As a Data Scientist at Bayer, you sit at the exciting intersection of advanced quantitative modeling, life sciences, and global enterprise operations. This role is crucial for transforming vast, complex datasets into actionable insights that power life-saving pharmaceuticals, optimize agricultural output, and drive digital health innovations. You will tackle real-world challenges ranging from biomarker discovery and clinical trial optimization to supply chain forecasting and digital product personalization.

Nearly every loop contains a round whose deliverable is a recommendation to someone non-technical. Practise stating a conclusion, the confidence attached to it, and the cost of being wrong in each direction, because that triple is the artifact being graded.

Focus areas

• Separate censoring from events in time-to-event models
• Minimise protected health information in every extract
• Reconstruct drug exposure intervals from dispensing records

Overview

Your day-to-day impact involves designing predictive models, building robust data pipelines, and partnering closely with cross-functional teams including biologists, software engineers, and product managers. What makes this position uniquely challenging and rewarding at Bayer is the immense scale and diversity of the data ecosystem. You are not just optimizing clicks or engagement metrics; you are leveraging data science to shape products that directly improve health and nutrition on a global scale.

Expect a collaborative, intellectually stimulating environment where scientific rigor is highly valued. Whether you are presenting your research to domain experts or deploying scalable machine learning models into production, your ability to bridge raw data with strategic business value will define your success. Successful candidates will find themselves embedded in mission-driven teams that value innovation, scientific integrity, and cross-disciplinary teamwork.

The Bayer interview process

Bayer candidates report 7 rounds · ≈ 4-6 weeks. The stages below are what candidates describe, not a published process.

HR Recruiter 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

Hiring Manager Conversation

Expect a live problem with pieces of it missing, closer to a conversation than an exam. A metric moved, or somebody wants to know whether a change worked, and you are asked how you would find out. The manager is watching the first ninety seconds, specifically whether you establish what decision hangs on the answer before you start proposing methods. Candidates who open with a technique get steered back. Once the decision is clear, describe what the data would look like if the story were true, and say what you would accept as evidence that it is not.

What to demonstrate

• Whether you fix the decision the analysis serves before choosing an approach
• How you continue when you are told the data you just asked for does not exist
• Whether you state what would change your mind, not only what would confirm the hypothesis you started with
• How you size an effect before you have measured it

How to prepare

• Take a metric you know well and practise explaining in under two minutes the four things that could have moved it and how you would separate them
• Pick a recent launch or experiment and write the single number you would ask for first, plus what you would conclude if it came back flat
• Practise being interrupted: have someone remove a data source halfway through your answer and carry on without restarting

Technical Evaluations

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

What to demonstrate

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

How to prepare

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

Coding Assessments

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

Behavioral Interviews

This round decides whether you owned a decision or watched one happen nearby. Interviewers for data roles listen for the point where the analysis stopped being a report and started changing what someone did, so build each story around that hinge: what was going to happen by default, what you found, and what happened instead. The most common weakness is a story that ends at delivery. If you can name the decision your work changed and the number that moved because of it, most follow-ups become easy.

What to demonstrate

• Whether the decision was yours to influence, or whether you are narrating a team outcome in the first person
• The counterfactual: what would have been done without your analysis, and why that default was worse
• How far your involvement ran past the handoff, and whether you checked that the change did what you predicted

How to prepare

• Pick three projects and write one sentence for each naming the decision-maker, the choice in front of them, and what they chose after seeing your work. If you cannot name a person and a choice, the story is not ready for this round.
• Reconstruct the baseline for your strongest project from the original query or dashboard rather than memory, so the before-number survives a follow-up asking where it came from.
• Prepare an honest version of a project where your recommendation was overruled, including what you did with the analysis afterwards.

Project Presentation

Because the format is not fixed, prepare the reasoning rather than the ritual. Nearly every version of this round draws on the same underlying material: a design you can defend, a metric you can define exactly, an analysis whose assumptions you can state out loud. Only the wrapper changes, whether that is a take-home, a live case, a deep dive on past work, or a rough estimate on a whiteboard. Answers rehearsed to fit one shape stall the moment the shape differs. Practise naming the assumption behind a number, then saying how much the conclusion moves if that assumption is wrong.

What to demonstrate

• Whether your justification for a method survives the question 'why not the simpler thing', including when the simpler thing would have worked
• Precision under pressure: what exactly counts as an active user, a conversion or a success, over what window, with what exclusions
• Whether you carry an argument through to a recommendation instead of stopping at a list of tradeoffs

How to prepare

• For each project you plan to mention, write the metric definition in one sentence: numerator, denominator, time window, exclusions. Say it out loud once, because vagueness shows up in speech before it shows up on paper.
• Rehearse the same project at three lengths: two minutes, ten minutes, and a deep dive on one technical decision. Cutting live is harder than it sounds.
• For your headline result, write down what would have had to be true for it to be wrong, and how you ruled that out.

Where candidates lose points

Pre-post evaluation on a high-cost or high-risk cohort

Cohorts selected on an extreme value of the outcome regress toward the mean on their own. Members identified as the top 1 percent of spend in one year spend far less the next, whether or not anyone intervenes, because the selecting year captured both chronic severity and one-off events. A pre-post design on such a cohort will report savings every time. A concurrent comparison group selected by the same rule in the same period, or a regression discontinuity at the selection threshold, is the minimum credible design.

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 interference between units in a marketplace experiment

Ask whether one unit's treatment can change another unit's outcome through shared inventory, a matching pool, a social graph or a common budget. Where it can, randomise at a level that contains the spillover, such as region or time slice, and say explicitly what that costs you in statistical power.

Optimising accuracy on a heavily imbalanced target

State the base rate first, then choose the metric from the relative cost of a false positive against a false negative: precision and recall at the operating threshold, PR-AUC, or expected cost. At a 1 percent positive rate, predicting the majority class for everyone scores 99 percent accuracy and is worthless.

Questions & practice

1–2 sentences introducing the category and what it tests.

Medium · statistics and probability

Approach

1. Quantify uncertainty explicitly rather than reporting a point estimate alone.
2. Say what the estimate is of, and over what population it generalises.
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?

Build a claims lag triangle and complete recent months

Medium · chain ladder · runout · pivot

medical_claim_line has claim_id, claim_version, frequency_code, service_start_date, paid_date, allowed_amount, claim_status. Using 24 fully mature incurred months, build a development triangle of allowed dollars by incurred month and payment lag in whole months, derive cumulative completion factors by chain ladder, then estimate ultimate allowed for the three most recent incurred months at a stated paid-through date. Deduplicate to surviving claim versions before building the triangle. Return incurred_month, paid_to_date, completion_factor, estimated_ultimate.

Approach

1. Deduplicate to surviving claim versions and keep paid lines first. A triangle built on all versions develops on adjustment churn rather than on payment timing, and replacements arrive late, so the distortion concentrates in exactly the tail you are trying to estimate.
2. Compute lag as a whole-month difference between incurred month and paid month, not as day difference divided by 30. Ragged month lengths otherwise shuffle identical claims between lag buckets depending on which month they fall in.
3. Pivot to incurred_month by lag, cumulate along lag, then form age-to-age factors as the ratio of the column L+1 total to the column L total, summed over incurred months mature enough to have both. Volume weighting is the chain ladder; a simple mean of per-month ratios lets one low-volume month dominate the factor.
4. Chain the age-to-age factors from each lag to ultimate and invert: completion factor at lag L is the reciprocal of the product of factors from L onward. Apply as estimated_ultimate = paid_to_date / completion_factor.
5. State the assumption you have just made, which is that the development pattern is stable. It is not, after a claims-system migration, a network change or a processing backlog, and the most recent month's factor is the least reliable because it rests on the fewest observations while carrying the largest adjustment.

Follow-up

• A processing backlog means the last two months developed slower than history. What does your estimate do, and how would you detect that before reporting?
• Pharmacy, professional and inpatient facility develop on different clocks. How do you split the triangle, and what breaks if the service mix shifts?
• What happens to a PMPM series if someone reports an unadjusted recent month, and in which direction does the error point?

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?

1–2 sentences introducing the category and what it tests.

Medium · sql and data manipulation

Approach

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

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?

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

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

Worked solution (25 min)

1. CTE sites: site_id and site_activation_date from study_site for the study where site_activation_date IS NOT NULL. The (study_id, site_id) key already gives one activation date per site, so assert that key rather than de-duplicating defensively.
2. CTE first_rand: site_id and MIN(randomization_ts) over study_subject_visit rows for the study where randomization_ts IS NOT NULL, grouped by site_id.
3. LEFT JOIN sites to first_rand and compute days_to_first_randomization with an explicit cast of the timestamp to a date.
4. Summary: PERCENTILE_CONT(0.5) over non-null days, plus the count and share of sites with no randomization by day 90, restricted to sites activated at least 90 days before the data cut.
5. Compare the site-level row count with COUNT(DISTINCT site_id) in study_subject_visit for the study; the difference is the activated sites that have screened nobody, which is the population the metric is for.

Expected result: One row per activated site with site_id, site_activation_date, first_randomization_ts and days_to_first_randomization, NULL in both randomization columns for sites that have randomized nobody, including sites with no study_subject_visit rows at all; plus one summary row with median_days_over_enrolling_sites, sites_activated_90d_or_more and share_with_no_randomization_by_day_90.

Verify it yourself

• Site-level row count equals the number of study_site rows for the study with a non-null site_activation_date, and is greater than or equal to COUNT(DISTINCT site_id) in study_subject_visit for that study. Below it means either an activated site is missing from study_site or a visit row carries a site_id whose activation date is NULL, both data-quality findings.
• No days_to_first_randomization is negative. A negative value is a data-quality finding to raise, not a number to clamp at zero.
• The day-90 denominator excludes sites activated within 90 days of the data cut. Including them can only add sites that have not had a full 90 days, and any of those without a randomization would be miscounted as a failure.

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?

1–2 sentences introducing the category and what it tests.

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. Name the randomisation unit first; it decides the variance and what the test can detect.

Follow-up

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

1–2 sentences introducing the category and what it tests.

Medium

Approach

1. State your assumptions explicitly before working the problem.
2. Clarify what is being asked and what a complete answer would contain.
3. 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?

Score trial sites without rewarding enrollment that does not stick

Medium · site scorecards · shrinkage · guardrails · gaming

A clinical operations group ranks sites on subjects randomized per activated site-month, using study_subject_visit (study_id, site_id, subject_id, arm_code, randomization_ts, site_activation_date, visit_name, visit_number, visit_status, primary_endpoint_value, protocol_deviation_flag, deviation_severity). Bottom-ranked sites lose allocation on the next study. Define the primary metric, two guardrails, the handling of sites activated too recently to rank, and the estimator you rank on. Then state what a site under allocation pressure does to the number, and which guardrail sees it.

Approach

1. Move the numerator from randomized to randomized and retained through the protocol-defined primary endpoint visit. A subject who randomizes and withdraws consumes screening, drug supply and monitoring while contributing nothing to the analysis population, so counting them rewards the wrong behaviour precisely when a site is under pressure.
2. Build the denominator as exposure, not sites: site-months from site_activation_date to the earlier of period end and database lock, per study. Ranking on a raw count instead of a rate confuses a large site with a fast one.
3. Handle young sites by reporting rather than dropping. Sites with fewer than a stated minimum exposure are shown in an insufficient-exposure band, because excluding them is how a non-enrolling tail disappears from a scorecard, and the non-enrolling tail is usually the larger operational problem.
4. Rank on a shrunken estimate, not the raw ratio. With a few site-months of exposure the ratio has enormous variance, and a site with two site-months and one randomization will top an unshrunken table. Partial pooling toward the study mean, or a simple empirical-Bayes posterior mean, prevents rank being an artefact of small exposure.
5. Pick guardrails that read the two pressure responses. A site that loosens eligibility judgement shows an implausibly low screen-failure rate, computed as subjects with randomization_ts NULL and visit_status screen_failed over subjects screened. A site that pushes visits to hit counts shows up in major protocol deviations per 100 completed visits, reported separately from minor ones because a single major deviation can remove a subject from the per-protocol set.
6. Report the companion the median hides: the share of activated sites with no randomization by day 90. A median computed only over sites that eventually randomized is silent about the sites that never did.

Worked solution (30 min)

1. Define the numerator as subjects with non-null randomization_ts whose primary endpoint visit reaches visit_status completed with a non-null primary_endpoint_value, per study and site.
2. Compute site-months of exposure from site_activation_date to period end or database lock, and set the minimum exposure below which a site is banded rather than ranked.
3. Compute the raw rate, then the shrunken estimate by partial pooling toward the study mean, and print both columns side by side so the shrinkage is visible rather than hidden.
4. Specify the two guardrails with numerator, denominator and window, keeping major and minor deviations as separate series.
5. Add the no-randomization-by-day-90 share across all activated sites, so the non-enrolling tail appears on the same page as the ranking.

Expected result: A scorecard whose primary metric is randomized-and-retained subjects per activated site-month per study, ranked on a shrunken estimate with the raw rate shown beside it; sites below a stated minimum exposure reported in an insufficient-exposure band rather than excluded; guardrails of screen-failure rate and major protocol deviations per 100 completed visits reported separately from minor ones; and a companion share of activated sites with no randomization by day 90.

Verify it yourself

• Compare raw and shrunken rankings. The largest rank movements should be concentrated among the lowest-exposure sites; if a high-exposure site moves far, the pooling is misconfigured.
• Confirm the denominator's site-months sum matches an independent count of months between activation and period end across all sites.
• Check that screen failures are being counted at all: a site reporting a zero screen-failure rate across dozens of screened subjects is a data-capture problem or an eligibility-judgement problem, and both need an answer.
• Verify that excluding the insufficient-exposure band does not change the study-level mean materially; if it does, those sites carry more of the study than the banding implies.

Follow-up

• A site has the best randomized-and-retained rate and the worst major deviation rate. What do you recommend, and what would change your answer?
• Your shrunken ranking moves a site from 3rd to 14th. How do you explain shrinkage to the site's operations lead in two sentences?
• Primary endpoint retention takes 18 months to observe. What do you rank on in the meantime, and what bias does that interim choice introduce?

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.

Getting an analysis acted on

Work that nobody used is a common and unflattering pattern in data careers, and interviewers probe for it. Have a story about an analysis that changed a decision, and be specific about how you got it in front of the person who could act. Also have one about work that went nowhere, with your reading of why.

1–2 sentences introducing the category and what it tests.

Medium · behavioural and stakeholder questions

Approach

1. Quantify the outcome, including what you would not claim credit for.
2. Name the disagreement or constraint, and how you resolved it with evidence.
3. Pick a story where you drove the decision, not one where you observed it.

Follow-up

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

Sequence three urgent requests with one analyst-week available

Medium · prioritisation · tradeoffs · stakeholder communication

Three requests land on Monday and you have one week. An actuarial team needs incurred-claims completion factors restated before a filing deadline on Thursday. A clinical programme owner wants a deterioration model refreshed because its calibration has drifted in one region. A trial operations team wants site enrolment forecasts for a portfolio review in two weeks. Each requester believes theirs is blocking. Deliverable: your sequence with the reasoning, the message you send to whoever is deprioritised, and the smaller artefact you hand each of the two you cannot fully serve.

Approach

1. The probe is whether you prioritise on consequence and reversibility rather than on who asked loudest or most recently.
2. Classify each request by what happens if it slips. A regulatory or contractual deadline is irreversible on its date, a drifting model is causing harm every day it keeps running, and a portfolio review can absorb a provisional number. That ordering is defensible to all three requesters because it does not depend on your preferences.
3. Take the deadline-bound work first, but scope it to the minimum defensible output, because completion factors feeding a filing carry a different error tolerance than a slide.
4. Do not let the drifting model simply wait. Quantify the harm cheaply by comparing calibration in the affected region against the rest, and if it is materially miscalibrated propose flagging or suppressing its output for that region within the hour rather than at the end of a refresh.
5. Give each deprioritised requester something real: a provisional forecast with its uncertainty and a refresh date, or a diagnostic that tells them whether their problem is urgent. Say no explicitly with a date rather than going quiet, because silence is what produces escalation.

Follow-up

• The programme owner escalates to your manager. What do you want your manager to be able to say?
• Midweek the actuarial work needs two more full days than you estimated. What gives?
• How does your answer change if the drifting model drives a clinical outreach list rather than a report?

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?