Server Utilization Time - Solution & Explanation

Problem Overview: Each row represents a server event (start or stop) with a timestamp. The task is to compute the total time each server was actively running by pairing every start event with its corresponding stop event and summing the duration.

Approach 1: Self Join Event Pairing (O(n log n) time, O(n) space)

One straightforward SQL strategy pairs start rows with the next stop row for the same server using a self join. You join the table with itself on server_id and ensure the stop timestamp is greater than the start timestamp. Then filter to keep the nearest stop event and compute stop_time - start_time. Finally, aggregate the durations with SUM() grouped by server. This works but requires extra filtering logic to ensure the correct pairing and usually involves sorting or subqueries.

Because every event may compare with multiple later rows, the query becomes heavier on large logs. The logic is also harder to maintain when event ordering matters. This method still works in relational systems without strong window function support.

Approach 2: Window Function Pairing with LEAD (O(n log n) time, O(n) space)

The clean solution uses SQL window functions. Partition the events by server_id and order them by timestamp. Apply LEAD(timestamp) to fetch the timestamp of the next event for the same server. When the current row is a start event, the next row should be the corresponding stop. The difference between these timestamps gives the utilization duration for that session.

After computing these session durations, aggregate them with SUM() per server. Window functions handle event pairing in a single pass over each server's ordered events, making the query concise and reliable. This approach is the standard pattern for event log problems involving start/stop intervals.

This technique relies on ordered partitions and sequential access, which is exactly what SQL window functions are designed for. Problems involving time intervals, sessionization, or log processing frequently use LEAD or LAG from window functions inside relational databases. Efficient querying and aggregation like this are core patterns in database interview questions.

Recommended for interviews: The window function solution is what interviewers typically expect. It shows you understand event sequencing and modern SQL features. Explaining the self-join approach first demonstrates reasoning about pairing events, but implementing it with LEAD shows stronger SQL fluency and cleaner query design.