Bikes Last Time Used - Problem

You have a table Bikes that tracks bike sharing ride information. Each row contains details about a ride including the bike number and the start/end times.

Task: Find the last time when each bike was used and return the result ordered by the bikes that were most recently used (latest end times first).

  • Each ride_id is unique
  • Both start_time and end_time are valid datetime values
  • A bike's "last used" time is determined by its latest end_time

Table Schema

Bikes
Column Name Type Description
ride_id PK int Unique identifier for each ride
bike_number int Identifier for the bike used
start_time datetime When the ride started
end_time datetime When the ride ended
Primary Key: ride_id
Note: Each row represents a single bike ride with guaranteed valid datetime values

Input & Output

Example 1 — Multiple rides per bike
Input Table:
ride_id bike_number start_time end_time
1 101 2023-09-01 09:00:00 2023-09-01 09:30:00
2 102 2023-09-01 10:00:00 2023-09-01 10:45:00
3 101 2023-09-01 11:00:00 2023-09-01 11:20:00
4 103 2023-09-01 12:00:00 2023-09-01 12:15:00
Output:
bike_number last_used_time
103 2023-09-01 12:15:00
101 2023-09-01 11:20:00
102 2023-09-01 10:45:00
💡 Note:

Bike 101 had two rides - the latest ended at 11:20. Bike 102 had one ride ending at 10:45. Bike 103 had one ride ending at 12:15. Results are ordered by most recent end time first (12:15, 11:20, 10:45).
Example 2 — Single rides only
Input Table:
ride_id bike_number start_time end_time
1 201 2023-09-02 08:00:00 2023-09-02 08:25:00
2 202 2023-09-02 09:00:00 2023-09-02 09:15:00
Output:
bike_number last_used_time
202 2023-09-02 09:15:00
201 2023-09-02 08:25:00
💡 Note:

Each bike had exactly one ride. Bike 202 was last used at 09:15, and bike 201 was last used at 08:25. Results ordered by most recent first.
Example 3 — Empty result
Input Table:
ride_id bike_number start_time end_time
Output:
bike_number last_used_time
💡 Note:

When there are no rides in the table, the result is empty as no bikes have been used.

Constraints

  • ride_id contains unique values
  • start_time and end_time are valid datetime values
  • start_time < end_time for all rides
  • 1 ≤ bike_number ≤ 1000

Visualization

Tap to expand
Bikes Last Time Used INPUT: Bikes Table bike_id ride_id end_time 1 101 2024-01-15 1 102 2024-01-20 2 103 2024-01-18 2 104 2024-01-22 3 105 2024-01-10 Multiple rides per bike Bike 1, 2, 3... ALGORITHM STEPS 1 GROUP BY bike_id Group all rides by bike 2 MAX(end_time) Find latest time per bike 3 ORDER BY DESC Sort by most recent first 4 SELECT Results Return bike_id, last_used SELECT bike_id, MAX(end_time) AS last_used FROM Bikes GROUP BY bike_id ORDER BY last_used DESC FINAL RESULT bike_id last_used 2 2024-01-22 1st 1 2024-01-20 2nd 3 2024-01-10 3rd OK - Sorted! Each bike appears once with its most recent ride end time Latest Key Insight: GROUP BY with MAX() aggregation finds the latest timestamp for each bike efficiently. ORDER BY DESC ensures most recently used bikes appear first. Time complexity: O(n log n) This is the optimal single-pass solution using SQL aggregation functions. TutorialsPoint - Bikes Last Time Used | Optimal Solution

Related Problems

Asked in
Uber 28 Lyft 15 Amazon 12
23.5K Views
Medium Frequency
~8 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen