# The Loudest Machines

> Every server keeps a diary. Find the ones that never stop writing.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

An observability team is sizing next quarter's log storage and needs to see how the load spreads across servers. Report how many log entries each server generated during 2026, busiest first.

## Worked solution and explanation

### What this really is

Under the capacity-planning story, this is a filtered count-per-group: scope the table to one calendar year, then count rows per server. Everyone gets the GROUP BY right. What separates candidates is two quieter things: extracting the year in a way this engine actually supports, and making the ordering deterministic. Skip the tie-break and your busiest-first list drifts between runs, because skewed log volumes leave many servers sharing the same count.

#### Step 1: Scope to the year

Filter log_timestamp to the target calendar year. The sandbox is SQLite, so EXTRACT(YEAR FROM ...) is not available; STRFTIME('%Y', log_timestamp) returns the year as text, which you compare against the string literal '2026'. Comparing to a bare number would silently mismatch.

#### Step 2: Count per server

GROUP BY server_name and COUNT(*). Every surviving row is one log entry, so a plain COUNT(*) is the volume. No DISTINCT: you want every entry, including repeat lines from the same server.

#### Step 3: Order deterministically

Sort by log_count descending for busiest-first, then server_name ascending. The second key is not cosmetic. With many servers tied on volume it is the only thing that makes the row order, and any top-N cut, reproducible.

**Year-scoped volume per server**

```sql
SELECT server_name, COUNT(*) AS log_count
FROM server_logs
WHERE STRFTIME('%Y', log_timestamp) = '2026'
GROUP BY server_name
ORDER BY log_count DESC, server_name ASC
```

> **The tie-break is load-bearing**
>
> The prompt only asks for busiest-first, but the expected rows show tied counts listed alphabetically. That is your cue to add server_name ASC as a secondary sort. Without it, two servers on the same count can come back in either order and the output fails an exact-match check.

> **EXTRACT is not in SQLite**
>
> Reaching for EXTRACT(YEAR FROM log_timestamp) or log_timestamp::date crashes here: SQLite has neither. STRFTIME('%Y', ...) is the portable year extraction, and it returns text, so compare against '2026' with quotes.

> **One scan, one hash aggregate**
>
> Across 80M rows the plan is a single scan filtered by year feeding a hash aggregate keyed on server_name (about 30 distinct values). No window function, no self-join. Because log_timestamp is the partition key, the year predicate prunes most partitions before the aggregate ever runs, so the query stays cheap despite the row count.

---

## Common follow-up questions

- The team now wants this broken out by month as well as by server. How does the query change? _(Tests whether they can add a second grouping key and a derived month column without breaking the grain.)_
- Some servers were decommissioned mid-year and should be dropped entirely. Where would you apply that exclusion, and why there? _(Tests filter placement and the difference between filtering rows and filtering groups.)_
- How would you return only the top five servers by volume, and what happens if the fifth and sixth are tied? _(Tests LIMIT combined with the deterministic ordering, and awareness of ties at the cutoff line.)_

## Related

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