Query Player Churn | Dataford Interview Questions

Your question is Query Player Churn. 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

Ubisoft wants to monitor player engagement across titles such as Rainbow Six Siege. Using an analysis date of 2025-02-01, write a PostgreSQL query that classifies each player according to their most recent session.

Requirements

Use the latest session for each player and calculate inactivity days relative to 2025-02-01.
Classify players as active for fewer than 14 inactive days, at_risk for 14 to 29 days, churned for 30 or more days, and never_played when no session exists.
Return every player, including players without matching sessions, and order results by status priority, inactivity days descending, and player ID.

Schema

players

Column	Type	Description
player_idPK	INT	Unique player identifier
username	VARCHAR(100)	Ubisoft player name
region	VARCHAR(30)	Player geographic region

game_sessions

Column	Type	Description
session_idPK	INT	Unique session identifier
player_id	INT	Player associated with the session
game_title	VARCHAR(100)	Ubisoft game title
session_started_at	TIMESTAMP	Session start timestamp
platform	VARCHAR(30)	Gaming platform