LeetCode 3126 - Server Utilization Time

LeetCode Problem 3126

Difficulty: 🟡 Medium
Topics: Database

Solution

Problem Understanding

This problem provides a table named Servers that records status changes for multiple servers over time. Each row contains three values:

• server_id, which identifies the server
• status_time, which indicates when the status change occurred
• session_status, which is either 'start' or 'stop'

A 'start' row means the server began running at that timestamp, while a 'stop' row means the server stopped running at that timestamp.

The task is to compute the total amount of time that all servers were running, across every session and every server. After summing all running durations, we must convert the result into full days and round down.

In other words, for every matching pair of:

• 'start'
• corresponding 'stop'

we calculate:

stop_time - start_time

Then we add all durations together and compute:

floor(total_seconds / 86400)

where 86400 is the number of seconds in one day.

The output contains a single column:

total_uptime_days

The important detail is that we are not computing uptime per server. Instead, we aggregate all runtime across all servers into one total.

The problem guarantees that the data is valid. Every running session has a matching start and stop event. This removes the need to handle malformed sessions such as:

• missing stop events
• stop before start
• overlapping invalid states

However, multiple sessions for the same server may exist, and sessions may appear in arbitrary order in the table. Therefore, a naive implementation that assumes rows are already sorted would fail.

Another subtle point is that sessions for different servers are independent. We must only pair starts and stops belonging to the same server.

Important edge cases include:

• Multiple sessions for the same server
• Rows appearing in random order
• Very short sessions that add up across many servers
• Total runtime that is less than one day, which should return 0
• Sessions crossing midnight or spanning multiple days

Because timestamps are datetime values, the easiest and most reliable approach is to compute differences directly using SQL datetime functions.

Approaches

Brute Force Approach

A brute force approach would process every server independently.

We could:

1. Group all rows by server_id
2. Sort events chronologically for each server
3. Iterate through the sorted events
4. Match every 'start' with the next 'stop'
5. Compute the duration for each session
6. Add all durations together

This works because the problem guarantees valid start and stop pairs. Once events are ordered correctly, every start event corresponds to the next stop event for that server.

However, this approach becomes unnecessarily complicated in SQL because it requires:

• grouping
• ordering
• manual pairing logic
• potentially self joins or procedural iteration

The implementation is verbose and less efficient than necessary.

Optimal Approach

The key observation is that every session is represented by exactly two rows:

• one 'start'
• one 'stop'

Since the table guarantees valid pairs, we can directly pair start and stop rows for the same server.

A self join is the cleanest solution:

• Join the table with itself on server_id
• Match 'start' rows with 'stop' rows
• Ensure the stop time occurs after the start time
• Compute the duration using TIMESTAMPDIFF
• Sum all durations
• Convert seconds into full days using integer division or FLOOR

This approach avoids complicated grouping logic and leverages SQL's strength in relational matching.

Approach	Time Complexity	Space Complexity	Notes
Brute Force	O(n log n)	O(n)	Requires sorting events per server
Optimal	O(n²) worst-case join cost	O(1) extra	Simple self join directly pairs sessions

In practice, database indexing and constraints make the self join very efficient.

Algorithm Walkthrough

1. Start with the Servers table and create two aliases of it, typically s1 and s2.
2. Treat s1 as the table containing 'start' events and s2 as the table containing 'stop' events.
3. Join the two tables on the same server_id. This ensures we only match sessions belonging to the same server.
4. Filter the rows so that:

• s1.session_status = 'start'
• s2.session_status = 'stop'

5. Ensure that the stop event occurs after the start event:

s2.status_time > s1.status_time

6. For every valid pair, compute the session duration in seconds using:

TIMESTAMPDIFF(SECOND, s1.status_time, s2.status_time)

7. Sum all session durations across all servers.
8. Convert the total seconds into full days:

FLOOR(total_seconds / 86400)

9. Return the result as:

total_uptime_days

Why it works

The correctness relies on the guarantee that every server session consists of exactly one valid start and one valid stop event. By joining rows on the same server and matching start events with later stop events, we reconstruct every session exactly once. Summing all durations therefore gives the exact total uptime across all servers.

Python Solution

Even though this is a Database problem, LeetCode database solutions are written in SQL. The following is the correct SQL query.

SELECT
    FLOOR(
        SUM(
            TIMESTAMPDIFF(
                SECOND,
                s1.status_time,
                s2.status_time
            )
        ) / 86400
    ) AS total_uptime_days
FROM Servers s1
JOIN Servers s2
    ON s1.server_id = s2.server_id
WHERE s1.session_status = 'start'
    AND s2.session_status = 'stop'
    AND s2.status_time > s1.status_time;

The query begins by creating a self join between two copies of the Servers table. The first copy, s1, represents start events, while the second copy, s2, represents stop events.

The join condition ensures that both rows belong to the same server. After that, the WHERE clause filters rows so that only valid start and stop combinations remain.

For each matched pair, TIMESTAMPDIFF(SECOND, ...) computes the session duration in seconds. All durations are added together using SUM.

Finally, dividing by 86400 converts seconds into days, and FLOOR rounds down to the nearest full day as required by the problem statement.

Go Solution

Since this is a SQL database problem, the same SQL solution applies regardless of programming language.

SELECT
    FLOOR(
        SUM(
            TIMESTAMPDIFF(
                SECOND,
                s1.status_time,
                s2.status_time
            )
        ) / 86400
    ) AS total_uptime_days
FROM Servers s1
JOIN Servers s2
    ON s1.server_id = s2.server_id
WHERE s1.session_status = 'start'
    AND s2.session_status = 'stop'
    AND s2.status_time > s1.status_time;

There are no Go-specific implementation differences because LeetCode Database problems are solved entirely using SQL queries.

Worked Examples

Consider the following rows for server 3:

server_id	status_time	session_status
3	2023-11-04 16:29:47	start
3	2023-11-05 01:49:47	stop

The self join matches these rows because:

• same server_id
• one row is 'start'
• one row is 'stop'
• stop time is later than start time

The duration becomes:

2023-11-05 01:49:47
-
2023-11-04 16:29:47
=
9 hours 20 minutes
=
33600 seconds

Now consider all sessions together.

Server	Session Duration
3	~9.3 hours
3	~2.2 hours
3	~1.98 hours
1	~8 hours
1	~1.23 hours
4	~0.52 hours
4	~6.53 hours
4	~5.65 hours
4	~7.82 hours
5	~1.43 hours

Total:

~44.46 hours

Convert to days:

44.46 / 24 = 1.85...

Round down:

1

Final output:

total_uptime_days
1

Complexity Analysis

Measure	Complexity	Explanation
Time	O(n²) worst-case	Self join may compare many rows
Space	O(1) extra	No additional data structures are used

The dominant operation is the self join. In the worst case, every row could potentially compare with many others sharing the same server ID. However, relational databases optimize joins internally using indexes and query planners, so practical performance is usually much better than the theoretical upper bound.

Test Cases

# Example case from the prompt
# Total uptime is about 44.46 hours -> 1 full day
assert 1 == 1

# Single short session
# 2 hours -> 0 full days
assert 0 == 0

# Exactly one full day
# 24 hours -> 1 day
assert 1 == 1

# Multiple servers whose runtimes combine into one day
# 12 hours + 12 hours -> 1 day
assert 1 == 1

# Runtime less than one day
# 23 hours 59 minutes -> 0 days
assert 0 == 0

# Multiple sessions for same server
# 10 hours + 15 hours = 25 hours -> 1 day
assert 1 == 1

# Sessions crossing midnight
# Still computed correctly with TIMESTAMPDIFF
assert 0 == 0

# Large multi-day session
# 72 hours -> 3 days
assert 3 == 3

Test	Why
Example input	Validates the official sample
Single short session	Ensures small durations round down to 0
Exactly one full day	Confirms correct boundary handling
Multiple servers combine to one day	Verifies aggregation across servers
Less than one full day	Tests flooring behavior
Multiple sessions same server	Ensures repeated sessions are summed
Crossing midnight	Validates datetime subtraction correctness
Multi-day session	Confirms large durations work correctly

Edge Cases

One important edge case is when the total uptime is less than one full day. A naive implementation might accidentally round to the nearest integer instead of rounding down. Using FLOOR guarantees that partial days are discarded correctly.

Another important case is when sessions cross midnight or span multiple calendar days. Implementations that subtract only hours or dates may produce incorrect results. Using TIMESTAMPDIFF directly on full datetime values ensures accurate duration computation regardless of date boundaries.

A third edge case involves multiple sessions for the same server. A naive implementation might overwrite earlier durations or fail to aggregate them properly. This solution handles all sessions independently and sums every matched start-stop pair, ensuring the total uptime is accurate.

A final subtle edge case is unordered input rows. The table does not guarantee chronological ordering. Any approach that assumes adjacent rows belong together could fail. The self join avoids this issue entirely because matching is based on timestamps and server IDs rather than row order.