Your question is SQL Rolling Churn With Seasonality. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
customer_id, customer_name, window_end, rolling_active_months, rolling_spend, seasonal_expected_active_months, seasonally_adjusted_activity_ratio, and churn_pattern.churned, at_risk, or retained; sort by customer_id ascending.| 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 |
| 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 |
| 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 |