Week-over-Week Retention Query | Dataford Interview Questions

Your question is Week-over-Week Retention Query. 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

Asana Spa has launched the Smart Goals feature and wants to measure whether users continue using it after their first use. Write a PostgreSQL query that calculates weekly retention by first-use cohort.

The launch week begins on 2025-01-06, and weeks start on Monday.

Requirements

Build each user's Smart Goals first-use week, excluding events before launch and events for other features.
For every cohort week and activity week through the latest qualifying event, return the number of users in the cohort, the number active in that activity week, and the retention rate as a percentage.
Include week 0, show weeks with no retained users as 0, avoid duplicate events, and order by cohort week followed by activity week.

Schema

users

Column	Type	Description
user_idPK	INT	Unique Asana Spa user identifier
user_name	VARCHAR(100)	User display name

feature_events

Column	Type	Description
event_idPK	INT	Unique feature event identifier
user_id	INT	User associated with the feature event
feature_name	VARCHAR(100)	Name of the feature used
event_at	TIMESTAMP	Timestamp when the feature event occurred