Rolling Retention by Tier and Region | Dataford Interview Questions

OpenAI wants to evaluate subscription retention across ChatGPT plans and geographic regions. Write a PostgreSQL query that builds monthly retention cohorts from each user's first subscription and tracks product activity after signup.

Requirements

Define each user's cohort month from their first subscription start date.
Segment cohorts by subscription tier and geographic region, labeling missing regions as Unknown.
Generate months 0 through 3 for every cohort user, including months with no activity.
Count cohort users active in each month, calculate monthly retention, and calculate a three-month rolling average of retention using a window function.
Return one row per region, tier, cohort month, and month offset, ordered chronologically within each cohort.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique OpenAI user identifier
region	VARCHAR(20)	User geographic region

subscriptions

Column	Type	Description
subscription_idPK	INTEGER	Unique subscription identifier
user_id	INTEGER	Subscribed user
tier	VARCHAR(20)	ChatGPT subscription tier
started_at	DATE	Subscription start date

product_activity

Column	Type	Description
activity_idPK	INTEGER	Unique product activity event
user_id	INTEGER	User generating the activity
activity_date	DATE	Date of product activity
platform	VARCHAR(20)	Platform used for the activity

Your question is Rolling Retention by Tier and Region. Start with the requirements and the three 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