Bike Fleet Last Usage Timestamp

Easy

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: bike_number 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 1

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 2

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 3

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.

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

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 |

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 |

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