Rolling 30-Day Event Counts | Dataford Interview Questions

Write a SQL query using window functions to calculate each patient's rolling 30-day event count and rank the most recent events.

Return one row for every recorded event. The rolling count includes the event itself and other events for the same patient from the preceding 30 days through the event timestamp. Rank events separately for each patient by event timestamp descending, allowing events at the same timestamp to share a rank.

Output

Return patient_id, patient_name, event_id, event_timestamp, event_type_name, rolling_30_day_event_count, and recency_rank.
Sort by patient_id, recency_rank, then event_id.

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
date_of_birth	DATE	Patient date of birth

patient_events

Column	Type	Description
event_idPK	INT	Unique event identifier
patient_id	INT	Patient associated with the event
event_timestamp	TIMESTAMP	Timestamp when the event occurred
event_type_id	INT	Optional event type identifier

event_types

Column	Type	Description
event_type_idPK	INT	Unique event type identifier
event_type_name	VARCHAR(100)	Human-readable event type

Your question is Rolling 30-Day Event Counts. Start with the requirements and the three 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