SQL for Player Retention Analysis | Dataford Interview Questions

Write SQL to analyze player retention by day, cohort, and game mode.

Use each player's first valid session date as the cohort date and that session's mode as the cohort mode. Retention counts a player when they return in the same mode on a later day.

Output

One row per observed cohort date, cohort mode, and retention day.
Columns: cohort_date, cohort_mode, retention_day, cohort_players, retained_players, and retention_rate_pct.
Include retention day 0, calculate retention as a percentage rounded to two decimals, and order by cohort date, cohort mode, and retention day.

Schema

players

Column	Type	Description
player_idPK	INT	Unique player identifier
player_name	VARCHAR(100)	Player display name

game_modes

Column	Type	Description
mode_idPK	INT	Unique game mode identifier
mode_name	VARCHAR(50)	Game mode name

game_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
player_id	INT	Player who played the session
mode_id	INT	Game mode played in the session
played_at	TIMESTAMP	Session start timestamp

Your question is SQL for Player Retention Analysis. Start with the requirements and the three 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