Last Server Activity

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

DoorDash

Problem

A server shows up in server_logs once for every line it writes, but we only care about the last time each one spoke. List every server with its most recent log timestamp, most recently active first, so the ones that have gone quiet sink to the bottom.

Each server's last heartbeat.

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

Worked solution and explanation

This is a last-seen heartbeat check dressed up as a log query. server_logs holds one row per line a server wrote, so every server repeats many times, and the answer wants exactly one row per server carrying its latest log_timestamp. Anyone can sort the log newest first. The skill is collapsing each server to a single last-seen time and then sorting the servers by that time. Sort the raw lines instead and the first row looks right, but the list repeats busy servers and buries the quiet ones you were asked to find.

Walking to the answer

Step 1: Settle the grain first

One row per server is the output grain, so server_name is the only key you collapse on. Everything else on a log line (its log_level, its message, its response_time_ms) is noise for this question. Put any of it in the key and a server splits into several rows.

Step 2: Take the latest time per server

MAX(log_timestamp) per server_name is the last time each server spoke. You need only the time, not the rest of that line, so there is no row to pick and nothing to tie-break.

Step 3: Sort by the collapsed value

Name the aggregate once as last_activity and sort by it descending. The freshest server leads and the silent ones fall to the bottom, which is exactly where an on-call engineer reads.

The query

SELECT server_name, MAX(log_timestamp) AS last_activity
FROM server_logs
GROUP BY server_name
ORDER BY last_activity DESC

Sorting the raw lines

SELECT server_name, log_timestamp FROM server_logs ORDER BY log_timestamp DESC returns all 200 lines. cdn-edge-us sits on top and then reappears further down, and a quiet server only shows up after you scroll past every busy one.

Collapsing first

Grouping on server_name with MAX(log_timestamp) returns 11 rows, one per server. The last row is the server that has been silent the longest.

Common follow-up questions

• How would you list only the servers that have logged nothing in the last hour? (Moves the condition onto the collapsed value: filtering servers after their last-seen time is computed.)
• What if you also need the `log_level` and `message` of each server's last line? (Now the whole row matters, so picking the latest line per server, with a tie rule, comes back.)
• A server that never logged does not appear in `server_logs` at all. How would you still report it? (Needs a registry of expected servers kept on the outer side of a join to the log.)
• How would you keep this cheap once `server_logs` reaches billions of rows? (Partition pruning, or a last-seen table maintained incrementally as lines arrive.)