Leetcode 2687 - Bikes Last Time Used GROUP MAX() - Solved & Explained by Everyday Data Science
Bikes Last Time Used - Video Solution
Watch 2 video solutions for Bikes Last Time Used , a easy level problem involving Database. This walkthrough by Everyday Data Science has 582 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Problem Statement
Table: Bikes
+-------------+----------+ | 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.
Example 1:
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.
Approach Overview
Problem Overview: The query asks for the most recent time each bike was used based on ride history. You scan the trip records and return the latest usage timestamp per bike.
Approach 1: Correlated Subquery (O(n^2) time, O(1) space)
A straightforward solution runs a correlated subquery for each bike to compute its latest usage time. For every bike row, execute a subquery that selects MAX(ride_time) from the rides table where the bike IDs match. This works because MAX() returns the most recent timestamp for that bike. The downside is repeated scans of the rides table, which leads to O(n^2) behavior when the dataset grows.
Approach 2: Aggregation with GROUP BY (O(n) time, O(k) space)
The efficient approach aggregates ride records once using MAX() with GROUP BY bike_id. The database groups all rides belonging to the same bike and computes the latest timestamp directly. If the problem requires listing all bikes even when they were never used, combine the aggregated rides table with the bikes table using a LEFT JOIN. This approach scans the rides table a single time, making it linear with respect to the number of ride records.
SQL engines optimize GROUP BY aggregations efficiently using indexes and hash grouping. Instead of repeatedly querying the same table, the query planner builds grouped results once and returns the latest timestamp per bike. This pattern appears frequently in analytics queries where you need the "latest record per entity".
Core SQL operations involved:
MAX(timestamp) to extract the latest usage time.
GROUP BY bike_id to aggregate rides per bike.
LEFT JOIN to ensure bikes without rides still appear in the result set if required.
These operations fall under common database and SQL querying patterns, especially aggregation problems like "latest record per group" often solved with aggregation functions.
Recommended for interviews: The aggregation approach using GROUP BY and MAX(). It demonstrates that you understand how to compute per‑entity summaries efficiently in SQL. The correlated subquery works but signals weaker query optimization awareness.
Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery | O(n^2) | O(1) | Small datasets or quick prototype queries |
| Aggregation with GROUP BY | O(n) | O(k) | General case; efficient for large ride history tables |
| LEFT JOIN with Aggregated Subquery | O(n) | O(k) | When all bikes must appear even if never used |