Window Functions for Churn | Dataford Interview Questions

Business Context

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.

Task

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.

Requirements

Use ROW_NUMBER() to identify each user's most recent activity.
Return the user name, marketing channel, latest activity date, latest Ancestry surface, total activity count, and inactivity days.
Exclude users with fewer than two activities and users whose latest activity was within the previous 30 days.
Order results by inactivity days descending, then user_id ascending.

Schema

users

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

activity_events

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

Your question is Window Functions for Churn. 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