7-Day Retention by Integration Signup | Dataford Interview Questions

Your question is 7-Day Retention by Integration Signup. Start with the requirements and the three 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’s growth team wants to measure whether users acquired through a specific third-party integration come back one week after signup. Write a SQL query to calculate the 7-day retention rate for users who signed up through the Slack integration.

Requirements

Identify users whose signup source is the Slack integration.
Define a retained user as someone who has at least one activity event on the exact date that is 7 days after their signup date.
Return the total number of Slack-signup users, the number retained on day 7, and the retention rate as a percentage rounded to 2 decimals.
Exclude users with a NULL signup timestamp or NULL integration source.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_ts	TIMESTAMP	Timestamp when the user signed up for Asana
workspace_id	INT	Workspace joined at signup

integration_signups

Column	Type	Description
signup_idPK	INT	Unique integration signup record
user_id	INT	User associated with the integration signup
integration_name	VARCHAR(50)	Integration credited for acquisition
connected_ts	TIMESTAMP	Timestamp when the integration was connected

user_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who performed the event
event_name	VARCHAR(50)	Name of the user activity event
event_ts	TIMESTAMP	Timestamp when the event occurred