SQL Rolling Churn With Seasonality

HardSQL · PostgreSQL

Problem

Write a SQL query to identify customer churn patterns over a rolling three-month window, accounting for seasonal variations.

Use the provided customer, monthly activity, and seasonal factor data. Evaluate the latest available month, treating missing activity as inactivity and preserving customers with no activity.

Output

One row per customer for the latest available month.
Columns: customer_id, customer_name, window_end, rolling_active_months, rolling_spend, seasonal_expected_active_months, seasonally_adjusted_activity_ratio, and churn_pattern.
Classify customers as churned, at_risk, or retained; sort by customer_id ascending.

Schema

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name
signup_date	DATE	Date the customer joined
region	VARCHAR(50)	Customer geographic region

monthly_customer_activity

Column	Type	Description
activity_idPK	INT	Unique monthly activity record
customer_id	INT	Referenced customer
activity_month	DATE	First day of the activity month
is_active	BOOLEAN	Whether the customer was active that month
monthly_spend	NUMERIC(12,2)	Customer spend during the month

seasonal_factors

Column	Type	Description
month_numberPK	INT	Calendar month number from 1 through 12
month_name	VARCHAR(20)	Calendar month name
expected_active_rate	NUMERIC(5,2)	Expected fraction of customers active in the month

Tablescustomersmonthly_customer_activityseasonal_factors