logo

Back to questions

Server Utilization Time

Hard

Amazon Web Services (AWS) is powered by fleets of servers. Senior management has requested data-driven solutions to optimize server usage.

Write a query that calculates the total time that the fleet of servers was running. The output should be in units of full days.

Assumptions:

  • Each server might start and stop several times.
  • The total time in which the server fleet is running can be calculated as the sum of each server's uptime.

Table:

Column NameType
server_idinteger
status_timetimestamp
session_statusstring

Example Input:

server_idstatus_timesession_status
108/02/2022 10:00:00start
108/04/2022 10:00:00stop
208/17/2022 10:00:00start
208/24/2022 10:00:00stop

Example Output:

total_uptime_days
21

Explanation

In the example output, the combined uptime of all the servers (from each start time to stop time) totals 21 full days.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.