Server Fleet Full-Day Uptime

Medium

A platform engineering team maintains status logs that mark when each server session begins and ends. Table: Servers server_id: integer server identifier status_time: event timestamp in format YYYY-MM-DD HH:MM:SS session_status: event type, either "start" or "stop" Task: Compute the aggregate runtime across all servers and return the number of complete uptime days. Pairing rule (critical): For each server independently: Sort all start events by status_time ascending and assign indices 1..k. Sort all stop events by status_time ascending and assign indices 1..k. Pair start index i with stop index i. Session runtime is (stop_time - start_time) in seconds. After summing all paired session runtimes from every server: total_uptime_days = floor(total_runtime_seconds / 86400) Output requirements: Return exactly one column: total_uptime_days Return exactly one row Important note: Input rows can be in arbitrary order. There may be overlapping sessions for the same server, so do not assume strict start/stop alternation in the raw row sequence. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Servers:
| server_id | status_time          | session_status |
|-----------|----------------------|----------------|
| 1         | 2024-03-01 08:00:00  | start          |
| 1         | 2024-03-01 12:10:00  | start          |
| 1         | 2024-03-01 14:09:00  | stop           |
| 1         | 2024-03-01 17:20:00  | stop           |
| 2         | 2024-03-02 00:00:00  | start          |
| 2         | 2024-03-02 08:00:00  | stop           |
| 2         | 2024-03-04 09:30:00  | start          |
| 2         | 2024-03-04 10:44:00  | stop           |
| 3         | 2024-03-06 01:00:00  | start          |
| 3         | 2024-03-06 07:32:00  | stop           |

Output

[
  {"total_uptime_days": 1}
]

Explanation

All paired session seconds across the three servers sum to more than one day but less than two, so the final answer is 1.

Example 2

Input

Servers:
| server_id | status_time          | session_status |
|-----------|----------------------|----------------|
| 9         | 2024-05-01 00:00:00  | start          |
| 9         | 2024-05-01 06:00:00  | stop           |
| 9         | 2024-05-01 10:00:00  | start          |
| 9         | 2024-05-01 18:00:00  | stop           |
| 9         | 2024-05-02 03:00:00  | start          |
| 9         | 2024-05-02 11:00:00  | stop           |

Output

[
  {"total_uptime_days": 0}
]

Explanation

Total runtime is 22 hours, which is below one full day.

Example 3

Input

Servers:
| server_id | status_time          | session_status |
|-----------|----------------------|----------------|
| 30        | 2024-01-10 00:00:00  | start          |
| 30        | 2024-01-10 12:00:00  | stop           |
| 30        | 2024-01-11 00:00:00  | start          |
| 30        | 2024-01-12 00:00:00  | stop           |
| 40        | 2024-01-15 00:00:00  | start          |
| 40        | 2024-01-16 20:00:00  | stop           |

Output

[
    {"total_uptime_days": 3}
]

Explanation

Combined runtime is 12h + 24h + 44h = 80h. Since 80 hours contains 3 complete 24-hour blocks, the output is 3.

Constraints

1 <= number of rows in Servers <= 300000
1 <= server_id <= 2000000000
session_status is either 'start' or 'stop'
status_time uses format YYYY-MM-DD HH:MM:SS
For each server, number of start rows equals number of stop rows
For each server, after sorting starts and stops independently, stop_i >= start_i for every pair index i
Input order is arbitrary and not guaranteed to reflect pairing
Output must contain exactly one row and one column: total_uptime_days
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with column [total_uptime_days]

Servers: | server_id | status_time | session_status | |-----------|----------------------|----------------| | 1 | 2024-03-01 08:00:00 | start | | 1 | 2024-03-01 12:10:00 | start | | 1 | 2024-03-01 14:09:00 | stop | | 1 | 2024-03-01 17:20:00 | stop | | 2 | 2024-03-02 00:00:00 | start | | 2 | 2024-03-02 08:00:00 | stop | | 2 | 2024-03-04 09:30:00 | start | | 2 | 2024-03-04 10:44:00 | stop | | 3 | 2024-03-06 01:00:00 | start | | 3 | 2024-03-06 07:32:00 | stop |

Servers: | server_id | status_time | session_status | |-----------|----------------------|----------------| | 9 | 2024-05-01 00:00:00 | start | | 9 | 2024-05-01 06:00:00 | stop | | 9 | 2024-05-01 10:00:00 | start | | 9 | 2024-05-01 18:00:00 | stop | | 9 | 2024-05-02 03:00:00 | start | | 9 | 2024-05-02 11:00:00 | stop |

Servers: | server_id | status_time | session_status | |-----------|----------------------|----------------| | 30 | 2024-01-10 00:00:00 | start | | 30 | 2024-01-10 12:00:00 | stop | | 30 | 2024-01-11 00:00:00 | start | | 30 | 2024-01-12 00:00:00 | stop | | 40 | 2024-01-15 00:00:00 | start | | 40 | 2024-01-16 20:00:00 | stop |