Rolling Positivity With SQL Window Functions | Dataford Interview Questions

Write a SQL query using window functions to compute a rolling 7-day average of test positivity by patient cohort.

Use the patients and test_results tables. Exclude tests with missing results or dates. Calculate positivity per cohort and test date, then return the rolling average over the current date and preceding six calendar days.

Output

One row per cohort and test date with valid tests
Columns: cohort, test_date, test_count, positive_tests, positivity_rate, and rolling_7_day_avg
Include only non-null cohorts, order by cohort, then test_date

Schema

patients

Column	Type	Description
patient_idPK	INT	Unique patient identifier
cohort	VARCHAR(50)	Patient cohort label

test_results

Column	Type	Description
test_idPK	INT	Unique test identifier
patient_id	INT	Patient who received the test
test_date	DATE	Date the test was performed
test_result	VARCHAR(20)	Test outcome, such as positive or negative

Your question is Rolling Positivity With SQL Window Functions. 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