# Where the Noise Lives

> Some days are noisier than others.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Our on-call team is figuring out which days of the week the servers run the noisiest. Show each day's name with its log entry count, noisiest first.

## Worked solution and explanation

### What this is really asking

Strip the 'server logs' costume and this is a histogram over a derived dimension: bucket every row by the weekday hiding inside its timestamp, then count the buckets. The data does not hand you a weekday column, so the whole problem is one move: manufacture the grouping key from log_timestamp before you can group on it. Candidates who try to GROUP BY log_timestamp itself get 31 million groups (one per distinct second) instead of seven. The grain you GROUP BY is the grain of your answer, and here you have to build that grain yourself.

### The trap: it is a text column

log_timestamp is stored as TEXT, not a native date. In SQLite there is no EXTRACT(DOW FROM ...); reach for it and the query will not even parse. You pull the weekday with strftime('%w', log_timestamp), which returns a one-character string '0' (Sunday) through '6' (Saturday). Two traps live here: first, it is a string, so compare it against '0'..'6', not the integers 0..6; second, '%w' alone gives you a number nobody wants to read in a report, so you still owe the mapping to a real day name.

#### Step 1: Build the grouping key from the timestamp

strftime('%w', log_timestamp) turns each text timestamp into a weekday code '0'-'6'. This is the SQLite stand-in for Postgres EXTRACT(DOW ...). Decide on this expression first; everything else hangs off it.

#### Step 2: Label it, then group and count

Wrap the code in a searched CASE that maps '0' to 'Sunday' through '6' to 'Saturday'. Group on that labeled expression and COUNT(*) the rows in each bucket. One row per weekday name falls out.

#### Step 3: Order with a deterministic tiebreak

Sort entry_count descending for most-to-fewest. Add the weekday code as a second sort key so two days with identical counts always land in the same order instead of shuffling between runs.

### The solution

**Weekday histogram with strftime**

```sql
SELECT
    CASE
        WHEN strftime('%w', log_timestamp) = '0' THEN 'Sunday'
        WHEN strftime('%w', log_timestamp) = '1' THEN 'Monday'
        WHEN strftime('%w', log_timestamp) = '2' THEN 'Tuesday'
        WHEN strftime('%w', log_timestamp) = '3' THEN 'Wednesday'
        WHEN strftime('%w', log_timestamp) = '4' THEN 'Thursday'
        WHEN strftime('%w', log_timestamp) = '5' THEN 'Friday'
        WHEN strftime('%w', log_timestamp) = '6' THEN 'Saturday'
    END AS day_of_week,
    COUNT(*) AS entry_count
FROM server_logs
GROUP BY day_of_week
ORDER BY entry_count DESC, strftime('%w', log_timestamp)
```

*GROUP BY the labeled expression; tiebreak on the raw weekday code so order is stable.*

> **EXTRACT is not SQLite**
>
> On Postgres you would write EXTRACT(DOW FROM log_timestamp); on SQLite that construct does not exist and the query fails to parse. strftime('%w', col) is the portable equivalent, but note it returns TEXT '0'-'6', so your CASE compares against quoted strings. Mixing the two engines' idioms is the single most common reason a 'correct' query throws a syntax error in this sandbox.

> **Interviewers watch for**
>
> Naming the output grain out loud ("one row per weekday") before writing GROUP BY is the tell that separates people who think in data shapes from people who pattern-match syntax. The follow-up they are waiting for: which days are missing? A plain grouping can only emit weekdays that appear in the data, so a week with no Sunday logs simply has no Sunday row, not a zero.

**GROUP BY log_timestamp**

Groups on the full second-resolution timestamp: up to ~31M distinct groups, one per logged instant. The 'per day of week' requirement is silently lost and the result is unusable.

**GROUP BY derived weekday**

Groups on the strftime-derived label: exactly the weekdays present, at most seven rows. This is the grain the question asks for.

> **Why it stays cheap at 70M rows**
>
> At 70M rows the cost is the single scan plus a hash aggregate into seven buckets; the aggregation collapses the row count before the sort, so ORDER BY touches seven rows, not 70M. There is no join and no subquery to blow up. If log_timestamp is text, a date-range filter cannot use an index, so in production you would store it as a real timestamp (or a generated weekday column) to keep scans cheap.

## Common follow-up questions

- A weekday with zero logs is missing from your output entirely. How would you make all seven days appear, with 0 for the empty ones? _(Tests whether they know a plain GROUP BY cannot emit absent groups and that a calendar/numbers table left-joined in is the fix.)_
- These timestamps are UTC. If the business wants weekday volume in US Pacific time, what changes? _(Tests timezone awareness: strftime works in UTC, so weekday boundaries can be off for non-UTC events.)_
- The table is partitioned by day on log_timestamp stored as text. What would you change so a single-month version of this query prunes partitions instead of full-scanning? _(Tests partition-pruning and storage-type reasoning on a 70M-row table.)_

## Related

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