Interview Guides

7-Day Rider Retention in NYC | Dataford Interview Questions - Dataford - Ace your Interview

7-Day Rider Retention in NYC

Hard

SQL & Data Manipulation

Asked at 1 company1Window FunctionsRunning TotalsRetention

Also asked at

Problem

Task

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.

Table Definitions

`riders`

column	type	description
rider_id	INT	Unique rider identifier
signup_date	DATE	Rider signup date
signup_city	VARCHAR(50)	City where the rider signed up
referral_code	VARCHAR(20)	Optional referral code

`trips`

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Rider who took the trip
trip_date	DATE	Trip completion date
city	VARCHAR(50)	City where the trip occurred
fare_amount	NUMERIC(10,2)	Trip fare

Sample Data

`riders`

rider_id	signup_date	signup_city	referral_code
104	2024-01-03	NYC	REF20
101	2024-01-01	NYC	REF10
109	2024-01-05	NYC
103	2024-01-02	NYC	NULL
108	2024-01-04	NYC	REF10
106	2024-01-03	Boston	REF30
102	2024-01-01	NYC	NULL
110	2024-01-05	NYC	REF50
105	2024-01-03	NYC	REF20
107	2024-01-04	NYC	NULL

`trips`

trip_id	rider_id	trip_date	city	fare_amount
205	103	2024-01-09	NYC	17.00
201	101	2024-01-02	NYC	15.50
210	106	2024-01-05	Boston	9.00
203	102	2024-01-08	NYC	22.00
208	105	2024-01-10	NYC	11.00
212	108	2024-01-12	NYC	13.00
206	104	2024-01-04	NYC	8.00
211	107	2024-01-05	NYC	16.00
202	101	2024-01-05	NYC	12.00
204	103	2024-01-03	Chicago	18.00
207	104	2024-01-11	NYC	14.00
209	105	2024-01-05	NYC	10.00

Expected Output

signup_date	new_riders	retained_riders	retention_rate
2024-01-01	2	2	1.0000
2024-01-02	1	0	0.0000
2024-01-03	2	2	1.0000
2024-01-04	2	1	0.5000
2024-01-05	2	0	0.0000

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

Sample Data

Example 1

Input

{"trips":[["205","103","2024-01-09","NYC","17.00"],["201","101","2024-01-02","NYC","15.50"],["210","106","2024-01-05","Boston","9.00"],["203","102","2024-01-08","NYC","22.00"],["208","105","2024-01-10","NYC","11.00"],["212","108","2024-01-12","NYC","13.00"],["206","104","2024-01-04","NYC","8.00"],["211","107","2024-01-05","NYC","16.00"],["202","101","2024-01-05","NYC","12.00"],["204","103","2024-01-03","Chicago","18.00"],["207","104","2024-01-11","NYC","14.00"],["209","105","2024-01-05","NYC","1

Output

[["2024-01-01","2","2","1.0000"],["2024-01-02","1","1","1.0000"],["2024-01-03","2","2","1.0000"],["2024-01-04","2","1","0.5000"],["2024-01-05","2","0","0.0000"]]

Problem

Task

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.

Table Definitions

`riders`

column	type	description
rider_id	INT	Unique rider identifier
signup_date	DATE	Rider signup date
signup_city	VARCHAR(50)	City where the rider signed up
referral_code	VARCHAR(20)	Optional referral code

`trips`

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Rider who took the trip
trip_date	DATE	Trip completion date
city	VARCHAR(50)	City where the trip occurred
fare_amount	NUMERIC(10,2)	Trip fare

Sample Data

`riders`

rider_id	signup_date	signup_city	referral_code
104	2024-01-03	NYC	REF20
101	2024-01-01	NYC	REF10
109	2024-01-05	NYC
103	2024-01-02	NYC	NULL
108	2024-01-04	NYC	REF10
106	2024-01-03	Boston	REF30
102	2024-01-01	NYC	NULL
110	2024-01-05	NYC	REF50
105	2024-01-03	NYC	REF20
107	2024-01-04	NYC	NULL

`trips`

trip_id	rider_id	trip_date	city	fare_amount
205	103	2024-01-09	NYC	17.00
201	101	2024-01-02	NYC	15.50
210	106	2024-01-05	Boston	9.00
203	102	2024-01-08	NYC	22.00
208	105	2024-01-10	NYC	11.00
212	108	2024-01-12	NYC	13.00
206	104	2024-01-04	NYC	8.00
211	107	2024-01-05	NYC	16.00
202	101	2024-01-05	NYC	12.00
204	103	2024-01-03	Chicago	18.00
207	104	2024-01-11	NYC	14.00
209	105	2024-01-05	NYC	10.00

Expected Output

signup_date	new_riders	retained_riders	retention_rate
2024-01-01	2	2	1.0000
2024-01-02	1	0	0.0000
2024-01-03	2	2	1.0000
2024-01-04	2	1	0.5000
2024-01-05	2	0	0.0000

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

Sample Data

Example 1

Input

{"trips":[["205","103","2024-01-09","NYC","17.00"],["201","101","2024-01-02","NYC","15.50"],["210","106","2024-01-05","Boston","9.00"],["203","102","2024-01-08","NYC","22.00"],["208","105","2024-01-10","NYC","11.00"],["212","108","2024-01-12","NYC","13.00"],["206","104","2024-01-04","NYC","8.00"],["211","107","2024-01-05","NYC","16.00"],["202","101","2024-01-05","NYC","12.00"],["204","103","2024-01-03","Chicago","18.00"],["207","104","2024-01-11","NYC","14.00"],["209","105","2024-01-05","NYC","1

Output

[["2024-01-01","2","2","1.0000"],["2024-01-02","1","1","1.0000"],["2024-01-03","2","2","1.0000"],["2024-01-04","2","1","0.5000"],["2024-01-05","2","0","0.0000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

January Rider Retention RateMedium

Reels Cohort 7-Day Rolling RetentionEasy

Ride Frequency by Signup CohortMedium

Next question

7-Day Rider Retention in NYC

Hard

SQL & Data Manipulation

Asked at 1 company1Window FunctionsRunning TotalsRetention

Also asked at

Problem

Task

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.

Table Definitions

`riders`

column	type	description
rider_id	INT	Unique rider identifier
signup_date	DATE	Rider signup date
signup_city	VARCHAR(50)	City where the rider signed up
referral_code	VARCHAR(20)	Optional referral code

`trips`

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Rider who took the trip
trip_date	DATE	Trip completion date
city	VARCHAR(50)	City where the trip occurred
fare_amount	NUMERIC(10,2)	Trip fare

Sample Data

`riders`

rider_id	signup_date	signup_city	referral_code
104	2024-01-03	NYC	REF20
101	2024-01-01	NYC	REF10
109	2024-01-05	NYC
103	2024-01-02	NYC	NULL
108	2024-01-04	NYC	REF10
106	2024-01-03	Boston	REF30
102	2024-01-01	NYC	NULL
110	2024-01-05	NYC	REF50
105	2024-01-03	NYC	REF20
107	2024-01-04	NYC	NULL

`trips`

trip_id	rider_id	trip_date	city	fare_amount
205	103	2024-01-09	NYC	17.00
201	101	2024-01-02	NYC	15.50
210	106	2024-01-05	Boston	9.00
203	102	2024-01-08	NYC	22.00
208	105	2024-01-10	NYC	11.00
212	108	2024-01-12	NYC	13.00
206	104	2024-01-04	NYC	8.00
211	107	2024-01-05	NYC	16.00
202	101	2024-01-05	NYC	12.00
204	103	2024-01-03	Chicago	18.00
207	104	2024-01-11	NYC	14.00
209	105	2024-01-05	NYC	10.00

Expected Output

signup_date	new_riders	retained_riders	retention_rate
2024-01-01	2	2	1.0000
2024-01-02	1	0	0.0000
2024-01-03	2	2	1.0000
2024-01-04	2	1	0.5000
2024-01-05	2	0	0.0000

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

Sample Data

Example 1

Input

{"trips":[["205","103","2024-01-09","NYC","17.00"],["201","101","2024-01-02","NYC","15.50"],["210","106","2024-01-05","Boston","9.00"],["203","102","2024-01-08","NYC","22.00"],["208","105","2024-01-10","NYC","11.00"],["212","108","2024-01-12","NYC","13.00"],["206","104","2024-01-04","NYC","8.00"],["211","107","2024-01-05","NYC","16.00"],["202","101","2024-01-05","NYC","12.00"],["204","103","2024-01-03","Chicago","18.00"],["207","104","2024-01-11","NYC","14.00"],["209","105","2024-01-05","NYC","1

Output

[["2024-01-01","2","2","1.0000"],["2024-01-02","1","1","1.0000"],["2024-01-03","2","2","1.0000"],["2024-01-04","2","1","0.5000"],["2024-01-05","2","0","0.0000"]]

Problem

Task

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.

Table Definitions

`riders`

column	type	description
rider_id	INT	Unique rider identifier
signup_date	DATE	Rider signup date
signup_city	VARCHAR(50)	City where the rider signed up
referral_code	VARCHAR(20)	Optional referral code

`trips`

column	type	description
trip_id	INT	Unique trip identifier
rider_id	INT	Rider who took the trip
trip_date	DATE	Trip completion date
city	VARCHAR(50)	City where the trip occurred
fare_amount	NUMERIC(10,2)	Trip fare

Sample Data

`riders`

rider_id	signup_date	signup_city	referral_code
104	2024-01-03	NYC	REF20
101	2024-01-01	NYC	REF10
109	2024-01-05	NYC
103	2024-01-02	NYC	NULL
108	2024-01-04	NYC	REF10
106	2024-01-03	Boston	REF30
102	2024-01-01	NYC	NULL
110	2024-01-05	NYC	REF50
105	2024-01-03	NYC	REF20
107	2024-01-04	NYC	NULL

`trips`

trip_id	rider_id	trip_date	city	fare_amount
205	103	2024-01-09	NYC	17.00
201	101	2024-01-02	NYC	15.50
210	106	2024-01-05	Boston	9.00
203	102	2024-01-08	NYC	22.00
208	105	2024-01-10	NYC	11.00
212	108	2024-01-12	NYC	13.00
206	104	2024-01-04	NYC	8.00
211	107	2024-01-05	NYC	16.00
202	101	2024-01-05	NYC	12.00
204	103	2024-01-03	Chicago	18.00
207	104	2024-01-11	NYC	14.00
209	105	2024-01-05	NYC	10.00

Expected Output

signup_date	new_riders	retained_riders	retention_rate
2024-01-01	2	2	1.0000
2024-01-02	1	0	0.0000
2024-01-03	2	2	1.0000
2024-01-04	2	1	0.5000
2024-01-05	2	0	0.0000

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

Sample Data

Example 1

Input

{"trips":[["205","103","2024-01-09","NYC","17.00"],["201","101","2024-01-02","NYC","15.50"],["210","106","2024-01-05","Boston","9.00"],["203","102","2024-01-08","NYC","22.00"],["208","105","2024-01-10","NYC","11.00"],["212","108","2024-01-12","NYC","13.00"],["206","104","2024-01-04","NYC","8.00"],["211","107","2024-01-05","NYC","16.00"],["202","101","2024-01-05","NYC","12.00"],["204","103","2024-01-03","Chicago","18.00"],["207","104","2024-01-11","NYC","14.00"],["209","105","2024-01-05","NYC","1

Output

[["2024-01-01","2","2","1.0000"],["2024-01-02","1","1","1.0000"],["2024-01-03","2","2","1.0000"],["2024-01-04","2","1","0.5000"],["2024-01-05","2","0","0.0000"]]

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

January Rider Retention RateMedium

Reels Cohort 7-Day Rolling RetentionEasy

Ride Frequency by Signup CohortMedium

Next question