Rolling Retention Rates Across Sites | Dataford Interview Questions

Verily Baseline study operations need to compare patient retention across clinical sites. A patient is retained for a visit date if the same patient has another visit at the same site within the following 30 calendar days.

Write a PostgreSQL query that calculates rolling 30-day retention by site and active visit date.

Requirements

Count distinct active patients for each site and visit date.
Count distinct retained patients whose subsequent visit occurs after the active date and within 30 days.
Use a window function to calculate the retention rate across the current date and the previous 29 calendar days for each site.
Return only rows with a non-null visit date, ordered by site and date. Round the retention rate to four decimal places.

Schema

sites

Column	Type	Description
site_idPK	INTEGER	Clinical site identifier
site_name	VARCHAR(100)	Clinical site name
region	VARCHAR(50)	Geographic region of the site

patient_visits

Column	Type	Description
visit_idPK	INTEGER	Unique visit identifier
site_id	INTEGER	Site where the visit occurred
patient_id	INTEGER	Patient identifier
visit_date	DATE	Date of the patient visit

Your question is Rolling Retention Rates Across Sites. 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