Loudest in the Room

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

DoorDash

Problem

Our daily busiest-endpoints panel no longer matches what on-call sees in api_calls, partly because some endpoint paths were logged with stray spaces around them and show up as two different endpoints. For every day, return the endpoints whose call count is among that day's three highest counts, with the day, the cleaned endpoint, and its level (1 for the busiest count, 3 for the third highest).

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

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

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 clean the endpoint path, 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 traps

The build

Step 1: Count calls per day and cleaned endpoint

In daily_counts, bucket call_time to its calendar date with DATE(call_time), strip the path with TRIM(endpoint), and COUNT(*) grouped by both. Grouping on the raw timestamp would split each day into per-second fragments; grouping on the raw path would split padded endpoints in two.

Step 2: Assign a level within each day

In ranked, apply DENSE_RANK() OVER (PARTITION BY call_day ORDER BY call_count DESC). Partitioning by day restarts the numbering every day, descending count 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 rnk <= 3 and return the day, the endpoint and the level. Because equal counts share a level, a day can legitimately return more than three rows, and that is intended: it is exactly what a row cap would hide.

The solution

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

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 add a deterministic tiebreak column to the window's `ORDER BY` so the cut is reproducible.)
• Product wants the top three per day and region. How do you extend it? (Add `region` to both the `GROUP BY` and the `PARTITION BY` so the numbering restarts per day and region instead of per day.)
• Path hygiene keeps drifting. Where should the cleaning live long term? (Normalize on write so every reader agrees, or extend the cleaning (case, trailing slash) once product rules those are the same route.)