Ola: Driver-Partner Activation and Retention by Approval Month

Hard
843.

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.

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

ColumnType
driver_idint
cityvarchar
approved_ondate

Primary key: driver_id.

One row per driver-partner; approved_on is NULL until onboarding finishes.

Table: Trip

ColumnTypeValues
trip_idint
driver_idint
statusenumcompleted, cancelled
completed_atdatetime

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.

Example

The rows must come back in the order shown.

DriverPartner

driver_idcityapproved_on
1Bengaluru2024-01-10
2Pune2024-01-25
3Chennai2024-02-05
4DelhiNULL
5Hyderabad2024-02-20

Trip

trip_iddriver_idstatuscompleted_at
11completed2024-01-10 09:00:00
21completed2024-01-17 18:30:00
31completed2024-01-24 11:00:00
41completed2024-01-31 20:00:00
52completed2024-01-31 23:00:00
62cancelled2024-02-21 10:00:00
73completed2024-02-12 08:00:00
83completed2024-03-03 19:00:00

Output

cohort_monthapprovedactive_week_1active_week_4all_four_weeks
2024-012211
2024-022010
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
driver_idint
cityvarchar
approved_ondate

Primary key: driver_id.

One row per driver-partner; approved_on is NULL until onboarding finishes.

Table: Trip

ColumnType
trip_idint
driver_idint
statusenum(completed, cancelled)
completed_atdatetime

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_idcityapproved_on
1Bengaluru2024-01-10
2Pune2024-01-25
3Chennai2024-02-05
4DelhiNULL
5Hyderabad2024-02-20

Trip

trip_iddriver_idstatuscompleted_at
11completed2024-01-10 09:00:00
21completed2024-01-17 18:30:00
31completed2024-01-24 11:00:00
41completed2024-01-31 20:00:00
52completed2024-01-31 23:00:00
62cancelled2024-02-21 10:00:00
73completed2024-02-12 08:00:00
83completed2024-03-03 19:00:00

Output

cohort_monthapprovedactive_week_1active_week_4all_four_weeks
2024-012211
2024-022010

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_month

Another 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 →