Log Priority
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
Which entries in the server logs that have a recorded response time are urgent for on-call, and which are routine? List each with its server name, original log level and response time in milliseconds, labeled urgent when the level is CRITICAL or ERROR and routine otherwise.
Which servers are on fire before coffee?
Tables
server_logs
Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT
| 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
| server_name | log_level | response_time_ms | triage |
|---|---|---|---|
| web-prod-02 | WARN | 23 | routine |
| api-prod-02 | DEBUG | 49 | routine |
| db-primary | CRITICAL | 0 | urgent |
| worker-01 | ERROR | 101 | urgent |
| monitor-01 | INFO | 140 | routine |
Worked solution and explanation
What this really tests
Under the on-call framing this is a two-part row-level transform: give each log a category it doesn't store, and drop the rows you can't act on. Nothing in server_logs carries an urgent/routine flag, so a plain column list can't produce it. You reach for a CASE that tests log_level against the severe levels with IN, and you gate the whole view on response_time_ms IS NOT NULL. Skip that filter and every row with no recorded latency leaks into a view whose entire job is to show what to act on, each one blank where the number should be.
Building the query
Step 1: Drop the rows you can't triage
Keep only rows where response_time_ms IS NOT NULL. A share of the table has no recorded latency, and a triage entry with a blank number is noise, so those rows go before anything else is computed.
Step 2: Derive the triage label
server_logs has no urgent/routine column, so derive it. A CASE that checks log_level IN ('CRITICAL', 'ERROR') returns 'urgent', and the ELSE catches every other level as 'routine'. The IN list keeps the severe set in one place.
The solution
SELECT server_name,
log_level,
response_time_ms,
CASE WHEN log_level IN ('CRITICAL', 'ERROR') THEN 'urgent' ELSE 'routine' END AS triage
FROM server_logs
WHERE response_time_ms IS NOT NULLCommon follow-up questions
- A sixth level, FATAL, is added and should also count as urgent. What changes in your query? (Tests whether the candidate keeps the severity policy in one place rather than scattering it.)
- How would you also surface, per server, how many urgent rows it currently has? (Tests moving from a row-level projection to a grouped aggregate.)
- What breaks if `log_level` arrives in mixed case, like 'Error' or 'critical', and how would you handle it? (Tests awareness of value normalization before a membership test.)