# The Ones Worth Paging > Three levels wake someone up at night. Tally each one. Canonical URL: 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. ## Tables ### server_logs - log_id INTEGER - server_name TEXT - log_level TEXT - message TEXT - response_time_ms INTEGER - log_timestamp TEXT Sample rows (10 of 200): | log_id | server_name | log_level | message | response_time_ms | log_timestamp | | --- | --- | --- | --- | --- | --- | | 137 | web-prod-02 | WARN | Connection timeout after 30s | 23 | 2026-02-02 01:07:13 | | 174 | api-prod-01 | ERROR | Database query slow: 2.5s | NULL | 2026-03-03 02:14:26 | | 211 | api-prod-02 | DEBUG | Cache miss for key user_123 | 49 | 2026-04-04 03:21:39 | | 248 | db-primary | CRITICAL | Memory usage at 85% | 0 | 2026-05-05 04:28:52 | | 285 | db-replica-01 | INFO | SSL certificate expires in 7 days | NULL | 2026-06-06 05:35:05 | | 322 | cache-01 | WARN | Rate limit exceeded for IP 192.168.1.x | 88 | 2026-07-07 06:42:18 | | 359 | worker-01 | ERROR | Disk space low: 10% remaining | 101 | 2026-08-08 07:49:31 | | 396 | cdn-edge-us | DEBUG | New deployment v2.3.1 started | NULL | 2026-09-09 08:56:44 | | 433 | cdn-edge-eu | CRITICAL | Health check passed | 127 | 2026-01-10 09:03:57 | | 470 | monitor-01 | INFO | Failed to connect to upstream | 140 | 2026-02-11 10:10:10 | ## Expected output First 3 of 3 rows: | log_level | total_count | | --- | --- | | CRITICAL | 40 | | ERROR | 40 | | WARN | 40 | ## 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 `OR`s, 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 **SEVERITY COUNTS** ```sql 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 ``` > **Three groups, every partition scanned** > > 200M rows, partitioned by `log_timestamp`. With no time predicate this scans every partition. A partial index on `log_level` for the three values can help, but the planner usually picks a parallel seq scan with a hash aggregate over three groups. The cost is bounded by I/O, not by the aggregate. > **Ask for the time window first** > > Ask aloud: do they want a time window? On a 200M-row table you almost always want `WHERE log_timestamp >= now() - interval '24 hours'` so the query touches one partition. If they say full history, fine, write it. Asking shows you read the partition key before scanning. > **One row per level, not one wide row** > > Reaching for `SUM(CASE WHEN log_level = 'ERROR' THEN 1 END)` pivots the levels into one wide row. The prompt asks for one row per level, so a pivot inverts the requirement. Read the output shape before you write the SELECT. > **Check the casing before trusting the list** > > If `log_level` ever holds values like 'error' or 'Error', the literal `IN` list misses them. Confirm the values are uppercase before writing the filter. If they are not, wrap the column in `UPPER(log_level)` and accept that the index can no longer be used. --- ### 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_worth_paging) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.