Retention Rates for New Activations | Dataford Interview Questions

Write a SQL query to calculate week-over-week retention rates for new rider activations across different geographic markets.

Use the riders and rides tables. Treat a rider as retained when they complete at least one ride during a given week after activation. Calculate weeks 0 through 4 relative to each rider's activation week.

Output

One row per market, activation week, and retention week.
Columns: market, activation_week, retention_week, activated_riders, retained_riders, and retention_rate.
Include valid activations with a non-null market and activation date, including weeks with zero retained riders.
Order by market, activation_week, and retention_week ascending.

Schema

riders

Column	Type	Description
rider_idPK	INT	Unique rider identifier
market	VARCHAR(50)	Geographic market where the rider activated
activation_date	DATE	Date when the rider first activated

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
rider_id	INT	Rider who completed the ride
ride_date	DATE	Date when the ride was completed

Your question is Retention Rates for New Activations. 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