Objective

Amazon Web Services (AWS) is powered by fleets of servers. Senior management has requested data-driven solutions to optimize server usage. You are given a table that logs each server's start and stop events.

Table Schema:

The server_utilization table:

Task Requirements:

• Calculate the total time that the fleet of servers was running
• Each server might start and stop several times
• The total fleet running time is the sum of each server's uptime across all sessions
• Output the result in units of full days (truncate any partial day)

Output columns: total_uptime_days

WITH sessions AS (
    SELECT
        server_id,
        status_time,
        session_status,
        LEAD(status_time) OVER (
            PARTITION BY server_id ORDER BY status_time
        ) AS stop_time
    FROM server_utilization
)
SELECT
    FLOOR(
        SUM(EXTRACT(EPOCH FROM (stop_time - status_time))) / 86400
    )::INT AS total_uptime_days
FROM sessions
WHERE session_status = 'start';

Explanation

Step 1: Pair each start with its corresponding stop

The CTE uses LEAD() partitioned by server_id and ordered by status_time to look ahead to the next event. For a 'start' row, the next event is the corresponding 'stop', captured as stop_time.

LEAD(status_time) OVER (
    PARTITION BY server_id ORDER BY status_time
) AS stop_time

Step 2: Filter for start events only

The outer query filters for session_status = 'start' rows, each carrying the paired stop_time.

WHERE session_status = 'start'

Step 3: Sum all session durations in seconds

EXTRACT(EPOCH FROM (stop_time - status_time)) converts each session's duration to seconds. SUM() aggregates all sessions across all servers.

SUM(EXTRACT(EPOCH FROM (stop_time - status_time)))

Step 4: Convert to full days

Dividing by 86400 (seconds per day) converts the total to days. FLOOR() truncates partial days to return only complete days.

FLOOR(... / 86400)::INT AS total_uptime_days