The Loudest Machines

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

ESPN

Problem

An SRE sizing next quarter's log storage wants to see how 2026's load spread across the servers writing to server_logs. Report how many log entries each server wrote that year, busiest first.

Every server keeps a diary. Find the ones that never stop writing.

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

What this really is

Under the capacity-planning story, this is a filtered count per group: carve one calendar year out of a text timestamp, then count every row each server wrote. Everyone writes the GROUP BY. Candidates split on two questions: which rows are in scope, and what gets counted. server_logs spans five years and only part of it falls in 2026, so a count with no year filter inflates every server. About a third of the rows have no response_time_ms, so COUNT(response_time_ms) quietly drops untimed lines that still take up storage.

Step 1: Scope to the year

log_timestamp is text in 'YYYY-MM-DD HH:MM:SS' form. STRFTIME('%Y', log_timestamp) pulls out the four-digit year as text, which you compare against the string '2026'. Comparing the raw column to '2026' matches nothing, because no full timestamp equals a bare year.

Step 2: Count every surviving row

Use GROUP BY server_name with COUNT(*). Every surviving row is one stored line, whatever its log_level and whether or not it was timed, so the row count is the volume. Do not add DISTINCT or a level filter.

Step 3: Busiest first

Sort by log_count descending. Servers tied on volume (two sit at 11, two at 10) may come back in either order. The grader checks that counts never rise down the list. It does not check which tied server is printed first, so no tiebreaker is owed.

SELECT server_name, COUNT(*) AS log_count
FROM server_logs
WHERE STRFTIME('%Y', log_timestamp) = '2026'
GROUP BY server_name
ORDER BY log_count DESC

Common follow-up questions

• The team now wants this broken out by month as well as by server. How does the query change? (Tests adding a derived month column as a second grouping key without breaking the grain.)
• Some servers were decommissioned mid-year and should be dropped entirely. Where would you apply that exclusion, and why there? (Tests filter placement and the difference between filtering rows and filtering groups.)
• How would you return only the top five servers by volume, and what happens if the fifth and sixth are tied? (Tests `LIMIT` after an `ORDER BY` and the need for a tie rule at the cutoff.)