Skip to content

3126. Server Utilization Time πŸ”’

DifficultyMedium

Description

Table: Servers

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| server_id      | int      |
| status_time    | datetime |
| session_status | enum     |
+----------------+----------+
(server_id, status_time, session_status) is the primary key (combination of columns with unique values) for this table.
session_status is an ENUM (category) type of ('start', 'stop').
Each row of this table contains server_id, status_time, and session_status.

Write a solution to find the total time when servers were running. The output should be rounded down to the nearest number of full days.

Return the result table in any order.

The result format is in the following example.

 

Example:

Input:

Servers table:

+-----------+---------------------+----------------+
| server_id | status_time         | session_status |
+-----------+---------------------+----------------+
| 3         | 2023-11-04 16:29:47 | start          |
| 3         | 2023-11-05 01:49:47 | stop           |
| 3         | 2023-11-25 01:37:08 | start          |
| 3         | 2023-11-25 03:50:08 | stop           |
| 1         | 2023-11-13 03:05:31 | start          |
| 1         | 2023-11-13 11:10:31 | stop           |
| 4         | 2023-11-29 15:11:17 | start          |
| 4         | 2023-11-29 15:42:17 | stop           |
| 4         | 2023-11-20 00:31:44 | start          |
| 4         | 2023-11-20 07:03:44 | stop           |
| 1         | 2023-11-20 00:27:11 | start          |
| 1         | 2023-11-20 01:41:11 | stop           |
| 3         | 2023-11-04 23:16:48 | start          |
| 3         | 2023-11-05 01:15:48 | stop           |
| 4         | 2023-11-30 15:09:18 | start          |
| 4         | 2023-11-30 20:48:18 | stop           |
| 4         | 2023-11-25 21:09:06 | start          |
| 4         | 2023-11-26 04:58:06 | stop           |
| 5         | 2023-11-16 19:42:22 | start          |
| 5         | 2023-11-16 21:08:22 | stop           |
+-----------+---------------------+----------------+

Output:

+-------------------+
| total_uptime_days |
+-------------------+
| 1                 |
+-------------------+

Explanation:

  • For server ID 3:
    • From 2023-11-04 16:29:47 to 2023-11-05 01:49:47: ~9.3 hours
    • From 2023-11-25 01:37:08 to 2023-11-25 03:50:08: ~2.2 hours
    • From 2023-11-04 23:16:48 to 2023-11-05 01:15:48: ~1.98 hours
    Total for server 3: ~13.48 hours
  • For server ID 1:
    • From 2023-11-13 03:05:31 to 2023-11-13 11:10:31: ~8 hours
    • From 2023-11-20 00:27:11 to 2023-11-20 01:41:11: ~1.23 hours
    Total for server 1: ~9.23 hours
  • For server ID 4:
    • From 2023-11-29 15:11:17 to 2023-11-29 15:42:17: ~0.52 hours
    • From 2023-11-20 00:31:44 to 2023-11-20 07:03:44: ~6.53 hours
    • From 2023-11-30 15:09:18 to 2023-11-30 20:48:18: ~5.65 hours
    • From 2023-11-25 21:09:06 to 2023-11-26 04:58:06: ~7.82 hours
    Total for server 4: ~20.52 hours
  • For server ID 5:
    • From 2023-11-16 19:42:22 to 2023-11-16 21:08:22: ~1.43 hours
    Total for server 5: ~1.43 hours
The accumulated runtime for all servers totals approximately 44.46 hours, equivalent to one full day plus some additional hours. However, since we consider only full days, the final output is rounded to 1 full day.

Solutions

Solution 1: Running-Depth Pairing

Thinking

The sample writes two \(\texttt{start}\) rows before the matching \(\texttt{stop}\) rows. Pairing each \(\texttt{start}\) with the next row measures a start-to-start gap and never reaches the outer \(\texttt{stop}\).

The listed intervals close the later \(\texttt{start}\) first: \(23{:}16{:}48\) with \(01{:}15{:}48\), and \(16{:}29{:}47\) with \(01{:}49{:}47\). That is stack order.

A running depth treats \(\texttt{start}\) as \(+1\) and \(\texttt{stop}\) as \(-1\). Each \(\texttt{start}\) matches the earliest later \(\texttt{stop}\) whose depth is one smaller. Sum those seconds and floor-divide by \(86400\).

Starts and stops on one server may nest. After a running depth is computed in time order, each \(\texttt{start}\) forms an interval with the earliest later \(\texttt{stop}\) whose depth is one less. The seconds in those intervals are added and floored to whole days.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# Write your MySQL query statement below
WITH
    T AS (
        SELECT
            server_id,
            status_time,
            session_status,
            SUM(IF(session_status = 'start', 1, -1)) OVER (
                PARTITION BY server_id
                ORDER BY status_time, session_status
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
            ) AS depth
        FROM Servers
    )
SELECT FLOOR(SUM(TIMESTAMPDIFF(SECOND, start_time, stop_time)) / 86400) AS total_uptime_days
FROM (
    SELECT
        s.status_time AS start_time,
        MIN(t.status_time) AS stop_time
    FROM T AS s
    JOIN T AS t
        ON s.server_id = t.server_id
        AND t.session_status = 'stop'
        AND t.depth = s.depth - 1
        AND t.status_time > s.status_time
    WHERE s.session_status = 'start'
    GROUP BY s.server_id, s.status_time
) AS p;

Comments