# Loudest in the Room

> Every day, a few endpoints carry the load. Surface them.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

An operations dashboard spotlights the busiest API endpoints each day. For every day, surface the endpoints whose daily call count is among the three highest counts that day, and report the day, the endpoint, and its level (1 for the busiest count, 3 for the third highest), earliest day first.

## Worked solution and explanation

### What this really is

Strip the ops-dashboard costume and this is top-N-per-group, with the twist that trips people: N counts count-levels, not rows. You count calls per day and endpoint, order the endpoints within a day by that count, and keep the three highest counts. Two decisions settle whether you pass: which ranking function you reach for, and where you put the level filter. Miss either and the query still runs, it just hands back the wrong set.

---

### The two traps

> **You cannot filter the window in the same WHERE**
>
> Window functions are evaluated after WHERE, so putting 'WHERE rnk <= 3' in the same SELECT that defines DENSE_RANK references a column that does not exist yet. Compute the level in a CTE (or subquery), then filter in the outer query. HAVING will not rescue you either, since it also runs before the window.

> **DENSE_RANK, not RANK or ROW_NUMBER**
>
> The ask is the three highest counts, not the top three arbitrary rows. ROW_NUMBER hands out 1, 2, 3 arbitrarily and silently cuts genuinely tied endpoints; RANK shares positions but leaves gaps, so a day whose lead is a three-way tie goes 1, 1, 1, 4 and loses the second and third counts entirely. DENSE_RANK gives 1, 1, 1, 2, 3, 3, which keeps exactly the three highest distinct counts the expected output shows.

---

### The build

#### Step 1: Count calls per day and endpoint

Bucket call_time to its calendar date with DATE(call_time) and COUNT(*), grouped by day and endpoint. This collapses 300M rows down to at most days times 150 endpoints.

#### Step 2: Assign a level within each day

DENSE_RANK() OVER (PARTITION BY the day ORDER BY the count DESC). Partitioning by day restarts the numbering every day, ordering by count descending puts the busiest endpoint at level 1, and DENSE_RANK makes equal counts share a level with no gaps, so level 3 is the third-highest count.

#### Step 3: Filter to the three highest counts outside the window

In the outer query keep rows where the level is 3 or less, then order by day, level, and endpoint. Because equal counts share a level, a day can legitimately return more than three rows, and that is intended.

### The solution

**Count per day-endpoint, level per day, filter outside**

```sql
WITH ranked AS (
  SELECT DATE(call_time) AS call_day,
         endpoint,
         COUNT(*) AS call_count,
         DENSE_RANK() OVER (PARTITION BY DATE(call_time) ORDER BY COUNT(*) DESC) AS rnk
  FROM api_calls
  GROUP BY DATE(call_time), endpoint
)
SELECT call_day, endpoint, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY call_day ASC, rnk ASC, endpoint ASC
```

> **Interviewers watch for**
>
> Naming why DENSE_RANK beats ROW_NUMBER and RANK, and knowing the window cannot be filtered in the same WHERE. Say both out loud before you write and you read as someone who has debugged this in production, not memorized a template.

> **Why 300M rows stays cheap**
>
> The GROUP BY collapses the table to one row per (day, endpoint) before the window ever runs, so DENSE_RANK sorts a few hundred rows per day, not 300M. The dominant cost is the single scan and aggregate; an expression index on DATE(call_time) lets the aggregate skip a full sort.

---

## Common follow-up questions

- The dashboard now wants exactly three rows per day even when there is a tie for third. What changes? _(Switch to ROW_NUMBER and accept an arbitrary tie-break, or add a deterministic tiebreak column to the ORDER BY so the cut is reproducible.)_
- Product wants top three per (day, region). How do you extend it? _(Add region to both the GROUP BY and the PARTITION BY so the numbering restarts per (day, region) instead of per day.)_
- Some days are dominated by health-check traffic. How would you drop low-signal endpoints before ranking? _(Filter noisy endpoints in a WHERE before the aggregate, or add a HAVING COUNT(*) floor inside the CTE so low-volume endpoints never reach the ranking.)_

## Related

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