SQL window functions for patient pathways

Most pathway questions compare a row with its neighbours: which appointment came first, what the latest status is, how long since the last discharge, whether this stay continues the one before it. A window function answers that kind of question in one pass, without a self-join. It also lets you get the answer quietly wrong. A tie, a default frame or a filter in the wrong place can each change a count without raising an error.

This article works through five pathway patterns: ranking, first attendance, latest status, running totals with frames, and continuous spells leading to 30-day readmissions. Every query ran in SQLite 3.50 against small synthetic tables, and every output table below is what it returned. The tables are written out in sql_window_functions.py, so you can see every edge case I planted.

Ranking: ROW_NUMBER, RANK and DENSE_RANK

All three number the rows in each partition in ORDER BY order. They differ only on ties. Pathway R2 has two attended appointments on the same day, 12 February:

SELECT pathway_id, appt_id, appt_date, outcome,
       ROW_NUMBER() OVER (PARTITION BY pathway_id ORDER BY appt_date) AS row_num,
       RANK()       OVER (PARTITION BY pathway_id ORDER BY appt_date) AS rank_,
       DENSE_RANK() OVER (PARTITION BY pathway_id ORDER BY appt_date) AS dense_rank_
FROM appointments
WHERE pathway_id = 'R2'
ORDER BY appt_date, appt_id;

ROW_NUMBER always gives distinct numbers, so it has to break the tie somehow. Here it gave appointment 5 the 2, but nothing in the query says it must: the ORDER BY puts the two rows level, so which one comes first is up to the engine. Another engine, or the same one with a different plan, can swap them. RANK gives both rows 2 and skips 3. DENSE_RANK gives both 2 and doesn’t skip.

The rule I follow: whenever I use ROW_NUMBER to pick one row, the ORDER BY ends in a column that is unique within the partition. If it doesn’t, the pick isn’t reproducible.

First attendance per pathway

“Days from referral to first attendance” needs the first appointment the patient actually attended. Filter to attendances first, then number them:

WITH attended AS (
    SELECT pathway_id, appt_id, appt_date,
           ROW_NUMBER() OVER (
               PARTITION BY pathway_id
               ORDER BY appt_date, appt_id
           ) AS rn
    FROM appointments
    WHERE outcome = 'Attended'
)
SELECT p.pathway_id, p.referral_date,
       a.appt_id AS first_appt_id,
       a.appt_date AS first_attended,
       julianday(a.appt_date) - julianday(p.referral_date) AS days_to_first
FROM pathways AS p
LEFT JOIN attended AS a
       ON a.pathway_id = p.pathway_id AND a.rn = 1
ORDER BY p.pathway_id;

The LEFT JOIN from the pathways table keeps R3, which has had two DNAs and no attendance yet. Dropping it would flatter the average wait.

The common mistake is to number every appointment and filter afterwards, with WHERE rn = 1 AND outcome = 'Attended'. That returns one row, R4. R1’s first appointment was a DNA and R2’s was cancelled by the hospital, so their row 1 isn’t an attendance and both pathways vanish. The window function runs after WHERE, so a filter inside the CTE shapes what gets numbered, and a filter outside it only throws rows away.

Latest status per referral

Status histories arrive with duplicates and ties. In this one, R1’s “Appointment booked” event was loaded twice, and R2 has two events stamped 16:45 on the same day. Keep one row per referral with ROW_NUMBER and a full tie-break:

WITH ranked AS (
    SELECT referral_id, event_id, status, status_time, loaded_at,
           ROW_NUMBER() OVER (
               PARTITION BY referral_id
               ORDER BY status_time DESC, event_id DESC, loaded_at DESC
           ) AS rn
    FROM referral_status
)
SELECT referral_id, event_id, status, status_time
FROM ranked
WHERE rn = 1
ORDER BY referral_id;

The tie-break is a business rule, not decoration. I’ve assumed the source’s event ID increases with each event, so on a tie the higher ID is the later status. Check that with whoever owns the source system before relying on it. loaded_at comes last, so the two identical copies of event 1002 still resolve to one row.

With RANK() ... ORDER BY status_time DESC and WHERE rnk = 1, the same data returns five rows: both copies of R1’s event, and both of R2’s tied statuses. Every downstream count of referrals by status is now inflated, and R2 is both booked and discharged. RANK is the right choice only when you want the ties.

Running totals and the default frame

An aggregate with OVER (ORDER BY ...) gives a running total. What it sums depends on the frame, and if you don’t write one you get the default. SQLite, PostgreSQL and SQL Server all document the same default when ORDER BY is present: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. In RANGE mode, “current row” includes every row that ties with it on the ORDER BY value.

SELECT appt_id, appt_date,
       COUNT(*) OVER (ORDER BY appt_date) AS default_frame,
       COUNT(*) OVER (
           ORDER BY appt_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS rows_frame
FROM appointments
WHERE pathway_id = 'R2'
ORDER BY appt_date, appt_id;

With the default frame, both appointments on 12 February see each other, so both show 3. Neither answer is wrong, but they answer different questions, and the one you get by accident is the one fewer people expect. Write the frame out every time.

Seven rows are not seven days

The frame matters most for rolling totals. The clinic_days table has one row per clinic day and no rows for weekends or bank holidays, which is how most activity tables look. Here are two “last seven days” totals:

SELECT clinic_date, attendances,
       SUM(attendances) OVER (
           ORDER BY clinic_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS last_7_rows,
       SUM(attendances) OVER (
           ORDER BY julianday(clinic_date)
           RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS last_7_days
FROM clinic_days
ORDER BY clinic_date;

For the week after Easter:

ROWS BETWEEN 6 PRECEDING means the last seven rows. On a table with no weekend rows, that’s seven clinic days, which span nine calendar days in a normal week and more around bank holidays. RANGE BETWEEN 6 PRECEDING on a day number means the last seven calendar days, whatever rows exist. Across the 12 weeks, once both windows were full, the ROWS total averaged 1.48 times the true seven-day total, and 2.30 times at its worst, after Easter.

Data table

Clinic date	ROWS: last 7 clinic days	RANGE: last 7 calendar days
2026-03-10	283	194
2026-03-11	276	190
2026-03-12	274	187
2026-03-13	254	180
2026-03-16	266	192
2026-03-17	277	203
2026-03-18	274	199
2026-03-19	283	203
2026-03-20	274	210
2026-03-23	281	202
2026-03-24	295	198
2026-03-25	280	198
2026-03-26	272	192
2026-03-27	276	201
2026-03-30	279	201
2026-03-31	284	199
2026-04-01	292	214
2026-04-02	307	233
2026-04-07	327	166
2026-04-08	329	151
2026-04-09	338	147
2026-04-10	327	181
2026-04-13	347	239
2026-04-14	347	234
2026-04-15	335	239
2026-04-16	330	241
2026-04-17	327	242
2026-04-20	331	239
2026-04-21	329	218
2026-04-22	322	226
2026-04-23	313	217
2026-04-24	301	213
2026-04-27	292	202
2026-04-28	296	209
2026-04-29	280	197
2026-04-30	285	190
2026-05-01	257	182
2026-05-05	261	147
2026-05-06	282	160
2026-05-07	280	165
2026-05-08	276	177
2026-05-11	283	223
2026-05-12	285	214
2026-05-13	298	198
2026-05-14	296	202
2026-05-15	289	212
2026-05-18	296	215
2026-05-19	301	216
2026-05-20	303	228
2026-05-21	302	220
2026-05-22	294	203

Synthetic data. Source: projects/article-examples/round-two/sql_window_functions.py.

Nothing about the ROWS line looks wrong on its own; it’s only wrong against its label. If a dashboard calls it “last 7 days”, it overstates weekly activity by about half.

RANGE with a numeric offset is not portable. SQLite has supported it since 3.28.0, and PostgreSQL accepts it with an interval offset on a date column (RANGE BETWEEN '6 days' PRECEDING AND CURRENT ROW). SQL Server does not: its documentation says RANGE can’t be used with a numeric PRECEDING or FOLLOWING value. The portable fix is a calendar spine, one row per day with zeros filled in, after which ROWS counts days:

WITH RECURSIVE calendar(d) AS (
    SELECT '2026-03-02'
    UNION ALL
    SELECT date(d, '+1 day') FROM calendar WHERE d < '2026-05-22'
),
filled AS (
    SELECT c.d AS clinic_date, COALESCE(a.attendances, 0) AS attendances
    FROM calendar AS c
    LEFT JOIN clinic_days AS a ON a.clinic_date = c.d
)
SELECT clinic_date,
       SUM(attendances) OVER (
           ORDER BY clinic_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS last_7_days
FROM filled;

The script checks that this matches the RANGE version on every clinic day, and it does. In a warehouse you’d join a date dimension rather than generate the calendar each time.

Continuous spells: gaps and islands

A patient transferred between hospitals, or with overlapping records, has several stays that are really one continuous period in hospital. Finding those periods is a gaps-and-islands problem: flag each stay that starts a new island, then turn the flags into island numbers with a running sum.

The flag needs care. A stay starts a new spell if it begins after the latest discharge of any earlier stay, not just the previous one. That is a running MAX, with a frame that stops one row before the current row:

WITH ordered AS (
    SELECT patient_id, stay_id, admit_date, discharge_date,
           MAX(discharge_date) OVER (
               PARTITION BY patient_id
               ORDER BY admit_date, stay_id
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           ) AS latest_discharge_before
    FROM stays
),
flagged AS (
    SELECT *,
           CASE WHEN latest_discharge_before IS NULL
                  OR admit_date > latest_discharge_before
                THEN 1 ELSE 0 END AS new_spell
    FROM ordered
)
SELECT patient_id, stay_id, admit_date, discharge_date,
       latest_discharge_before, new_spell,
       SUM(new_spell) OVER (
           PARTITION BY patient_id
           ORDER BY admit_date, stay_id
           ROWS UNBOUNDED PRECEDING
       ) AS spell_no
FROM flagged
WHERE patient_id IN ('P01', 'P03')
ORDER BY patient_id, admit_date, stay_id;

P01’s second stay starts on the day the first ended, a transfer, so it joins spell 1. P03’s stay 302 sits inside stay 301, and 303 overlaps its end, so all three form one spell from 5 to 30 January.

The version most people write first uses LAG(discharge_date). For P03 it compares stay 303’s admission (22 January) with stay 302’s discharge (12 January), sees a gap, and starts a new spell in the middle of a stay that ran until the 25th. LAG sees only the previous row; nested and overlapping stays need everything before it. Dates are ISO text in SQLite, so MAX and > compare them correctly. Use real date types elsewhere.

Readmissions within 30 days

With spells in place, LAG is the right tool: each spell’s gap is measured from the end of the previous spell. The query below builds the spells as above, takes each spell’s admission method from its first stay with FIRST_VALUE, and then measures the gaps:

WITH ordered AS (
    SELECT *,
           MAX(discharge_date) OVER (
               PARTITION BY patient_id ORDER BY admit_date, stay_id
               ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
           ) AS latest_discharge_before
    FROM stays
),
numbered AS (
    SELECT *,
           SUM(CASE WHEN latest_discharge_before IS NULL
                      OR admit_date > latest_discharge_before
                    THEN 1 ELSE 0 END) OVER (
               PARTITION BY patient_id ORDER BY admit_date, stay_id
               ROWS UNBOUNDED PRECEDING
           ) AS spell_no
    FROM ordered
),
spells AS (
    SELECT patient_id, spell_no, method,
           MIN(admit_date) AS spell_start,
           MAX(discharge_date) AS spell_end
    FROM (
        SELECT *,
               FIRST_VALUE(admission_method) OVER (
                   PARTITION BY patient_id, spell_no ORDER BY admit_date, stay_id
               ) AS method
        FROM numbered
    )
    GROUP BY patient_id, spell_no, method
),
gaps AS (
    SELECT *,
           julianday(spell_start)
             - julianday(LAG(spell_end) OVER (
                   PARTITION BY patient_id ORDER BY spell_start)) AS days_since_discharge
    FROM spells
)
SELECT patient_id, spell_no, spell_start, spell_end, method, days_since_discharge,
       CASE WHEN method = 'Emergency' AND days_since_discharge <= 30
            THEN 1 ELSE 0 END AS readmit_30d
FROM gaps
ORDER BY patient_id, spell_no;

Five emergency readmissions from 13 spells. P05’s planned return inside 30 days doesn’t count, and P04’s next-day emergency admission does.

Run the same LAG directly on the 16 stays and it finds eight. The three extra are P01’s same-day transfer (0 days), P03’s overlapping stay (10 days) and P03’s nested stay at −15 days, which passes <= 30 because nobody expects a negative gap. Three of the eight “readmissions” happened inside a continuous spell in hospital. A negative interval in your output is always worth investigating.

Two more choices belong in the written definition. This query counts readmissions on the returning spell. A rate per index discharge flips it around: use LEAD(spell_start) to look forward from each discharge, and exclude index spells that ended in death. Published readmission indicators add their own exclusions, so match the one you’re reporting against rather than inventing yours. Metric definitions as code covers how to write those choices down once.

Dialect notes I checked

• Default frame. With ORDER BY and no frame clause, SQLite, PostgreSQL and SQL Server all use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Ranking functions don’t take a frame, so this doesn’t affect them.
• RANGE with an offset. SQLite 3.28.0 and later, and PostgreSQL (interval offsets for dates), support it. SQL Server supports RANGE only with UNBOUNDED and CURRENT ROW, so use a calendar table and ROWS.
• Date differences. SQLite has no date type, so I used julianday(). In SQL Server use DATEDIFF(day, earlier, later); in PostgreSQL, subtracting one date from another gives whole days.
• Filtering on a window function. In SQLite, PostgreSQL and SQL Server you can’t put a window function in WHERE, because it’s evaluated after WHERE. Wrap it in a CTE or subquery, as every example here does.

For where these patterns sit in a pipeline, the outpatient analytics pipeline uses the same ROW_NUMBER pattern to keep the latest copy of each appointment, and data tests that catch real problems covers the uniqueness test that proves it worked. The difference between stays, episodes and spells is covered in a star schema for hospital activity.

Reproduce

The tables, queries and chart come from one script. It builds an in-memory SQLite database, runs every query in this article and writes the output tables to projects/article-examples/round-two/output/sql-window-functions.md:

uv run python projects/article-examples/round-two/sql_window_functions.py