SQL Week-Over-Week Growth | Dataford Interview Questions

Athelas Operations needs a weekly view of active patient monitoring sessions to identify changes in monitoring volume. Write a PostgreSQL query using the Athelas monitoring session data to calculate week-over-week growth.

Requirements

Count sessions where both the session and the patient are active.
Group counts by calendar week using Monday as the week start.
Compare each week with the immediately preceding week and calculate percentage growth as (current_week - prior_week) / prior_week * 100.
Return weeks in chronological order. Return NULL growth when no prior week exists or the prior count is zero.

Schema

patients

Column	Type	Description
patient_idPK	INTEGER	Unique patient identifier
full_name	VARCHAR(120)	Patient full name
status	VARCHAR(20)	Current patient status

monitoring_sessions

Column	Type	Description
session_idPK	INTEGER	Unique monitoring session identifier
patient_id	INTEGER	Patient associated with the session
started_at	TIMESTAMP	Timestamp when monitoring began
session_status	VARCHAR(20)	Current monitoring session status

Your question is SQL Week-Over-Week Growth. 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