Interview Guides

Ride Frequency by Signup Cohort | Dataford Interview Questions - Dataford - Ace your Interview

Ride Frequency by Signup Cohort

Medium

SQL & Data Manipulation

Asked at 2 companies2Window FunctionsGroup ByDate Functions

Also asked at

Problem

Task

At SwiftRide, the analytics team wants to measure how ride activity changes over time for different user signup cohorts. Write a SQL query to calculate monthly ride frequency by cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month and ride month, return:
- total active riders
- total completed rides
- average completed rides per active rider
Only count rides where status = 'completed'.
Include only users whose country is 'US' and whose account status is 'active' or 'paused'.
Order the final result by cohort_month, then ride_month.

Use the tables below.

Table Definitions

users

column	type	description
user_id	INT	Primary key
signup_date	DATE	Date the user created an account
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source

rides

column	type	description
ride_id	INT	Primary key
user_id	INT	Rider identifier
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

column	type	description
referral_source_id	INT	Primary key
source_name	VARCHAR(50)	Marketing or referral source

Sample Data

users

user_id	signup_date	country	account_status	referral_source_id
3	2024-02-05	US	paused	2
1	2024-01-10	US	active	1
8	2024-03-20	US	active	4
5	2024-02-18	CA	active	2
2	2024-01-22	US	active	NULL
7	2024-03-01	US	banned	3
4	2024-02-27	US	active	3
6	2024-03-03	US	active	NULL
9	2024-01-31	US	paused	99
10	2024-04-02	US	active	1

rides

ride_id	user_id	ride_date	status	fare_amount
101	1	2024-01-15	completed	18.50
102	1	2024-02-03	completed	12.00
103	1	2024-02-18	cancelled	0.00
104	2	2024-02-10	completed	9.75
105	2	2024-03-01	completed	14.20
106	3	2024-02-20	completed	7.40
107	3	2024-03-05	completed	11.10
108	4	2024-03-12	completed	16.00
109	4	2024-03-28	completed	8.50
110	5	2024-03-15	completed	13.00
111	6	2024-03-18	completed	10.00
112	6	2024-04-01	completed	15.00
113	7	2024-03-22	completed	20.00
114	8	2024-04-05	cancelled	0.00
115	9	2024-02-14	completed	6.25
116	9	2024-02-21	completed	5.75
117	10	2024-04-10	completed	19.00
118	2	2024-03-15	completed	8.80

Expected Output

cohort_month	ride_month	active_riders	completed_rides	avg_rides_per_active_rider
2024-01	2024-01	1	1	1.00
2024-01	2024-02	4	5	1.25
2024-01	2024-03	2	3	1.50
2024-02	2024-02	1	1	1.00
2024-02	2024-03	2	3	1.50
2024-03	2024-03	1	1	1.00
2024-03	2024-04	1	1	1.00
2024-04	2024-04	1	1	1.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source identifier

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
user_id	INT	User who took the ride
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride completion status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

Column	Type	Description
referral_source_idPK	INT	Referral source identifier
source_name	VARCHAR(50)	Referral or marketing source name

Sample Data

Example 1

Input

{"rides":[[101,1,"2024-01-15","completed","18.50"],[102,1,"2024-02-03","completed","12.00"],[103,1,"2024-02-18","cancelled","0.00"],[104,2,"2024-02-10","completed","9.75"],[105,2,"2024-03-01","completed","14.20"],[106,3,"2024-02-20","completed","7.40"],[107,3,"2024-03-05","completed","11.10"],[108,4,"2024-03-12","completed","16.00"],[109,4,"2024-03-28","completed","8.50"],[110,5,"2024-03-15","completed","13.00"],[111,6,"2024-03-18","completed","10.00"],[112,6,"2024-04-01","completed","15.00"],[1

Output

[["2024-01","2024-01","1","1","1.00"],["2024-01","2024-02","3","4","1.33"],["2024-01","2024-03","1","2","2.00"],["2024-02","2024-02","1","1","1.00"],["2024-02","2024-03","2","3","1.50"],["2024-03","2024-03","1","1","1.00"],["2024-03","2024-04","1","1","1.00"],["2024-04","2024-04","1","1","1.00"]]

Problem

Task

At SwiftRide, the analytics team wants to measure how ride activity changes over time for different user signup cohorts. Write a SQL query to calculate monthly ride frequency by cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month and ride month, return:
- total active riders
- total completed rides
- average completed rides per active rider
Only count rides where status = 'completed'.
Include only users whose country is 'US' and whose account status is 'active' or 'paused'.
Order the final result by cohort_month, then ride_month.

Use the tables below.

Table Definitions

users

column	type	description
user_id	INT	Primary key
signup_date	DATE	Date the user created an account
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source

rides

column	type	description
ride_id	INT	Primary key
user_id	INT	Rider identifier
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

column	type	description
referral_source_id	INT	Primary key
source_name	VARCHAR(50)	Marketing or referral source

Sample Data

users

user_id	signup_date	country	account_status	referral_source_id
3	2024-02-05	US	paused	2
1	2024-01-10	US	active	1
8	2024-03-20	US	active	4
5	2024-02-18	CA	active	2
2	2024-01-22	US	active	NULL
7	2024-03-01	US	banned	3
4	2024-02-27	US	active	3
6	2024-03-03	US	active	NULL
9	2024-01-31	US	paused	99
10	2024-04-02	US	active	1

rides

ride_id	user_id	ride_date	status	fare_amount
101	1	2024-01-15	completed	18.50
102	1	2024-02-03	completed	12.00
103	1	2024-02-18	cancelled	0.00
104	2	2024-02-10	completed	9.75
105	2	2024-03-01	completed	14.20
106	3	2024-02-20	completed	7.40
107	3	2024-03-05	completed	11.10
108	4	2024-03-12	completed	16.00
109	4	2024-03-28	completed	8.50
110	5	2024-03-15	completed	13.00
111	6	2024-03-18	completed	10.00
112	6	2024-04-01	completed	15.00
113	7	2024-03-22	completed	20.00
114	8	2024-04-05	cancelled	0.00
115	9	2024-02-14	completed	6.25
116	9	2024-02-21	completed	5.75
117	10	2024-04-10	completed	19.00
118	2	2024-03-15	completed	8.80

Expected Output

cohort_month	ride_month	active_riders	completed_rides	avg_rides_per_active_rider
2024-01	2024-01	1	1	1.00
2024-01	2024-02	4	5	1.25
2024-01	2024-03	2	3	1.50
2024-02	2024-02	1	1	1.00
2024-02	2024-03	2	3	1.50
2024-03	2024-03	1	1	1.00
2024-03	2024-04	1	1	1.00
2024-04	2024-04	1	1	1.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source identifier

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
user_id	INT	User who took the ride
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride completion status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

Column	Type	Description
referral_source_idPK	INT	Referral source identifier
source_name	VARCHAR(50)	Referral or marketing source name

Sample Data

Example 1

Input

{"rides":[[101,1,"2024-01-15","completed","18.50"],[102,1,"2024-02-03","completed","12.00"],[103,1,"2024-02-18","cancelled","0.00"],[104,2,"2024-02-10","completed","9.75"],[105,2,"2024-03-01","completed","14.20"],[106,3,"2024-02-20","completed","7.40"],[107,3,"2024-03-05","completed","11.10"],[108,4,"2024-03-12","completed","16.00"],[109,4,"2024-03-28","completed","8.50"],[110,5,"2024-03-15","completed","13.00"],[111,6,"2024-03-18","completed","10.00"],[112,6,"2024-04-01","completed","15.00"],[1

Output

[["2024-01","2024-01","1","1","1.00"],["2024-01","2024-02","3","4","1.33"],["2024-01","2024-03","1","2","2.00"],["2024-02","2024-02","1","1","1.00"],["2024-02","2024-03","2","3","1.50"],["2024-03","2024-03","1","1","1.00"],["2024-03","2024-04","1","1","1.00"],["2024-04","2024-04","1","1","1.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Signup Cohorts by RetentionMedium

Top Cities by Ride GrowthMedium

Compare User Retention by CohortMedium

Next question

Ride Frequency by Signup Cohort

Medium

SQL & Data Manipulation

Asked at 2 companies2Window FunctionsGroup ByDate Functions

Also asked at

Problem

Task

At SwiftRide, the analytics team wants to measure how ride activity changes over time for different user signup cohorts. Write a SQL query to calculate monthly ride frequency by cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month and ride month, return:
- total active riders
- total completed rides
- average completed rides per active rider
Only count rides where status = 'completed'.
Include only users whose country is 'US' and whose account status is 'active' or 'paused'.
Order the final result by cohort_month, then ride_month.

Use the tables below.

Table Definitions

users

column	type	description
user_id	INT	Primary key
signup_date	DATE	Date the user created an account
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source

rides

column	type	description
ride_id	INT	Primary key
user_id	INT	Rider identifier
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

column	type	description
referral_source_id	INT	Primary key
source_name	VARCHAR(50)	Marketing or referral source

Sample Data

users

user_id	signup_date	country	account_status	referral_source_id
3	2024-02-05	US	paused	2
1	2024-01-10	US	active	1
8	2024-03-20	US	active	4
5	2024-02-18	CA	active	2
2	2024-01-22	US	active	NULL
7	2024-03-01	US	banned	3
4	2024-02-27	US	active	3
6	2024-03-03	US	active	NULL
9	2024-01-31	US	paused	99
10	2024-04-02	US	active	1

rides

ride_id	user_id	ride_date	status	fare_amount
101	1	2024-01-15	completed	18.50
102	1	2024-02-03	completed	12.00
103	1	2024-02-18	cancelled	0.00
104	2	2024-02-10	completed	9.75
105	2	2024-03-01	completed	14.20
106	3	2024-02-20	completed	7.40
107	3	2024-03-05	completed	11.10
108	4	2024-03-12	completed	16.00
109	4	2024-03-28	completed	8.50
110	5	2024-03-15	completed	13.00
111	6	2024-03-18	completed	10.00
112	6	2024-04-01	completed	15.00
113	7	2024-03-22	completed	20.00
114	8	2024-04-05	cancelled	0.00
115	9	2024-02-14	completed	6.25
116	9	2024-02-21	completed	5.75
117	10	2024-04-10	completed	19.00
118	2	2024-03-15	completed	8.80

Expected Output

cohort_month	ride_month	active_riders	completed_rides	avg_rides_per_active_rider
2024-01	2024-01	1	1	1.00
2024-01	2024-02	4	5	1.25
2024-01	2024-03	2	3	1.50
2024-02	2024-02	1	1	1.00
2024-02	2024-03	2	3	1.50
2024-03	2024-03	1	1	1.00
2024-03	2024-04	1	1	1.00
2024-04	2024-04	1	1	1.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source identifier

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
user_id	INT	User who took the ride
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride completion status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

Column	Type	Description
referral_source_idPK	INT	Referral source identifier
source_name	VARCHAR(50)	Referral or marketing source name

Sample Data

Example 1

Input

{"rides":[[101,1,"2024-01-15","completed","18.50"],[102,1,"2024-02-03","completed","12.00"],[103,1,"2024-02-18","cancelled","0.00"],[104,2,"2024-02-10","completed","9.75"],[105,2,"2024-03-01","completed","14.20"],[106,3,"2024-02-20","completed","7.40"],[107,3,"2024-03-05","completed","11.10"],[108,4,"2024-03-12","completed","16.00"],[109,4,"2024-03-28","completed","8.50"],[110,5,"2024-03-15","completed","13.00"],[111,6,"2024-03-18","completed","10.00"],[112,6,"2024-04-01","completed","15.00"],[1

Output

[["2024-01","2024-01","1","1","1.00"],["2024-01","2024-02","3","4","1.33"],["2024-01","2024-03","1","2","2.00"],["2024-02","2024-02","1","1","1.00"],["2024-02","2024-03","2","3","1.50"],["2024-03","2024-03","1","1","1.00"],["2024-03","2024-04","1","1","1.00"],["2024-04","2024-04","1","1","1.00"]]

Problem

Task

At SwiftRide, the analytics team wants to measure how ride activity changes over time for different user signup cohorts. Write a SQL query to calculate monthly ride frequency by cohort.

Requirements

Define each user's cohort as the month of their signup_date.
For each cohort month and ride month, return:
- total active riders
- total completed rides
- average completed rides per active rider
Only count rides where status = 'completed'.
Include only users whose country is 'US' and whose account status is 'active' or 'paused'.
Order the final result by cohort_month, then ride_month.

Use the tables below.

Table Definitions

users

column	type	description
user_id	INT	Primary key
signup_date	DATE	Date the user created an account
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source

rides

column	type	description
ride_id	INT	Primary key
user_id	INT	Rider identifier
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

column	type	description
referral_source_id	INT	Primary key
source_name	VARCHAR(50)	Marketing or referral source

Sample Data

users

user_id	signup_date	country	account_status	referral_source_id
3	2024-02-05	US	paused	2
1	2024-01-10	US	active	1
8	2024-03-20	US	active	4
5	2024-02-18	CA	active	2
2	2024-01-22	US	active	NULL
7	2024-03-01	US	banned	3
4	2024-02-27	US	active	3
6	2024-03-03	US	active	NULL
9	2024-01-31	US	paused	99
10	2024-04-02	US	active	1

rides

ride_id	user_id	ride_date	status	fare_amount
101	1	2024-01-15	completed	18.50
102	1	2024-02-03	completed	12.00
103	1	2024-02-18	cancelled	0.00
104	2	2024-02-10	completed	9.75
105	2	2024-03-01	completed	14.20
106	3	2024-02-20	completed	7.40
107	3	2024-03-05	completed	11.10
108	4	2024-03-12	completed	16.00
109	4	2024-03-28	completed	8.50
110	5	2024-03-15	completed	13.00
111	6	2024-03-18	completed	10.00
112	6	2024-04-01	completed	15.00
113	7	2024-03-22	completed	20.00
114	8	2024-04-05	cancelled	0.00
115	9	2024-02-14	completed	6.25
116	9	2024-02-21	completed	5.75
117	10	2024-04-10	completed	19.00
118	2	2024-03-15	completed	8.80

Expected Output

cohort_month	ride_month	active_riders	completed_rides	avg_rides_per_active_rider
2024-01	2024-01	1	1	1.00
2024-01	2024-02	4	5	1.25
2024-01	2024-03	2	3	1.50
2024-02	2024-02	1	1	1.00
2024-02	2024-03	2	3	1.50
2024-03	2024-03	1	1	1.00
2024-03	2024-04	1	1	1.00
2024-04	2024-04	1	1	1.00

Schema

users

Column	Type	Description
user_idPK	INT	Unique user identifier
signup_date	DATE	Date the user signed up
country	VARCHAR(50)	User country
account_status	VARCHAR(20)	Current account status
referral_source_id	INT	Optional referral source identifier

rides

Column	Type	Description
ride_idPK	INT	Unique ride identifier
user_id	INT	User who took the ride
ride_date	DATE	Date of the ride
status	VARCHAR(20)	Ride completion status
fare_amount	NUMERIC(10,2)	Fare charged for the ride

referral_sources

Column	Type	Description
referral_source_idPK	INT	Referral source identifier
source_name	VARCHAR(50)	Referral or marketing source name

Sample Data

Example 1

Input

{"rides":[[101,1,"2024-01-15","completed","18.50"],[102,1,"2024-02-03","completed","12.00"],[103,1,"2024-02-18","cancelled","0.00"],[104,2,"2024-02-10","completed","9.75"],[105,2,"2024-03-01","completed","14.20"],[106,3,"2024-02-20","completed","7.40"],[107,3,"2024-03-05","completed","11.10"],[108,4,"2024-03-12","completed","16.00"],[109,4,"2024-03-28","completed","8.50"],[110,5,"2024-03-15","completed","13.00"],[111,6,"2024-03-18","completed","10.00"],[112,6,"2024-04-01","completed","15.00"],[1

Output

[["2024-01","2024-01","1","1","1.00"],["2024-01","2024-02","3","4","1.33"],["2024-01","2024-03","1","2","2.00"],["2024-02","2024-02","1","1","1.00"],["2024-02","2024-03","2","3","1.50"],["2024-03","2024-03","1","1","1.00"],["2024-03","2024-04","1","1","1.00"],["2024-04","2024-04","1","1","1.00"]]

Practice SQL

PostgreSQL

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

Up next

Compare Signup Cohorts by RetentionMedium

Top Cities by Ride GrowthMedium

Compare User Retention by CohortMedium

Next question