Log Levels

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

Problem

For the weekly incident review, the SRE team is profiling the fast path: log entries that returned in under 300 ms. For each severity level they want the number of these fast entries and their average response time, with the levels logging the most fast entries listed first. Leave out any severity level with fewer than five fast entries.

Severity breakdown with response times.

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 problem is really testing

Beneath the incident-digest costume this is a two-stage filter. First you narrow the rows to the fast path with WHERE response_time_ms < 300, aggregate per log_level, and then you filter the GROUPS by how many fast entries each one logged. The reflex that sinks people is reaching for WHERE to express the at-least-five cutoff, but a row does not know how many siblings share its log_level; that count only exists after GROUP BY, which is exactly what HAVING is for. Put the cutoff in the wrong place and you either cannot write it at all or you drop it and silently keep a level like 'INFO' that only cleared the bar four times.

Build it in stages

Step 1: Narrow to the fast path

Keep only the fast entries with WHERE response_time_ms < 300. This also quietly drops rows where response_time_ms is NULL, since NULL < 300 evaluates to unknown rather than true, so nulls never sneak into the counts.

Step 2: Aggregate per severity level

GROUP BY log_level collapses the fast rows into one row per severity, and per group you emit COUNT(*) AS log_count and AVG(response_time_ms) AS avg_response_ms. AVG skips nulls on its own, though the WHERE has already removed them here.

Step 3: Drop the low-volume levels

HAVING COUNT(*) >= 5 runs after the grouping and removes any level with fewer than five fast entries. On this data that drops 'INFO', which logged only four.

Step 4: Put the busiest levels first

ORDER BY log_count DESC puts the busiest levels first. Here 'CRITICAL', 'DEBUG' and 'ERROR' all sit at six, and the question says nothing about which of them leads, so any order among them is a correct answer. A tiebreaker only earns its place when a LIMIT cuts through a tie and someone has to decide who makes the list.

The solution

SELECT log_level,
       COUNT(*) AS log_count,
       AVG(response_time_ms) AS avg_response_ms
FROM server_logs
WHERE response_time_ms < 300
GROUP BY log_level
HAVING COUNT(*) >= 5
ORDER BY log_count DESC

Common follow-up questions

• What changes if you also need each level's average across all entries, not just the fast ones? (Tests whether they can separate a filtered aggregate from a full one, likely with conditional aggregation.)
• How would you report the slow path (`response_time_ms >= 300`) in the same query alongside the fast counts? (Tests conditional aggregation with `CASE` inside `COUNT` and `AVG`.)
• If `response_time_ms` were frequently `NULL`, how would you report how many entries each level is missing? (Tests explicit null handling versus relying on aggregate skip behavior.)