Question 3 of 6 Intermediate Scenario

How can a LEFT JOIN silently change denominators in cohort analysis?

Short answer

A LEFT JOIN keeps all left-side rows but may multiply them through repeated matches. Filtering the right-hand side afterward can also drop unmatched cohort members.

Why

Consider 200 enrolled patients, 50 of whom have three test results each, and the rest have none. A patient-to-test LEFT JOIN returns 300 rows, not 200: 150 rows for tested patients and 150 NULL-extended rows. COUNT(*) now measures joined rows, not people.

Filtering WHERE test.value > 0 removes all patients lacking tests, changing the target population. To compute patient-level denominators, first define eligibility and use COUNT(DISTINCT patient_id) or aggregate tests to the intended grain before joining, depending on the question. For a test-positive rate, decide whether untested people are negative, missing, or out of scope.

Check row count, distinct IDs, coverage and duplicate key frequencies. Never interpret a denominator without documenting its unit of analysis.

Common mistakes

Calculating prevalence from COUNT(*) after joining patients to repeated measurements

The interviewer might ask next

What changes if only patients with at least one valid follow-up test are eligible?