SQL for Day 7 Retention | Dataford Interview Questions

Your question is SQL for Day 7 Retention. 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

The Product Analytics team for Scopely's Monopoly GO! wants to measure whether players from a specific signup cohort return to the game seven days later. Write a PostgreSQL query for users who signed up on 2025-01-10.

Requirements

Restrict the cohort to Monopoly GO! users whose signup date is 2025-01-10.
Count each cohort user once, even if they generate multiple events on Day 7.
Treat a user as retained when they have at least one game event on 2025-01-17.
Return the cohort date, cohort size, retained users, and retention rate as a percentage rounded to two decimal places.

Schema

users

Column	Type	Description
user_idPK	INTEGER	Unique player identifier
game_name	VARCHAR(30)	Scopely game name
signup_at	TIMESTAMP	Player signup timestamp
platform	VARCHAR(20)	Player platform

game_events

Column	Type	Description
event_idPK	INTEGER	Unique game event identifier
user_id	INTEGER	Player who generated the event
activity_at	TIMESTAMP	Event timestamp
event_type	VARCHAR(30)	Type of game activity