Public-health analysts want clinics ranked by outbreak case counts within each district without skipping ranks after ties. Which window function should they use?
Select an answer to reveal the explanation.
Short Explanation
Tied clinic counts are like two firehouses with the same call volume—DENSE_RANK keeps them neck-and-neck without leaving a gap in the district board.
Full Explanation
DENSE_RANK is the correct window function when public-health analysts rank clinics by outbreak case counts within each district and must not skip ranks after ties. DENSE_RANK assigns ranking within a partition and, after a tie, continues with the next consecutive rank—so two clinics tied for first both get rank 1 and the next clinic gets rank 2, which matches municipal outbreak dashboards that should not leave gaps after ties.
OPENJSON fails by concept because it shreds JSON into rows; it does not rank clinics. EDIT_DISTANCE_SIMILARITY fails because it scores string similarity, not numeric case-count ranking within districts. CREATE EXTERNAL MODEL fails because it registers an external AI model for inference scenarios; it is unrelated to window ranking of outbreak counts.
Exam caveat: Distinguish RANK (gaps after ties), DENSE_RANK (no gaps), and ROW_NUMBER (unique sequence even on ties). When the stem explicitly forbids skipping ranks after ties, DENSE_RANK is the intended answer—not JSON, fuzzy string, or AI-model distractors.
Operational check: Build a district partition with two clinics sharing the top case count and a third clinic below; verify DENSE_RANK yields 1, 1, 2; confirm PARTITION BY district_id isolates rankings; and publish the board only after epidemiology staff sign off on tie handling.