Rank Diagnostic Sequences in SQL | Dataford Interview Questions

Your question is Rank Diagnostic Sequences in SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Syneos Health clinical data reviewers need to identify recurring diagnostic patterns across patient visits. A diagnostic sequence is the ordered list of non-null diagnosis codes recorded during one completed visit.

Write a PostgreSQL query to find the three most frequent diagnostic sequences for completed visits in 2025.

Requirements

Build each visit-level sequence by ordering diagnoses by diagnosis_order and concatenating codes with >.
Count how many qualifying visits produced each sequence.
Use the RANK() window function to rank sequences by frequency, including ties.
Return sequences with rank less than or equal to 3, ordered by rank and sequence.

Schema

patient_visits

Column	Type	Description
visit_idPK	INT	Unique visit identifier
patient_id	INT	Patient identifier
visit_date	DATE	Date of the patient visit
visit_status	VARCHAR(20)	Visit status such as completed or canceled

visit_diagnoses

Column	Type	Description
diagnosis_idPK	INT	Unique diagnosis record identifier
visit_id	INT	Visit associated with the diagnosis
diagnosis_code	VARCHAR(20)	ICD diagnosis code
diagnosis_order	INT	Position of the diagnosis within the visit