SQL Window Functions for Trends | Dataford Interview Questions

How would you use SQL window functions to analyze patient engagement over time at Omada Health?

Use the provided patient and engagement event data. Return qualifying engagement days on or after each patient's enrollment date.

Output

One row per patient and engagement date, with patient_id, patient_name, program, engagement_date, engagement_count, and engagement_day_number.
Exclude events for unknown patients and events before enrollment.
Order by program, patient_id, and engagement_date ascending.

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
patient_name	VARCHAR(100)	Patient display name
program	VARCHAR(50)	Omada health program
enrollment_date	DATE	Date the patient enrolled in the program

engagement_events

Column	Type	Description
event_idPK	INT	Unique engagement event identifier
patient_id	INT	Patient associated with the event
event_date	DATE	Date of the engagement event
event_type	VARCHAR(50)	Type of engagement activity

Your question is SQL Window Functions for Trends. 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.