Rolling Retention with Window Functions | Dataford Interview Questions

Your question is Rolling Retention with Window Functions. 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

Lyft wants to measure whether riders who take a completed ride remain active. Write a PostgreSQL query that calculates rolling 30-day retention for active Lyft riders.

For each activity date from January 1 through January 4, 2024, an active rider is retained if the same rider completes at least one later ride within the following 30 days, including day 30 and excluding the original activity date.

Requirements

Join rides to riders and include only riders whose account is active and rides whose status is completed.
Use a window function to deduplicate multiple rides by the same rider on the same date.
Return each activity date, active rider count, retained rider count, and retention rate as a percentage, rounded to two decimals.
Order the results chronologically. Do not count a rider more than once in either metric.

Schema

riders

Column	Type	Description
rider_idPK	INT	Unique Lyft rider identifier
rider_name	VARCHAR(100)	Rider display name
account_status	VARCHAR(20)	Current rider account status

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
rider_id	INT	Rider who took the ride
ride_date	DATE	Calendar date of the ride
ride_status	VARCHAR(20)	Ride outcome
completed_at	TIMESTAMP	Timestamp when the ride completed