Drivers Qualifying for City Trip Incentives — SQL Problem
The growth team runs trip incentives per city: a driver in the incentive's city who completes at least min_trips trips dated within the incentive window…
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #309
Problem statement
The growth team runs trip incentives per city: a driver in the incentive's city who completes at least min_trips trips dated within the incentive window (start_date to end_date, both inclusive) earns the bonus. A driver can qualify for several incentives; cancelled trips never count.
Return incentive_id, driver_id, completed_trips (the driver's completed trips in that window) and bonus for every qualifying pair, ordered by incentive_id then driver_id.
Tables
Table: Incentive
| Column | Type |
|---|---|
| incentive_id | int |
| city | varchar |
| start_date | date |
| end_date | date |
| min_trips | int |
| bonus | int |
Primary key: incentive_id.
One incentive scheme; bonus in rupees.
Table: Driver
| Column | Type |
|---|---|
| driver_id | int |
| name | varchar |
| city | varchar |
Primary key: driver_id.
Table: Trip
| Column | Type |
|---|---|
| trip_id | int |
| driver_id | int |
| trip_date | date |
| status | enum(completed, cancelled) |
Primary key: trip_id.
Examples
Example 1
Incentive
| incentive_id | city | start_date | end_date | min_trips | bonus |
|---|---|---|---|---|---|
| 1 | Pune | 2024-08-05 | 2024-08-07 | 3 | 500 |
| 2 | Mumbai | 2024-08-05 | 2024-08-11 | 2 | 800 |
Driver
| driver_id | name | city |
|---|---|---|
| 1 | Sachin | Pune |
| 2 | Neha | Pune |
| 3 | Imran | Mumbai |
| 4 | Asha | Mumbai |
Trip
| trip_id | driver_id | trip_date | status |
|---|---|---|---|
| 1 | 1 | 2024-08-05 | completed |
| 2 | 1 | 2024-08-06 | completed |
| 3 | 1 | 2024-08-07 | completed |
| 4 | 2 | 2024-08-05 | completed |
| 5 | 2 | 2024-08-06 | cancelled |
| 6 | 2 | 2024-08-08 | completed |
| 7 | 2 | 2024-08-07 | completed |
| 8 | 3 | 2024-08-11 | completed |
| 9 | 3 | 2024-08-04 | completed |
| 10 | 4 | 2024-08-09 | completed |
| 11 | 4 | 2024-08-10 | completed |
| 12 | 1 | 2024-08-09 | completed |
Output
| incentive_id | driver_id | completed_trips | bonus |
|---|---|---|---|
| 1 | 1 | 3 | 500 |
| 2 | 4 | 2 | 800 |
How to solve Drivers Qualifying for City Trip Incentives
Each row of the answer is a pair (incentive, driver), so build those pairs with joins: Driver joins Incentive on the city, and Trip joins on the driver and a range condition — t.trip_date BETWEEN i.start_date AND i.end_date — plus status = 'completed'. A join condition need not be an equality; a trip on 8 August simply fails to match a scheme ending on the 7th.
Group by the pair and count the matched trips. The threshold differs per incentive, so HAVING COUNT(*) >= i.min_trips compares each group with its own incentive's value; because min_trips and bonus are functionally determined by the incentive, list them in GROUP BY so MySQL's ONLY_FULL_GROUP_BY accepts them.
An inner join drops pairs with no qualifying trips at all, which is fine since min_trips is at least 1. The alternative counts with a correlated subquery for every eligible pair, which is clearer but re-scans trips per pair; with an index on Trip(driver_id, trip_date) both are quick.
Reference solution (MySQL)
SELECT i.incentive_id, d.driver_id, COUNT(*) AS completed_trips, i.bonus
FROM Incentive i
JOIN Driver d ON d.city = i.city
JOIN Trip t ON t.driver_id = d.driver_id
AND t.status = 'completed'
AND t.trip_date BETWEEN i.start_date AND i.end_date
GROUP BY i.incentive_id, d.driver_id, i.bonus, i.min_trips
HAVING COUNT(*) >= i.min_trips
ORDER BY i.incentive_id, d.driver_idAnother way
SELECT incentive_id, driver_id, completed_trips, bonus FROM (
SELECT i.incentive_id, d.driver_id, i.bonus, i.min_trips,
(SELECT COUNT(*) FROM Trip t WHERE t.driver_id = d.driver_id AND t.status = 'completed'
AND t.trip_date >= i.start_date AND t.trip_date <= i.end_date) AS completed_trips
FROM Incentive i CROSS JOIN Driver d WHERE d.city = i.city
) x WHERE completed_trips >= min_trips AND completed_trips > 0
ORDER BY incentive_id, driver_id← Top-Earning Driver in Each City for Q1 2024 · Latest Tracking Scan for Each Parcel →