Your question is Window Functions for Churn. Start with the requirements and the two 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.
Ancestry Marketing wants to identify users who showed repeated engagement with Ancestry surfaces but later became inactive. Use activity history as of April 1, 2025 to support churn analysis.
Write a PostgreSQL query using a window function to return users with at least two recorded activities whose most recent activity was at least 30 days before April 1, 2025.
ROW_NUMBER() to identify each user's most recent activity.user_id ascending.| Column | Type | Description |
|---|---|---|
| user_idPK | INTEGER | Unique user identifier |
| full_name | VARCHAR(100) | User's full name |
| marketing_channel | VARCHAR(50) | Acquisition channel attributed to the user |
| Column | Type | Description |
|---|---|---|
| activity_idPK | INTEGER | Unique activity identifier |
| user_id | INTEGER | References users.user_id |
| activity_date | DATE | Date on which the activity occurred |
| surface | VARCHAR(80) | Ancestry surface used during the activity |
| event_type | VARCHAR(50) | Recorded activity type |