SQL for DAU and Retention | Dataford Interview Questions

Scopely's Monopoly GO! analytics team needs a daily engagement report. Write a PostgreSQL query that calculates DAU and install-cohort retention using UTC event dates.

Requirements

Count each player once per calendar day when they have at least one session event.
Return DAU for each player install date, including install dates with zero DAU.
Calculate D1 and D7 retention rates for each install-date cohort as percentages, rounded to two decimals.
Return one row per install date, ordered chronologically.

Schema

players

Column	Type	Description
player_idPK	INT	Unique player identifier
install_date	DATE	UTC installation date
platform	VARCHAR(20)	Player device platform

game_events

Column	Type	Description
event_idPK	INT	Unique event identifier
player_id	INT	Player who generated the event
event_ts	TIMESTAMP	UTC event timestamp
event_type	VARCHAR(30)	Event classification

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