Server With Most Errors

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Spotify

Problem

During an incident, the on-call SRE needs the one server carrying the most logged errors, and if two are running neck and neck, the tie goes to whichever server name comes first alphabetically. Show that server name and its error count.

One server stands out. Not in a good way.

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 an argmax with a tie hiding in it, dressed up as incident triage. Anyone can count ERROR lines per server. What separates candidates is noticing that the top count is shared: on this data nine servers sit at 4 errors each, and the only thing that decides the answer is the alphabetical rule. Write LIMIT 1 without it and the database hands back whichever tied server it happened to read first. Skip the log_level = 'ERROR' filter and you send the on-call to web-prod-02, the noisiest server overall with 20 lines, which is not the one throwing errors.

Walk it out loud

Step 1: Keep only ERROR lines

log_level holds five severities, and CRITICAL and WARN look like trouble too. The question counts errors, so filter to log_level = 'ERROR' in WHERE before anything is counted. Counting every line answers 'busiest server' instead, which on this data is web-prod-02.

Step 2: Count per server_name

GROUP BY server_name with COUNT(*) gives each server its own tally. Every row is one log line and log_id never repeats, so counting rows is counting errors.

Step 3: Sort by the count, then by the name

ORDER BY error_count DESC puts the biggest tallies first, and server_name as the second key decides among the tied ones. It ascends by default, which is exactly 'first alphabetically'.

Step 4: Cut to one row

LIMIT 1 keeps the winner. Because the name key settles every tie among the nine servers at 4, the cut is deterministic: api-prod-01, every run.

The solution

SELECT server_name, COUNT(*) AS error_count
FROM server_logs
WHERE log_level = 'ERROR'
GROUP BY server_name
ORDER BY error_count DESC, server_name
LIMIT 1

Max of the counts

MAX(error_count) over the per-server tallies returns 4, the right number with no server attached. Putting server_name beside it without grouping is either rejected or pairs the max with an arbitrary name.

Sort and cut

Sorting the grouped rows and keeping the first carries server_name along with its own count, and the name key makes the pick repeatable.

Common follow-up questions

• The incident started an hour ago. How do you restrict to recent logs, and what does that buy you on a table partitioned by `log_timestamp`? (Tests adding a time window and knowing that it enables partition pruning.)
• The SRE now wants every server tied for the top count, not just one. What changes? (Tests replacing a top-one cut with a selection that keeps ties.)
• Some writers log the level as 'error' or ' ERROR'. How do you count them all exactly once? (Tests normalizing a dirty categorical before filtering on it.)
• How would you return the top server per day instead of overall? (Tests scoping the top-one pick to each group.)