Bike Fleet Last Usage Timestamp

EASY10 pts

A micromobility platform tracks every completed bike trip. Operations teams need a compact "last seen active" report for the entire fleet.

Table: Bikes

ride_id: unique ride event identifier
bike_number: bike asset code
start_time: ride start timestamp
end_time: ride end timestamp

Task: For each bike_number, return the most recent end_time across all rides for that bike.

Output requirements:

Return exactly two columns:
1. bike_number
2. end_time
Return one row per distinct bike_number.
Sort final rows by end_time in descending order.
If multiple bikes share the same end_time, sort those ties by bike_number in ascending order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Bikes:
| 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":"W00576","end_time":"2012-03-28 02:50:00"},
  {"bike_number":"W00455","end_time":"2012-03-26 17:40:00"},
  {"bike_number":"W00300","end_time":"2012-03-25 10:50:00"}
]

Explanation

Each bike keeps only its maximum end_time. Results are then ordered from newest usage to oldest.

Example

Input

Bikes:
| ride_id | bike_number | start_time           | end_time             |
|---------|-------------|----------------------|----------------------|
| 10      | B-ALPHA     | 2024-01-01 09:00:00  | 2024-01-01 09:20:00  |
| 11      | B-BRAVO     | 2024-01-02 10:00:00  | 2024-01-03 08:00:00  |
| 12      | B-ALPHA     | 2024-01-03 07:00:00  | 2024-01-03 08:00:00  |
| 13      | B-CHARLIE   | 2024-01-03 06:00:00  | 2024-01-03 07:59:59  |

Output

[
  {"bike_number":"B-ALPHA","end_time":"2024-01-03 08:00:00"},
  {"bike_number":"B-BRAVO","end_time":"2024-01-03 08:00:00"},
  {"bike_number":"B-CHARLIE","end_time":"2024-01-03 07:59:59"}
]

Explanation

B-ALPHA and B-BRAVO tie on latest end_time, so tie-breaking by bike_number ascending places B-ALPHA first.

Example

Input

Bikes:
| ride_id | bike_number | start_time           | end_time             |
|---------|-------------|----------------------|----------------------|
| 100     | Z9          | 2025-05-01 08:00:00  | 2025-05-01 08:45:00  |

Output

[
  {"bike_number":"Z9","end_time":"2025-05-01 08:45:00"}
]

Explanation

With one ride for one bike, that ride's end_time is the latest by definition.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in Bikes <= 200000
ride_id values are unique
1 <= ride_id <= 1000000000
bike_number is a non-empty string identifier
start_time and end_time are valid timestamps in YYYY-MM-DD HH:MM:SS format
start_time <= end_time for every row
Return exactly two columns: bike_number, end_time
Return one row per distinct bike_number
Sort by end_time DESC, then bike_number ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: bike_number, end_time

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Bikes =

[{"ride_id":1,"bike_number":"W00576","start_time":"2012-03-25 11:30:00","end_time":"2012-03-25 12:40:00"},{"ride_id":2,"bike_number":"W00300","start_time":"2012-03-25 10:30:00","end_time":"2012-03-25 10:50:00"},{"ride_id":3,"bike_number":"W00455","start_time":"2012-03-26 14:30:00","end_time":"2012-03-26 17:40:00"},{"ride_id":4,"bike_number":"W00455","start_time":"2012-03-25 12:30:00","end_time":"2012-03-25 13:40:00"},{"ride_id":5,"bike_number":"W00576","start_time":"2012-03-25 08:10:00","end_time":"2012-03-25 09:10:00"},{"ride_id":6,"bike_number":"W00576","start_time":"2012-03-28 02:30:00","end_time":"2012-03-28 02:50:00"}]

Bike Fleet Last Usage Timestamp

Hints

Bike Fleet Last Usage Timestamp

Hints