Rolling Retention Rates by Cohort | Dataford Interview Questions

The OpenAI ChatGPT analytics team wants to compare engagement across user signup cohorts. Write a PostgreSQL query that measures activity during each user's first ninety days after signup.

Requirements

Group users by their signup_date cohort.
Calculate daily retention as the number of distinct users active on each cohort date divided by the cohort size.
Calculate the ninety-day rolling average of daily retention for each cohort using a window function ordered by activity date.
Exclude activity before signup, activity after day 90, users without a signup date, and rows with a null activity date. Return results ordered by cohort and activity date.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique user identifier
signup_date	DATE	Date the user signed up

user_activity

Column	Type	Description
activity_idPK	INTEGER	Unique activity record
user_id	INTEGER	User associated with the activity
activity_date	DATE	Date of the activity

Your question is Rolling Retention Rates by Cohort. 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