Rider Login Hours and July Incentive Tier

Medium
160.

Rider Login Hours and July Incentive Tier

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

ColumnType
rider_idint
namevarchar
joined_ondate

Primary key: rider_id.

One row per delivery partner.

Table: RiderShift

ColumnType
shift_idint
rider_idint
login_atdatetime
logout_atdatetime

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.

Example

The rows must come back in the order shown.

Rider

rider_idnamejoined_on
1Nikhil2023-11-02
2Simran2024-01-15
3Dev2024-06-20
4Zara2024-07-01

RiderShift

shift_idrider_idlogin_atlogout_at
112024-07-01 08:00:002024-07-01 20:00:00
212024-07-02 08:00:002024-07-02 20:00:00
312024-07-03 09:00:002024-07-03 21:00:00
412024-07-04 10:00:002024-07-04 14:00:00
522024-06-30 18:00:002024-07-01 02:00:00
622024-07-10 11:00:002024-07-10 21:00:00
722024-07-11 11:00:002024-07-11 21:00:00
832024-07-31 19:00:002024-08-01 01:25:00
932024-07-20 12:00:00NULL

Output

rider_idnamehours_loggedtier
1Nikhil40Gold
2Simran20Silver
3Dev6.42None
4Zara0None
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.

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

ColumnType
rider_idint
namevarchar
joined_ondate

Primary key: rider_id.

One row per delivery partner.

Table: RiderShift

ColumnType
shift_idint
rider_idint
login_atdatetime
logout_atdatetime

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_idnamejoined_on
1Nikhil2023-11-02
2Simran2024-01-15
3Dev2024-06-20
4Zara2024-07-01

RiderShift

shift_idrider_idlogin_atlogout_at
112024-07-01 08:00:002024-07-01 20:00:00
212024-07-02 08:00:002024-07-02 20:00:00
312024-07-03 09:00:002024-07-03 21:00:00
412024-07-04 10:00:002024-07-04 14:00:00
522024-06-30 18:00:002024-07-01 02:00:00
622024-07-10 11:00:002024-07-10 21:00:00
722024-07-11 11:00:002024-07-11 21:00:00
832024-07-31 19:00:002024-08-01 01:25:00
932024-07-20 12:00:00NULL

Output

rider_idnamehours_loggedtier
1Nikhil40Gold
2Simran20Silver
3Dev6.42None
4Zara0None

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_id

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