7-Day Rider Retention in NYC | Dataford Interview Questions

RideNow wants to measure early rider retention for new NYC riders. Write a SQL query to calculate the 7-day rolling retention rate by signup date cohort.

A rider is considered retained if they complete at least one trip in NYC between day 1 and day 7 after signup, inclusive. The signup day itself does not count.

Requirements

Consider only riders whose signup_city is 'NYC'.
Build daily signup cohorts using signup_date from the riders table.
For each cohort date, calculate:
- total new riders
- retained riders
- retention rate = retained riders / total new riders
Return one row per signup_date, ordered by signup_date ascending.
Ignore trips outside NYC when determining retention.
Count each rider at most once in the retained population, even if they took multiple qualifying trips.

Schema

riders

Column	Type	Description
rider_idPK	INT	Unique rider identifier
signup_date	DATE	Date the rider signed up
signup_city	VARCHAR(50)	City where the rider signed up
referral_code	VARCHAR(20)	Optional referral code used at signup

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
rider_id	INT	Rider who completed the trip
trip_date	DATE	Trip completion date
city	VARCHAR(50)	City where the trip occurred
fare_amount	NUMERIC(10,2)	Fare paid for the trip

Your question is 7-Day Rider Retention in NYC. 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