The Ones Worth Paging

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

Google

Problem

The SRE team is building a triage chart covering the three severity levels that call for action: CRITICAL, ERROR, and WARN. List each of these levels in alphabetical order alongside the total number of log entries recorded for it.

Three levels wake someone up at night. Tally each one.

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

This is a filtered bucket count dressed up as an SRE triage chart. The skill being probed is restricting rows to the three levels that call for action before you count them. Anyone can write the group-and-count; the tell is whether you read 'the three severity levels' as a hard filter or as scene-setting. Skip the filter and server_logs hands back five rows, with 'INFO' and 'DEBUG' sitting on a chart meant for paging, and the answer fails on row count before a single value is compared. The second thing the interviewer watches: do you ask about a time window before you agree to scan every partition of a 200M-row table.

Break down the requirements

Step 1: Restrict to the three actionable levels

Use WHERE log_level IN ('CRITICAL', 'ERROR', 'WARN'). It runs before the grouping, so 'INFO' and 'DEBUG' never become groups at all. IN reads better than a chain of ORs, and the planner treats them the same.

Step 2: Group on log_level

One row per level. GROUP BY log_level is the only correct grain. No DISTINCT, no window.

Step 3: Count rows per group

COUNT(*) aliased to total_count. COUNT(log_level) gives the same answer here because the column is never null inside the filter, but COUNT(*) says what you mean: every entry.

Step 4: Order deterministically

ORDER BY log_level returns the levels in alphabetical order: CRITICAL, ERROR, WARN. Without it, partition-parallel scans return groups in arbitrary order.

The solution

SELECT log_level, COUNT(*) AS total_count
FROM server_logs
WHERE log_level IN ('CRITICAL', 'ERROR', 'WARN')
GROUP BY log_level
ORDER BY log_level

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you return a zero count for a level that has no rows in the window? (Probes whether you reach for a `VALUES` table left-joined to the aggregate, since `GROUP BY` cannot invent missing groups.)
• Add a column for the share of total log volume each level represents. (Tests window aggregates: `COUNT(*) * 1.0 / SUM(COUNT(*)) OVER ()` after the `GROUP BY`.)
• Bucket by hour as well as level for the last 24 hours. (Forces a partition-pruning predicate on `log_timestamp` plus `date_trunc('hour', log_timestamp)` in the `GROUP BY`.)
• How would the plan change if `log_level` were stored as TEXT versus an ENUM type? (Checks understanding of dictionary encoding, comparison cost, and index selectivity on low-cardinality columns.)