Ola: Driver-Partners Cleared on All Four Documents

Medium
837.

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. 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

ColumnType
driver_idint
full_namevarchar
cityvarchar
applied_ondate

Primary key: driver_id.

One row per person who applied to drive.

Table: DocumentReview

ColumnTypeValues
review_idint
driver_idint
doc_typeenumdriving_licence, vehicle_rc, insurance, police_verification
verdictenumapproved, rejected
reviewed_atdatetime

Primary key: review_id.

One row per review. Two reviews of the same driver's same document never share a reviewed_at.

Example

The rows must come back in the order shown.

DriverApplicant

driver_idfull_namecityapplied_on
1Ramesh GowdaBengaluru2024-04-01
2Sunil JadhavPune2024-04-02
3Ajay VermaDelhi2024-04-02
4Kiran NaiduHyderabad2024-04-03

DocumentReview

review_iddriver_iddoc_typeverdictreviewed_at
11driving_licenceapproved2024-04-02 10:00:00
21vehicle_rcrejected2024-04-02 10:05:00
31insuranceapproved2024-04-02 10:10:00
41police_verificationapproved2024-04-05 16:00:00
51vehicle_rcapproved2024-04-06 11:00:00
62driving_licenceapproved2024-04-03 09:00:00
72vehicle_rcapproved2024-04-03 09:10:00
82insuranceapproved2024-04-03 09:20:00
93driving_licenceapproved2024-04-03 12:00:00
103vehicle_rcapproved2024-04-03 12:10:00
7 more rows

Output

driver_idfull_namecleared_at
4Kiran Naidu2024-04-05 09:00:00
1Ramesh Gowda2024-04-06 11:00:00
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-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

ColumnType
driver_idint
full_namevarchar
cityvarchar
applied_ondate

Primary key: driver_id.

One row per person who applied to drive.

Table: DocumentReview

ColumnType
review_idint
driver_idint
doc_typeenum(driving_licence, vehicle_rc, insurance, police_verification)
verdictenum(approved, rejected)
reviewed_atdatetime

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_idfull_namecityapplied_on
1Ramesh GowdaBengaluru2024-04-01
2Sunil JadhavPune2024-04-02
3Ajay VermaDelhi2024-04-02
4Kiran NaiduHyderabad2024-04-03

DocumentReview

review_iddriver_iddoc_typeverdictreviewed_at
11driving_licenceapproved2024-04-02 10:00:00
21vehicle_rcrejected2024-04-02 10:05:00
31insuranceapproved2024-04-02 10:10:00
41police_verificationapproved2024-04-05 16:00:00
51vehicle_rcapproved2024-04-06 11:00:00
62driving_licenceapproved2024-04-03 09:00:00
72vehicle_rcapproved2024-04-03 09:10:00
82insuranceapproved2024-04-03 09:20:00
93driving_licenceapproved2024-04-03 12:00:00
103vehicle_rcapproved2024-04-03 12:10:00
113insuranceapproved2024-04-03 12:20:00
123police_verificationapproved2024-04-04 08:00:00
133insurancerejected2024-04-05 08:00:00
144driving_licenceapproved2024-04-04 10:00:00
154vehicle_rcapproved2024-04-04 10:30:00
164insuranceapproved2024-04-04 11:00:00
174police_verificationapproved2024-04-05 09:00:00

Output

driver_idfull_namecleared_at
4Kiran Naidu2024-04-05 09:00:00
1Ramesh Gowda2024-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_id

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