Bikes Last Time Used - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min read

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.

Solution

Code

MySQL

Try this approach in the editor →

Detailed 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

Video Solution

Leetcode 2687 - Bikes Last Time Used GROUP MAX() - Solved & Explained by Everyday Data Science • Everyday Data Science • 582 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Bikes Last Time Used easy or hard?

Bikes Last Time Used is categorized as an Easy database problem with an acceptance rate above 80%. It focuses on basic SQL aggregation using MAX() and GROUP BY, which are fundamental concepts in SQL interviews.

Bikes Last Time Used Python/Java solution

This problem is a SQL database query rather than a typical algorithm implementation. The expected solution is written in SQL (such as MySQL) using aggregation functions like MAX() and GROUP BY to compute the last usage time for each bike.

How to solve Bikes Last Time Used in O(n)?

Use an aggregation query that groups ride history by bike_id and calculates MAX(ride_time). SQL engines compute grouped aggregates in a single pass over the table. Optionally join the result with the bikes table using LEFT JOIN if the output must include bikes with no rides.

What is the best approach for Bikes Last Time Used ?

The optimal approach uses SQL aggregation with MAX() and GROUP BY. Group ride records by bike_id and compute the latest timestamp using MAX(). This scans the rides table once, giving O(n) time complexity and efficient execution in MySQL.

Is Bikes Last Time Used asked at Google/Amazon/Meta?

Database aggregation and "latest record per group" SQL questions are common across companies like Amazon, Google, and Meta in data engineering and backend interviews. Problems similar to Bikes Last Time Used test knowledge of GROUP BY, MAX(), and joins.

What data structure is used in Bikes Last Time Used ?

The solution relies on SQL table aggregation rather than traditional in‑memory data structures. Internally, the database may use hash grouping or indexed scans to compute MAX(timestamp) per bike efficiently.

What is the time complexity of Bikes Last Time Used ?

The optimal SQL solution runs in O(n) time where n is the number of ride records. The database performs a single scan of the rides table and groups rows by bike_id to compute MAX(timestamp). Space complexity is O(k) for storing grouped results for k bikes.

Ready to solve this problem?

Practice Bikes Last Time Used with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyEasy

Acceptance80.6%

Approaches1

Reading time4 min

Practice this problem

Open in Editor