Rider Login Hours and July Incentive Tier — SQL Problem
Riders earn a monthly incentive by the hours they stay logged in.
- Difficulty: Medium
- Topics: Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #160
Problem statement
Riders earn a monthly incentive by the hours they stay logged in. A shift belongs to the month its login_at falls in, and its length is TIMESTAMPDIFF(MINUTE, login_at, logout_at). A shift that is still open (logout_at NULL) is not counted yet.
For every rider, return rider_id, name, hours_logged (total counted minutes of July 2024 shifts divided by 60, rounded to 2 decimal places; 0 for a rider with none) and tier: Gold for 40 hours or more, Silver for 20 hours or more, otherwise None (decided on the exact hours). Order the rows by hours_logged descending, then rider_id ascending.
Tables
Table: Rider
| Column | Type |
|---|---|
| rider_id | int |
| name | varchar |
| joined_on | date |
Primary key: rider_id.
One row per delivery partner.
Table: RiderShift
| Column | Type |
|---|---|
| shift_id | int |
| rider_id | int |
| login_at | datetime |
| logout_at | datetime |
Primary key: shift_id.
One row per login session in the rider app; rider_id is always in Rider. logout_at is NULL while the rider is still logged in.
Examples
Example 1
Rider
| rider_id | name | joined_on |
|---|---|---|
| 1 | Nikhil | 2023-11-02 |
| 2 | Simran | 2024-01-15 |
| 3 | Dev | 2024-06-20 |
| 4 | Zara | 2024-07-01 |
RiderShift
| shift_id | rider_id | login_at | logout_at |
|---|---|---|---|
| 1 | 1 | 2024-07-01 08:00:00 | 2024-07-01 20:00:00 |
| 2 | 1 | 2024-07-02 08:00:00 | 2024-07-02 20:00:00 |
| 3 | 1 | 2024-07-03 09:00:00 | 2024-07-03 21:00:00 |
| 4 | 1 | 2024-07-04 10:00:00 | 2024-07-04 14:00:00 |
| 5 | 2 | 2024-06-30 18:00:00 | 2024-07-01 02:00:00 |
| 6 | 2 | 2024-07-10 11:00:00 | 2024-07-10 21:00:00 |
| 7 | 2 | 2024-07-11 11:00:00 | 2024-07-11 21:00:00 |
| 8 | 3 | 2024-07-31 19:00:00 | 2024-08-01 01:25:00 |
| 9 | 3 | 2024-07-20 12:00:00 | NULL |
Output
| rider_id | name | hours_logged | tier |
|---|---|---|---|
| 1 | Nikhil | 40 | Gold |
| 2 | Simran | 20 | Silver |
| 3 | Dev | 6.42 | None |
| 4 | Zara | 0 | None |
How to solve Rider Login Hours and July Incentive Tier
The month rule is about login_at only: a shift from 30 June evening to 1 July morning is June's, and one starting late on 31 July is July's even though it ends in August. So the filter is the half-open range login_at >= '2024-07-01' AND login_at < '2024-08-01', plus logout_at IS NOT NULL for open shifts (whose length would be NULL anyway).
Because every rider must be listed, those conditions go in the LEFT JOIN's ON clause, not in WHERE; in WHERE they would delete the NULL-extended row of a rider with no July shift. Summing minutes over a rider with no matching shift gives NULL, so COALESCE makes it 0 before dividing by 60 and rounding.
The tier compares exact minutes with 40 × 60 and 20 × 60; CASE tries Gold first so a 45-hour rider is not labelled Silver. Pre-aggregating July in a CTE and joining it to Rider is the same plan in two steps. One pass over the shifts.
Reference solution (MySQL)
SELECT r.rider_id, r.name,
ROUND(COALESCE(SUM(TIMESTAMPDIFF(MINUTE, s.login_at, s.logout_at)), 0) / 60, 2) AS hours_logged,
CASE
WHEN COALESCE(SUM(TIMESTAMPDIFF(MINUTE, s.login_at, s.logout_at)), 0) >= 40 * 60 THEN 'Gold'
WHEN COALESCE(SUM(TIMESTAMPDIFF(MINUTE, s.login_at, s.logout_at)), 0) >= 20 * 60 THEN 'Silver'
ELSE 'None'
END AS tier
FROM Rider r
LEFT JOIN RiderShift s
ON s.rider_id = r.rider_id
AND s.login_at >= '2024-07-01' AND s.login_at < '2024-08-01'
AND s.logout_at IS NOT NULL
GROUP BY r.rider_id, r.name
ORDER BY hours_logged DESC, r.rider_idAnother way
WITH july AS (
SELECT rider_id, SUM(TIMESTAMPDIFF(MINUTE, login_at, logout_at)) AS minutes
FROM RiderShift
WHERE DATE_FORMAT(login_at, '%Y-%m') = '2024-07' AND logout_at IS NOT NULL
GROUP BY rider_id
)
SELECT r.rider_id, r.name, ROUND(IFNULL(j.minutes, 0) / 60, 2) AS hours_logged,
IF(IFNULL(j.minutes, 0) >= 2400, 'Gold', IF(IFNULL(j.minutes, 0) >= 1200, 'Silver', 'None')) AS tier
FROM Rider r LEFT JOIN july j ON j.rider_id = r.rider_id
ORDER BY hours_logged DESC, r.rider_id← Orders Above the Customer's Own Average Basket · Month-Over-Month Delivered GMV Change by City →