Week-over-Week Clinical Change Query | Dataford Interview Questions

Given a table of member clinical scores over multiple weeks, write a query to calculate the week-over-week change in their clinical improvement index.

Return every recorded member-week. If the immediately preceding week is missing, or either index is NULL, return a NULL change.

Output

One row per member and recorded week, with member_id, week_start, clinical_improvement_index, previous_week_index, and week_over_week_change.
Order by member_id, then week_start ascending.

Schema

member_clinical_scores

Column	Type	Description
score_idPK	INT	Unique clinical score record identifier
member_id	INT	Member identifier
week_start	DATE	Start date of the measurement week
clinical_improvement_index	NUMERIC(6,2)	Clinical improvement index recorded for the member and week

Your question is Week-over-Week Clinical Change Query. Start with the requirements and the one table 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