Live Retention SQL | Dataford Interview Questions

Live SQL: compute user retention using date operations (medium difficulty).

Asked in the SQL + Python live coding stage. The interviewer supplied the date function when asked instead of allowing Google. Calculate retention for users who signed up on or before 2024-01-08, using activity exactly seven days after signup.

Output

One row per signup date, with signup_date, cohort_users, retained_day_7_users, and day_7_retention_rate.
Include only signup dates represented in the eligible user data, ordered by signup_date ascending.
Return the retention rate as a decimal rounded to four places.

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date on which the user signed up

user_activity

Column	Type	Description
activity_idPK	INT	Unique activity record identifier
user_id	INT	User associated with the activity
activity_date	DATE	Date of user activity
activity_type	VARCHAR(30)	Type of recorded activity

Your question is Live Retention 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