SQL for Retention Metrics | Dataford Interview Questions

Write a SQL query to calculate the daily active ridership and the week-over-week retention rate of Waymo One riders.

Use completed rides only. Treat a rider as active once per calendar day, compare each date with the date seven days earlier, and return a NULL retention rate when no comparison date exists.

Output

One row per date with at least one completed ride.
Columns: ride_date, daily_active_riders, and wow_retention_rate.
Retention is the percentage of riders active on both dates, divided by the active riders seven days earlier.
Order by ride_date ascending.

Schema

riders

Column	Type	Description
rider_idPK	INT	Unique Waymo One rider identifier
rider_name	VARCHAR(100)	Rider display name
signup_date	DATE	Date the rider joined Waymo One

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
rider_id	INT	Rider associated with the ride
ride_date	DATE	Calendar date on which the ride occurred
status	VARCHAR(20)	Ride outcome, such as completed or cancelled

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