# Log Entries by Level

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

Canonical URL: <https://datadriven.io/problems/log_entries_by_level>

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.

## 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 5 of 5 rows:

| log_level | entry_count |
| --- | --- |
| CRITICAL | 40 |
| DEBUG | 40 |
| ERROR | 40 |
| INFO | 40 |
| WARN | 40 |

## Worked solution and explanation

### Why this problem exists in real interviews

The interviewer wants to see you apply grouping to `server_logs`.server_name while accounting for the distribution of `log_level`. This surfaces as a fundamentals check because small logic errors produce results that look correct at a glance.

---

### Break down the requirements

#### Step 1: Aggregate with COUNT

Group all server log rows by `log_level`

#### Step 2: Order the final output

Count the entries in each group as `entry_count`

---

### The solution

**Simple group-count with descending sort**

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

> **Cost Analysis**
>
> The query scans 40M rows from `server_logs`. The aggregation reduces the row count before any downstream processing, which is the key performance lever.

> **Interviewers Watch For**
>
> Naming the output grain ("one row per X") before writing the GROUP BY shows you think about data shape, not just syntax.

> **Common Pitfall**
>
> Returning more columns than the prompt asks for can trigger a "wrong schema" failure in automated grading. Match the output specification exactly.

---

## Common follow-up questions

- What happens to your result if `server_logs`.response_time_ms contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on `response_time_ms`.)_
- How would you verify that your aggregation on `server_logs`.log_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- With millions of distinct values in `server_logs`.log_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like `log_id`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/log_entries_by_level)
- [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.