Cohort Retention From Driving Frequency | Dataford Interview Questions

Arity wants to understand whether driving frequency is associated with driver retention. Write a PostgreSQL query using completed trips to assign each driver to a first-trip cohort and a driving-frequency segment.

Requirements

Define each driver's cohort as the month of their first completed trip.
Count completed trips during the first 60 days after that first trip. Classify drivers with at least two trips as Frequent, and others as Occasional.
For each cohort, segment, and month offset 0 through 2, return cohort size, retained drivers, and the retention rate. Month 0 is the cohort month.
Exclude cancelled trips and drivers without a completed trip. Order by cohort month, segment, and month offset.

Schema

drivers

Column	Type	Description
driver_idPK	INT	Unique Arity driver identifier
driver_name	VARCHAR(100)	Driver display name
signup_date	DATE	Date the driver joined Arity

trips

Column	Type	Description
trip_idPK	INT	Unique trip identifier
driver_id	INT	Driver associated with the trip
trip_date	DATE	Calendar date of the trip
trip_status	VARCHAR(20)	Trip lifecycle status

Your question is Cohort Retention From Driving Frequency. 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