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 - Solution OverviewStep 1: Input Ridesbike_numberend_time10109:3010210:4510111:20GROUP BYMAXStep 2: Group & Find MAX101:09:30, 11:20→ 11:20102:10:45→ 10:45ORDER BYDESCStep 3: Final Resultbike_numberlast_used_time10111:2010210:45Most recently used bikes appear first
Understanding the Visualization
1
Input
Bikes table with ride records
2
Group & Aggregate
GROUP BY bike_number, MAX(end_time)
3
Sort
ORDER BY latest times first
Key Takeaway
🎯 Key Insight: Use GROUP BY with aggregate functions to find summary statistics per group

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