Ola: Driver-Partner Activation and Retention by Approval Month
The supply team judges each month's batch of newly approved driver-partners by how they drive in their first four weeks.
- Difficulty: Hard
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #843
Problem statement
The supply team judges each month's batch of newly approved driver-partners by how they drive in their first four weeks.
Measure each driver's weeks from their approved_on date: week 1 is days 0–6 after approval (DATEDIFF(completed_at, approved_on)), week 2 is days 7–13, week 3 days 14–20 and week 4 days 21–27. Only completed trips count. For every approval month (cohort_month as YYYY-MM), return approved (drivers approved that month), active_week_1 (drivers with a completed trip in week 1), active_week_4 (in week 4) and all_four_weeks (drivers with a completed trip in each of weeks 1 to 4). Drivers still awaiting approval (approved_on is NULL) are left out. Order the rows by cohort_month.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: DriverPartner
| Column | Type |
|---|---|
| driver_id | int |
| city | varchar |
| approved_on | date |
Primary key: driver_id.
One row per driver-partner; approved_on is NULL until onboarding finishes.
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| status | enum(completed, cancelled) |
| completed_at | datetime |
Primary key: trip_id.
One row per trip a driver accepted; for a cancelled trip completed_at is when it was cancelled. Trips never come before approval.
Examples
Example 1
DriverPartner
| driver_id | city | approved_on |
|---|---|---|
| 1 | Bengaluru | 2024-01-10 |
| 2 | Pune | 2024-01-25 |
| 3 | Chennai | 2024-02-05 |
| 4 | Delhi | NULL |
| 5 | Hyderabad | 2024-02-20 |
Trip
| trip_id | driver_id | status | completed_at |
|---|---|---|---|
| 1 | 1 | completed | 2024-01-10 09:00:00 |
| 2 | 1 | completed | 2024-01-17 18:30:00 |
| 3 | 1 | completed | 2024-01-24 11:00:00 |
| 4 | 1 | completed | 2024-01-31 20:00:00 |
| 5 | 2 | completed | 2024-01-31 23:00:00 |
| 6 | 2 | cancelled | 2024-02-21 10:00:00 |
| 7 | 3 | completed | 2024-02-12 08:00:00 |
| 8 | 3 | completed | 2024-03-03 19:00:00 |
Output
| cohort_month | approved | active_week_1 | active_week_4 | all_four_weeks |
|---|---|---|---|---|
| 2024-01 | 2 | 2 | 1 | 1 |
| 2024-02 | 2 | 0 | 1 | 0 |
How to solve Ola: Driver-Partner Activation and Retention by Approval Month
Cohort questions have two levels: facts about each member, then counts over each cohort. The per-driver level joins a driver's completed trips with a LEFT JOIN (the status test belongs in ON, so drivers whose trips were all cancelled still count as approved) and computes three facts in one GROUP BY. MAX(CASE …) is 1 when any trip falls in days 0–6, likewise for days 21–27. For "every one of the four weeks", each trip's week number is DATEDIFF(…) DIV 7 (0–3 within the first 28 days), and COUNT(DISTINCT …) over trips inside that window counts how many different weeks were active; four means all of them. A driver with no trips has a NULL DATEDIFF, every CASE gives 0, and the distinct count is 0.
The cohort level then groups those per-driver rows by DATE_FORMAT(approved_on, '%Y-%m') and adds up the flags. Pending drivers are removed in WHERE before anything is counted. Days are counted as calendar days, so a trip at 23:00 on day 6 is still week 1 and a trip on day 28 belongs to none of the four weeks.
The alternative replaces the join with four correlated EXISTS tests per driver — one per week — which reads more literally but scans each driver's trips four times.
Reference solution (MySQL)
WITH per_driver AS (
SELECT d.driver_id, d.approved_on,
MAX(CASE WHEN DATEDIFF(t.completed_at, d.approved_on) BETWEEN 0 AND 6 THEN 1 ELSE 0 END) AS w1,
MAX(CASE WHEN DATEDIFF(t.completed_at, d.approved_on) BETWEEN 21 AND 27 THEN 1 ELSE 0 END) AS w4,
COUNT(DISTINCT CASE WHEN DATEDIFF(t.completed_at, d.approved_on) BETWEEN 0 AND 27
THEN DATEDIFF(t.completed_at, d.approved_on) DIV 7 END) AS weeks_active
FROM DriverPartner d
LEFT JOIN Trip t ON t.driver_id = d.driver_id AND t.status = 'completed'
WHERE d.approved_on IS NOT NULL
GROUP BY d.driver_id, d.approved_on
)
SELECT DATE_FORMAT(approved_on, '%Y-%m') AS cohort_month,
COUNT(*) AS approved,
SUM(w1) AS active_week_1,
SUM(w4) AS active_week_4,
SUM(CASE WHEN weeks_active = 4 THEN 1 ELSE 0 END) AS all_four_weeks
FROM per_driver
GROUP BY DATE_FORMAT(approved_on, '%Y-%m')
ORDER BY cohort_monthAnother way
WITH flags AS (
SELECT d.driver_id, DATE_FORMAT(d.approved_on, '%Y-%m') AS cohort_month,
EXISTS (SELECT 1 FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed' AND DATEDIFF(t.completed_at, d.approved_on) BETWEEN 0 AND 6) AS w1,
EXISTS (SELECT 1 FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed' AND DATEDIFF(t.completed_at, d.approved_on) BETWEEN 7 AND 13) AS w2,
EXISTS (SELECT 1 FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed' AND DATEDIFF(t.completed_at, d.approved_on) BETWEEN 14 AND 20) AS w3,
EXISTS (SELECT 1 FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed' AND DATEDIFF(t.completed_at, d.approved_on) BETWEEN 21 AND 27) AS w4
FROM DriverPartner d
WHERE d.approved_on IS NOT NULL
)
SELECT cohort_month, COUNT(*) AS approved, SUM(w1) AS active_week_1, SUM(w4) AS active_week_4,
SUM(CASE WHEN w1 = 1 AND w2 = 1 AND w3 = 1 AND w4 = 1 THEN 1 ELSE 0 END) AS all_four_weeks
FROM flags
GROUP BY cohort_month
ORDER BY cohort_month← Ola: Each Scooter's Opening and Closing Readings per Day · Ola: Scooters Covered by a Care Plan on Each Month End →