Drivers Qualifying for City Trip Incentives

Medium
309.

Drivers Qualifying for City Trip Incentives

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

ColumnType
incentive_idint
cityvarchar
start_datedate
end_datedate
min_tripsint
bonusint

Primary key: incentive_id.

One incentive scheme; bonus in rupees.

Table: Driver

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

Table: Trip

ColumnTypeValues
trip_idint
driver_idint
trip_datedate
statusenumcompleted, cancelled

Primary key: trip_id.

Example

The rows must come back in the order shown.

Incentive

incentive_idcitystart_dateend_datemin_tripsbonus
1Pune2024-08-052024-08-073500
2Mumbai2024-08-052024-08-112800

Driver

driver_idnamecity
1SachinPune
2NehaPune
3ImranMumbai
4AshaMumbai

Trip

trip_iddriver_idtrip_datestatus
112024-08-05completed
212024-08-06completed
312024-08-07completed
422024-08-05completed
522024-08-06cancelled
622024-08-08completed
722024-08-07completed
832024-08-11completed
932024-08-04completed
1042024-08-09completed
2 more rows

Output

incentive_iddriver_idcompleted_tripsbonus
113500
242800
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.

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

ColumnType
incentive_idint
cityvarchar
start_datedate
end_datedate
min_tripsint
bonusint

Primary key: incentive_id.

One incentive scheme; bonus in rupees.

Table: Driver

ColumnType
driver_idint
namevarchar
cityvarchar

Primary key: driver_id.

Table: Trip

ColumnType
trip_idint
driver_idint
trip_datedate
statusenum(completed, cancelled)

Primary key: trip_id.

Examples

Example 1

Incentive

incentive_idcitystart_dateend_datemin_tripsbonus
1Pune2024-08-052024-08-073500
2Mumbai2024-08-052024-08-112800

Driver

driver_idnamecity
1SachinPune
2NehaPune
3ImranMumbai
4AshaMumbai

Trip

trip_iddriver_idtrip_datestatus
112024-08-05completed
212024-08-06completed
312024-08-07completed
422024-08-05completed
522024-08-06cancelled
622024-08-08completed
722024-08-07completed
832024-08-11completed
932024-08-04completed
1042024-08-09completed
1142024-08-10completed
1212024-08-09completed

Output

incentive_iddriver_idcompleted_tripsbonus
113500
242800

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_id

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