SQL Rolling Retention by Cohort | Dataford Interview Questions

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

Problem

NVIDIA Omniverse needs a reliable cohort-retention report for user activity. Events can arrive after the activity occurred, so retention must be calculated from event_at, not the ingestion timestamp.

Write a PostgreSQL query that reports 7-day rolling retention by cohort. A user belongs to the cohort defined by the date of their earliest valid event. A user is retained when they have at least one later event from cohort day 1 through cohort day 7, inclusive.

Requirements

Derive each user's cohort from the minimum non-null event_at across all events.
Count each cohort's distinct users and distinct users retained within the seven-day window.
Return cohort_date, cohort_size, retained_users, and retention_rate_pct, ordered by cohort date.
Ignore received_at when determining cohort membership or retention, allowing late-arriving events to be counted correctly.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
user_name	VARCHAR(100)	NVIDIA customer or developer name

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User associated with the event
event_at	TIMESTAMPTZ	Timestamp when the activity occurred
received_at	TIMESTAMPTZ	Timestamp when the event reached the warehouse
event_type	VARCHAR(50)	Type of user activity