ORIGINAL COMPANY-CONTEXT PRACTICE
Find samples missing a required processing stage
Original practice scenario inspired by Thermo Fisher Scientific Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A synthetic lab database has samples, required_stages by assay type, and stage_runs with retry attempts and statuses. Explain a query that finds each sample's missing successful stages without counting failed retries as completion.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Construct the required sample-stage pairs first, then test whether a qualifying successful run exists for each pair. Retried attempts should not multiply requirements or erase missing stages.
Work through it
- Join each eligible sample to stages required for its assay type and the applicable requirement version. Define whether canceled samples belong in the report.
- Use NOT EXISTS against successful stage runs keyed by sample and stage. Apply the documented cutoff or method-version rule inside that existence test.
- Return sample_id and missing_stage in a deterministic order. Test duplicate successes, failed-then-successful retries, a sample with no runs, and an assay with no requirements.
Tradeoffs to discuss
- A completeness report may need historical requirement versions. Reinterpreting old samples under a new stage list changes the business question.
Common pitfalls
- COUNT(stage_runs) measures attempts, not completed stages.
- An inner join to runs removes the very samples whose missing work must be reported.