Window Functions Patient Ranking | Dataford Interview Questions

Write a SQL query using window functions to rank patients within each cohort by outcome date and compute a running total of events.

Use the patients and events tables. Include patients without events, place NULL outcome dates last, and use patient ID to make the final ordering deterministic.

Output

One row per patient with patient_id, cohort, outcome_date, event_count, cohort_rank, and running_event_total.
Rank patients within each cohort by outcome date, with tied dates sharing a rank.
Order by cohort, outcome date ascending with NULLs last, then patient ID.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
cohort	VARCHAR(20)	Patient cohort label
outcome_date	DATE	Date associated with the patient outcome

events

Column	Type	Description
event_idPK	INT	Unique event identifier
patient_id	INT	Patient identifier recorded for the event
event_type	VARCHAR(40)	Type of patient event
event_date	DATE	Date on which the event occurred

Your question is Window Functions Patient Ranking. Start with the requirements and the two tables in the Question tab.

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

Problem