7-Day Rolling Retention SQL | Dataford Interview Questions

Khan Academy wants to monitor whether learners return after using the platform. Using login events, calculate the seven-day rolling retention rate for each eligible login date.

A learner is retained for cohort date D if the learner has at least one additional login from D + 1 through D + 7, inclusive. Only include cohort dates with a complete seven-day observation window, ending at the latest login date in the table.

Requirements

Deduplicate multiple login events from the same learner on the same calendar date.
Exclude anonymous events where user_id is NULL.
Return the cohort date, cohort size, retained learner count, and retention rate as a decimal rounded to four places.
Preserve cohort dates with zero retained learners and order results chronologically.

Schema

login_events

Column	Type	Description
event_idPK	INT	Unique login event identifier
user_id	INT	Khan Academy learner identifier; NULL represents an anonymous event
logged_in_at	TIMESTAMPTZ	Timestamp when the learner logged in
surface	VARCHAR(30)	Khan Academy surface used for the login

Your question is 7-Day Rolling Retention SQL. Start with the requirements and the one table 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

Khan Academy wants to monitor whether learners return after using the platform. Using login events, calculate the seven-day rolling retention rate for each eligible login date.

Requirements

Deduplicate multiple login events from the same learner on the same calendar date.
Exclude anonymous events where user_id is NULL.
Return the cohort date, cohort size, retained learner count, and retention rate as a decimal rounded to four places.
Preserve cohort dates with zero retained learners and order results chronologically.

Schema

login_events

Column	Type	Description
event_idPK	INT	Unique login event identifier
user_id	INT	Khan Academy learner identifier; NULL represents an anonymous event
logged_in_at	TIMESTAMPTZ	Timestamp when the learner logged in
surface	VARCHAR(30)	Khan Academy surface used for the login

Your question is 7-Day Rolling Retention SQL. Start with the requirements and the one table 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.