ORIGINAL COMPANY-CONTEXT PRACTICE
Count research outcomes without duplicating participants
Original practice scenario inspired by Gilead Sciences, Inc.'s public business context; this is not a reported interview question or a description of its internal systems. A fictional research operations database stores participants, visits, measurements, and protocol amendments. Explain a query for one prespecified endpoint per participant at a defined visit, including missing measurements and repeated attempts. This is data-engineering practice, not clinical interpretation.
Written for independent practice using company context; this is not a verified question from a company interview.
A REASONED APPROACH
Model answer.
Define the eligible participant population and endpoint-selection rule before joining measurements. Repeated attempts and protocol versions can otherwise produce several conflicting rows for one participant.
Work through it
- Select the applicable protocol version and target visit window using recorded eligibility rules. Preserve eligible participants who have no qualifying measurement.
- Rank qualifying measurements by the prespecified rule, such as accepted status then acquisition order, with a complete tie-break. Keep one endpoint row per participant.
- Report observed and missing counts separately and retain exclusion reasons. Test multiple attempts, a boundary visit time, a withdrawn measurement, and a participant with no visit.
Tradeoffs to discuss
- Different defensible selection rules answer different questions. The software should implement the approved analysis definition rather than choose a convenient favorable value.
Common pitfalls
- An inner join hides missing endpoints.
- MAX(measurement) selects a value by magnitude, which is not a valid substitute for a documented acquisition-selection rule.