Problem

Table: Bikes

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

+-------------+----------+ 
| Column Name | Type     | 
+-------------+----------+ 
| ride_id     | int      | 
| bike_number | int      | 
| start_time  | datetime |
| end_time    | datetime |
+-------------+----------+
ride_id column contains unique values.
Each row contains a ride information that includes ride_id, bike number, start and end time of the ride.
It is guaranteed that start_time and end_time are valid datetime values.

Write a solution to find the last time when each bike was used.

Return the result table ordered by the bikes that were most recently used.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Input:Bikes table:
+---------+-------------+---------------------+---------------------+ 
| ride_id | bike_number | start_time          | end_time            |  
+---------+-------------+---------------------+---------------------+
| 1       | W00576      | 2012-03-25 11:30:00 | 2012-03-25 12:40:00 |
| 2       | W00300      | 2012-03-25 10:30:00 | 2012-03-25 10:50:00 |
| 3       | W00455      | 2012-03-26 14:30:00 | 2012-03-26 17:40:00 |
| 4       | W00455      | 2012-03-25 12:30:00 | 2012-03-25 13:40:00 |
| 5       | W00576      | 2012-03-25 08:10:00 | 2012-03-25 09:10:00 |
| 6       | W00576      | 2012-03-28 02:30:00 | 2012-03-28 02:50:00 |
+---------+-------------+---------------------+---------------------+ 

Output:
+-------------+---------------------+ 
| bike_number | end_time            |  
+-------------+---------------------+
| W00576      | 2012-03-28 02:50:00 |
| W00455      | 2012-03-26 17:40:00 |
| W00300      | 2012-03-25 10:50:00 |
+-------------+---------------------+ 
Explanation: 
bike with number W00576 has three rides, out of that, most recent ride is with ride_id 6 which ended on 2012-03-28 02:50:00.
bike with number W00300 has only 1 ride so we will include end_time in output directly. 
bike with number W00455 has two rides, out of that, most recent ride is with ride_id 3 which ended on 2012-03-26 17:40:00. 
Returning output in order by the bike that were most recently used.

Solution

Method 1 – Group By and Max Date (MySQL)

Intuition

To find the last time each bike was used, group the rides by bike_number and select the maximum end_time for each bike. To order by most recently used, sort the results by the last end_time in descending order.

Approach

  1. Group the table by bike_number.
  2. For each group, select the maximum end_time as last_time_used.
  3. Order the results by last_time_used in descending order.

Code

1
2
3
4

SELECT bike_number, MAX(end_time) AS last_time_used
FROM Bikes
GROUP BY bike_number
ORDER BY last_time_used DESC;

Complexity

  • ⏰ Time complexity: O(n) — One scan and group by bike_number.
  • 🧺 Space complexity: O(k) — For storing k bikes in the result.