A window function computes a value for each row from the rows around it, without collapsing them into groups:
SELECT encounter_id, patient_id,
ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY admit_date, encounter_id) AS visit_number
FROM hd_encounters;
- PARTITION BY patient_id restarts the window for each patient. Without it the numbering runs across everyone.
- ORDER BY sets the order inside the window. Two encounters can share a day (an emergency visit and the admission that followed), so break ties with encounter_id.
- ROW_NUMBER numbers every row; RANK gives tied rows the same number.
- LAG reads the previous row, LEAD the next one.
- QUALIFY filters on a window result, the way HAVING filters groups.
Each patient's encounters numbered from 1.