Rolling Churn With Window Functions | Dataford Interview Questions

Gallagher's customer analytics team wants to monitor whether churn is increasing across its tracked customer portfolio. Write a PostgreSQL query that calculates the monthly churn rate and its rolling average over the most recent six reported months.

Requirements

Join customer status records to Gallagher customers only.
Calculate each month's churn rate as churned customers divided by tracked customers.
Use a window function to calculate the six-month rolling average, including the current month.
Return results chronologically and round rates to four decimal places.

Assume each row in customer_monthly_status represents one tracked customer for one month. A customer is churned when lifecycle_status = 'churned'. The latest month in the data defines the end of the six-month reporting period.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(120)	Customer or organization name
company_name	VARCHAR(120)	Organization associated with the customer

customer_monthly_status

Column	Type	Description
status_idPK	INTEGER	Unique monthly status record identifier
customer_id	INTEGER	References customers.customer_id
month_start	DATE	First day of the reporting month
lifecycle_status	VARCHAR(20)	Monthly lifecycle status

Your question is Rolling Churn With 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