Ola: Driver-Partners Cleared on All Four Documents
A new driver-partner can start taking rides only when four documents are verified: driving_licence, vehicle_rc, insurance and police_verification.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #837
Problem statement
A new driver-partner can start taking rides only when four documents are verified: driving_licence, vehicle_rc, insurance and police_verification. A document may be reviewed several times (re-uploads after a rejection), and only its latest review counts.
Return every applicant whose latest review of each of the four documents is approved, with driver_id, full_name and cleared_at — the time of the latest of those reviews, when the last document was cleared. An applicant missing any review, or whose latest review of any document is a rejection, is not returned. Order the rows by cleared_at, then driver_id.
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: DriverApplicant
| Column | Type |
|---|---|
| driver_id | int |
| full_name | varchar |
| city | varchar |
| applied_on | date |
Primary key: driver_id.
One row per person who applied to drive.
Table: DocumentReview
| Column | Type |
|---|---|
| review_id | int |
| driver_id | int |
| doc_type | enum(driving_licence, vehicle_rc, insurance, police_verification) |
| verdict | enum(approved, rejected) |
| reviewed_at | datetime |
Primary key: review_id.
One row per review. Two reviews of the same driver's same document never share a reviewed_at.
Examples
Example 1
DriverApplicant
| driver_id | full_name | city | applied_on |
|---|---|---|---|
| 1 | Ramesh Gowda | Bengaluru | 2024-04-01 |
| 2 | Sunil Jadhav | Pune | 2024-04-02 |
| 3 | Ajay Verma | Delhi | 2024-04-02 |
| 4 | Kiran Naidu | Hyderabad | 2024-04-03 |
DocumentReview
| review_id | driver_id | doc_type | verdict | reviewed_at |
|---|---|---|---|---|
| 1 | 1 | driving_licence | approved | 2024-04-02 10:00:00 |
| 2 | 1 | vehicle_rc | rejected | 2024-04-02 10:05:00 |
| 3 | 1 | insurance | approved | 2024-04-02 10:10:00 |
| 4 | 1 | police_verification | approved | 2024-04-05 16:00:00 |
| 5 | 1 | vehicle_rc | approved | 2024-04-06 11:00:00 |
| 6 | 2 | driving_licence | approved | 2024-04-03 09:00:00 |
| 7 | 2 | vehicle_rc | approved | 2024-04-03 09:10:00 |
| 8 | 2 | insurance | approved | 2024-04-03 09:20:00 |
| 9 | 3 | driving_licence | approved | 2024-04-03 12:00:00 |
| 10 | 3 | vehicle_rc | approved | 2024-04-03 12:10:00 |
| 11 | 3 | insurance | approved | 2024-04-03 12:20:00 |
| 12 | 3 | police_verification | approved | 2024-04-04 08:00:00 |
| 13 | 3 | insurance | rejected | 2024-04-05 08:00:00 |
| 14 | 4 | driving_licence | approved | 2024-04-04 10:00:00 |
| 15 | 4 | vehicle_rc | approved | 2024-04-04 10:30:00 |
| 16 | 4 | insurance | approved | 2024-04-04 11:00:00 |
| 17 | 4 | police_verification | approved | 2024-04-05 09:00:00 |
Output
| driver_id | full_name | cleared_at |
|---|---|---|
| 4 | Kiran Naidu | 2024-04-05 09:00:00 |
| 1 | Ramesh Gowda | 2024-04-06 11:00:00 |
How to solve Ola: Driver-Partners Cleared on All Four Documents
Two ideas combine here: latest row per group and all of a set (relational division). The first is a ROW_NUMBER() partitioned by driver and document, newest first; rn = 1 keeps the review that currently stands for each document. A rejection followed by an approval leaves the approval; an approval followed by a rejection leaves the rejection.
Then each applicant's standing reviews are grouped. The applicant is cleared only when there are four of them (no document missing) and four are approvals. Counting both conditions in HAVING handles a missing document and a standing rejection in one place. cleared_at is the MAX of the four review times — the moment the last document passed — and since those are the latest review of each document, it is also the driver's latest review of any kind, which the alternative uses.
The alternative finds approved reviews that no later review of the same document overrides (NOT EXISTS with a later reviewed_at) and requires four of them. It needs no window function but runs a correlated check per review. Ties in cleared_at are broken by driver_id.
Reference solution (MySQL)
WITH latest AS (
SELECT driver_id, doc_type, verdict, reviewed_at,
ROW_NUMBER() OVER (PARTITION BY driver_id, doc_type ORDER BY reviewed_at DESC) AS rn
FROM DocumentReview
)
SELECT a.driver_id, a.full_name, MAX(l.reviewed_at) AS cleared_at
FROM DriverApplicant a
JOIN latest l ON l.driver_id = a.driver_id AND l.rn = 1
GROUP BY a.driver_id, a.full_name
HAVING COUNT(*) = 4 AND SUM(CASE WHEN l.verdict = 'approved' THEN 1 ELSE 0 END) = 4
ORDER BY cleared_at, a.driver_idAnother way
SELECT a.driver_id, a.full_name,
(SELECT MAX(r.reviewed_at) FROM DocumentReview r WHERE r.driver_id = a.driver_id) AS cleared_at
FROM DriverApplicant a
WHERE 4 = (
SELECT COUNT(*) FROM DocumentReview r
WHERE r.driver_id = a.driver_id AND r.verdict = 'approved'
AND NOT EXISTS (SELECT 1 FROM DocumentReview r2
WHERE r2.driver_id = r.driver_id AND r2.doc_type = r.doc_type AND r2.reviewed_at > r.reviewed_at)
)
ORDER BY cleared_at, a.driver_id← Ola: Wallet Cashback That Was Not Fully Credited · Ola: OTA Update Adoption in Its First Week, by City →