Bikes Last Time Used - Practice Coding Problems

Bikes Last Time Used - Problem

Database Easy

Imagine you're managing a bike-sharing system across the city, and you need to track when each bike was last returned to understand usage patterns and maintenance schedules.

You have a Bikes table that records every bike ride with the following structure:

Column Name	Type
ride_id	int
bike_number	int
start_time	datetime
end_time	datetime

Your task: Write a SQL query to find the last time each bike was used (i.e., the latest end_time for each bike).

Requirements:

Return the bike_number and its corresponding latest end_time
Order results by the most recently used bikes first (latest end_time descending)
Each ride_id is unique, and all datetime values are valid

Input & Output

Basic Example - Multiple Rides per Bike

$ Input: Bikes table: | ride_id | bike_number | start_time | end_time | |---------|-------------|--------------------|--------------------| | 1 | 101 | 2023-12-01 08:00:00| 2023-12-01 08:30:00| | 2 | 102 | 2023-12-01 09:00:00| 2023-12-01 09:45:00| | 3 | 101 | 2023-12-01 10:00:00| 2023-12-01 10:20:00| | 4 | 103 | 2023-12-01 11:00:00| 2023-12-01 11:30:00| | 5 | 102 | 2023-12-01 12:00:00| 2023-12-01 12:25:00|

› Output: | bike_number | last_used | |-------------|--------------------| | 102 | 2023-12-01 12:25:00| | 103 | 2023-12-01 11:30:00| | 101 | 2023-12-01 10:20:00|

💡 Note: Bike 102 was used twice (ending at 09:45 and 12:25), so its last_used is 12:25. Bike 101 was also used twice (ending at 08:30 and 10:20), so its last_used is 10:20. Results are ordered by most recent usage first.

Single Ride per Bike

$ Input: Bikes table: | ride_id | bike_number | start_time | end_time | |---------|-------------|--------------------|--------------------| | 1 | 201 | 2023-11-30 14:00:00| 2023-11-30 14:45:00| | 2 | 202 | 2023-11-30 15:00:00| 2023-11-30 15:30:00| | 3 | 203 | 2023-11-30 13:00:00| 2023-11-30 13:20:00|

› Output: | bike_number | last_used | |-------------|--------------------| | 202 | 2023-11-30 15:30:00| | 201 | 2023-11-30 14:45:00| | 203 | 2023-11-30 13:20:00|

💡 Note: Each bike has only one ride, so the last_used time is simply their respective end_time. Results are ordered by end_time descending.

Same Bike, Different Days

$ Input: Bikes table: | ride_id | bike_number | start_time | end_time | |---------|-------------|--------------------|--------------------| | 1 | 301 | 2023-11-28 10:00:00| 2023-11-28 10:30:00| | 2 | 301 | 2023-11-29 14:00:00| 2023-11-29 14:25:00| | 3 | 301 | 2023-11-27 16:00:00| 2023-11-27 16:15:00|

› Output: | bike_number | last_used | |-------------|--------------------| | 301 | 2023-11-29 14:25:00|

💡 Note: Bike 301 was used on three different days. The latest end_time is 2023-11-29 14:25:00, which becomes the last_used time for this bike.

Visualization

Tap to expand

Understanding the Visualization

Collect All Rides

Gather all bike ride records from the database

Group by Bike

Organize rides by bike_number into separate groups

Find Latest per Group

For each bike group, find the maximum end_time

Sort by Recency

Order results by latest usage first

Key Takeaway

🎯 Key Insight: GROUP BY with MAX() allows the database to efficiently find the maximum value within each group in a single pass, avoiding the need for multiple subqueries or complex joins.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

O(n) for grouping + O(n log n) for sorting by end_time

⚡ Linearithmic

Space Complexity

O(k)

Where k is the number of unique bikes (much smaller than total records)

✓ Linear Space

Constraints

1 ≤ number of rides ≤ 10⁵
1 ≤ bike_number ≤ 10⁴
start_time < end_time for all rides
All datetime values are in format 'YYYY-MM-DD HH:MM:SS'
ride_id is unique for each row

Asked in

U Uber 45 L Lyft 32 a Amazon 28 G Google 25

The optimal solution uses GROUP BY with MAX() aggregation to find the latest end_time for each bike in a single table scan. This approach leverages SQL's built-in optimization for grouping and aggregation, achieving O(n log n) time complexity due to the final sorting step.

Common Approaches

Approach	Time	Space	Notes
✓ Correlated Subquery (Brute Force)	O(n²)	O(1)	For each bike, scan all records to find the maximum end_time
GROUP BY with MAX (Optimal)	O(n log n)	O(k)	Group records by bike_number and find MAX(end_time) in a single pass

Correlated Subquery (Brute Force) — Algorithm Steps

Select distinct bike_number from the table
For each bike, execute a subquery to find MAX(end_time)
Join the results and order by end_time descending

Visualization

Tap to expand

Step-by-Step Walkthrough

Outer Query

Start with first bike_number

Inner Query

Scan entire table to find MAX end_time for this bike

Repeat

Repeat for each unique bike_number

Sort Results

Order by end_time descending

Code -

solution.c — C

// SQL solution using correlated subquery
char* query = "\
SELECT DISTINCT \
    b1.bike_number, \
    ( \
        SELECT MAX(b2.end_time) \
        FROM Bikes b2 \
        WHERE b2.bike_number = b1.bike_number \
    ) as last_used \
FROM Bikes b1 \
ORDER BY last_used DESC;\
";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each of n bikes, we scan through all n records to find the maximum

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond the result set

✓ Linear Space

Constraints

1 ≤ number of rides ≤ 10⁵
1 ≤ bike_number ≤ 10⁴
start_time < end_time for all rides
All datetime values are in format 'YYYY-MM-DD HH:MM:SS'
ride_id is unique for each row

28.5K Views

Medium Frequency

~12 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Correlated Subquery (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler