Cohort Retention with SQL | Dataford Interview Questions

Business Context

SparkBeyond wants to compare how users acquired in different months continue using the SparkBeyond platform. A qualifying activity is an insight_generated event on any SparkBeyond surface.

Task

Write a PostgreSQL query that calculates monthly retention by signup cohort and compares each cohort's retention rate with the previous cohort for the same lifecycle month.

Requirements

Derive each user's monthly cohort from users.signup_at.
Count each user at most once per cohort and lifecycle month, even if they generate multiple events.
Return the cohort month, lifecycle month number, cohort size, retained users, and retention percentage.
Use a LAG window function to show the prior cohort's retention percentage and the percentage-point difference.
Include qualifying activity only from insight_generated events, exclude activity before signup, and sort chronologically.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_at	TIMESTAMP	Timestamp when the user joined SparkBeyond

product_events

Column	Type	Description
event_idPK	INT	Unique product event identifier
user_id	INT	User who generated the event
event_at	TIMESTAMP	Timestamp when the event occurred
event_name	VARCHAR(40)	Name of the SparkBeyond product event
surface	VARCHAR(40)	SparkBeyond surface where the event occurred

Your question is Cohort Retention with SQL. 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