Log Entries by Level

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

Yelp

Problem

The SRE dashboard needs a count of server log entries at each log level, alphabetically by level.

Info, warn, error, fatal. The breakdown matters.

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 grain question wearing a dashboard costume. The whole job is deciding that the answer has one row per log_level, and that each row counts every line in server_logs, not something about the lines. Nobody fumbles the syntax. The slip is in the count: reach for COUNT(DISTINCT message) because 'entries' sounds like unique alerts, and every level reads 19 instead of 40, because the same message repeats inside a level. Lose the per-level grain entirely and you hand the dashboard a single 200 it cannot plot.

Walk the grain first

Step 1: Name the grain before the query

Say it out loud: one row per log level. log_level holds five values on this data ('CRITICAL', 'DEBUG', 'ERROR', 'INFO', 'WARN'), so the answer is five rows, and GROUP BY log_level is what produces exactly that shape.

Step 2: Count lines, not messages

COUNT(*) counts every row in the group. A repeated alert is still a separate log line, and message repeats inside every level, so COUNT(DISTINCT message) quietly reports 19 where the truth is 40.

Step 3: Sort by the name, not the number

The listing is alphabetical, so ORDER BY log_level ASC. Every level happens to hold 40 lines here, so sorting by entry_count would hand back the five rows in whatever order the engine pleases.

SELECT log_level, COUNT(*) AS entry_count
FROM server_logs
GROUP BY log_level
ORDER BY log_level ASC

Counts unique messages

COUNT(DISTINCT message) collapses repeated alerts, so each level shows 19: the number of different texts, not the volume the dashboard is tracking.

Counts every line

COUNT(*) counts each row of server_logs once, so each level shows its true 40 lines, repeats included.

Common follow-up questions

• How would you count only the last seven days, and what does partitioning on `log_timestamp` buy you there? (Tests partition pruning on a time-partitioned log table.)
• Severity order is CRITICAL, ERROR, WARN, INFO, DEBUG. How would you list the levels that way instead of alphabetically? (Tests mapping a categorical value to a sort key.)
• How would you add each level's share of all log lines next to `entry_count`? (Tests combining a per-group count with a whole-table total.)
• What changes if `log_level` arrives in mixed case, such as 'warn' and 'WARN'? (Tests normalizing a grouping key before counting.)