Server Utilization Time - Practice Coding Problems

Server Utilization Time - Problem

Database Medium

You are working as a DevOps engineer for a cloud hosting company that needs to calculate billing based on server usage. Your task is to analyze server activity logs to determine the total time servers were actively running.

Given a database table Servers that logs every time a server starts or stops, you need to calculate the total uptime across all servers and convert it to full days (rounded down).

Key Requirements:

Each server can have multiple start/stop sessions
Sessions are properly paired (every start has a corresponding stop)
Calculate total runtime across ALL servers
Return result as full days only (floor the result)

Example: If servers ran for 25.7 hours total, that equals 1.07 days, which rounds down to 1 full day.

Input & Output

example_1.sql — Basic Server Sessions

$ Input: Servers table with multiple server sessions: server_id=1: start at 03:05:31, stop at 11:10:31 (8 hours) server_id=3: start at 16:29:47, stop at 01:49:47 next day (9.3 hours)

› Output: total_uptime_days: 0

💡 Note: Total runtime is 17.3 hours, which equals 0.72 days. Since we floor the result, it becomes 0 full days.

example_2.sql — Extended Sessions

$ Input: Multiple servers with longer sessions: server_id=1: 8 + 1.2 = 9.2 hours server_id=3: 9.3 + 2.2 + 2.0 = 13.5 hours server_id=4: 6.5 + 0.5 + 5.7 + 7.8 = 20.5 hours server_id=5: 1.4 hours

› Output: total_uptime_days: 1

💡 Note: Total runtime is 44.6 hours = 1.86 days. Floored to 1 full day.

example_3.sql — Edge Case - Short Sessions

$ Input: Multiple very short server sessions: server_id=1: 0.5 hours server_id=2: 1.0 hours server_id=3: 0.3 hours

› Output: total_uptime_days: 0

💡 Note: Total runtime is 1.8 hours = 0.075 days. Floored to 0 full days.

Constraints

1 ≤ number of server records ≤ 10⁴
Each server has properly paired start/stop events
status_time is a valid datetime format
session_status is either 'start' or 'stop'
No overlapping sessions for the same server
All timestamps are in chronological order per server

Visualization

Tap to expand

Understanding the Visualization

Group by Server

Separate all events by server_id, like sorting parking tickets by license plate

Sequence Events

Number start and stop events chronologically for each server

Pair Sessions

Match start event #1 with stop event #1, start #2 with stop #2, etc.

Calculate Duration

Compute time difference for each session pair

Aggregate & Convert

Sum all durations and convert to full days using FLOOR function

Key Takeaway

🎯 Key Insight: Window functions enable efficient session pairing by partitioning data by server and sequencing events chronologically, avoiding expensive nested queries while maintaining optimal performance.

Asked in

a Amazon 85 G Google 72 ⊞ Microsoft 58 f Meta 41

The optimal solution uses SQL window functions to efficiently pair start-stop events. Key insight: partition data by server_id, use ROW_NUMBER() to sequence events, then JOIN matching pairs. This avoids nested queries and achieves O(n log n) performance, making it suitable for large-scale server monitoring systems.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach (Optimal)	O(n log n)	O(n)	Use window functions to pair consecutive start-stop events efficiently
Brute Force (Nested Query)	O(n²)	O(1)	For each start event, find the next stop event using nested queries

Window Function Approach (Optimal) — Algorithm Steps

Use ROW_NUMBER() window function partitioned by server_id and session_status
Create separate result sets for start and stop events with sequential numbering
JOIN start and stop events using server_id and the row number
Calculate time differences using TIMESTAMPDIFF function
Sum all durations and convert to days using FLOOR function

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by Server

Group records by server_id using window functions

Number Sequences

Assign row numbers to start and stop events separately

Join by Sequence

Match start and stop events with same row number

Calculate & Sum

Compute durations and aggregate total uptime

Code -

solution.c — C

// Optimal SQL solution using window functions
#include <stdio.h>

int main() {
    char query[] = 
    "SELECT FLOOR(SUM(TIMESTAMPDIFF(SECOND, start_time, stop_time)) / 86400) as total_uptime_days\n"
    "FROM (\n"
    "    SELECT \n"
    "        starts.server_id,\n"
    "        starts.status_time as start_time,\n"
    "        stops.status_time as stop_time\n"
    "    FROM (\n"
    "        SELECT server_id, status_time,\n"
    "               ROW_NUMBER() OVER (PARTITION BY server_id ORDER BY status_time) as rn\n"
    "        FROM Servers \n"
    "        WHERE session_status = 'start'\n"
    "    ) starts\n"
    "    JOIN (\n"
    "        SELECT server_id, status_time,\n"
    "               ROW_NUMBER() OVER (PARTITION BY server_id ORDER BY status_time) as rn\n"
    "        FROM Servers \n"
    "        WHERE session_status = 'stop'\n"
    "    ) stops\n"
    "    ON starts.server_id = stops.server_id AND starts.rn = stops.rn\n"
    ") as sessions;";
    
    printf("%s\n", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting operations in window functions dominate, with O(n) for the actual pairing and calculation

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage needed for window function partitioning and intermediate results

⚡ Linearithmic Space

68.2K Views

High Frequency

~15 min Avg. Time

2.8K 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

Constraints

Visualization

Related Problems

Common Approaches

Window Function Approach (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler