The 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

L3

Asked in

TripAdvisor

Problem

A wave of credential-stuffing has been hammering the login endpoint, and the gateway team wants its two worst offenders. From the rate_limits log, look only at requests to /api/v1/auth and find the two clients with the highest total blocked requests, most blocked first.

Somewhere in the noise, two keep knocking. Find who.

Tables

rate_limits

Columns: limit_id INTEGER, client TEXT, endpoint TEXT, allowed INTEGER, blocked INTEGER, window TEXT, checked TEXT

Expected output

Worked solution and explanation

Strip the abuse-detection costume off this and it's a per-client SUM over a magnitude column, scoped to a single endpoint, with a top-two stapled on. The skill being probed: do you know blocked is the number to add up, not a flag to count? Almost everyone gets the GROUP BY and the LIMIT. What separates people is realizing blocked holds a per-window count, so COUNT(*) and SUM(blocked) answer different questions and rank the clients differently. Count rows and you report which client showed up most on the login endpoint; sum the magnitudes and you report who actually got blocked the most.

The trap: a row is not a request

On /api/v1/auth, blocked is populated only on the windows where something was actually blocked, and each value is how many requests that window rejected. There are two independent ways to get this wrong. First, COUNT(*) per client counts appearances on the endpoint, which is dominated by rows where nothing was blocked and answers a different question. Second, even a count restricted to blocked rows tallies how many windows a client was blocked in, not how many requests. Only SUM(blocked) adds up the actual rejected-request volume, and on this data it swaps the second-place client versus a plain row count.

Ranks appearances, not blocks

COUNT(*) per client on the endpoint counts rows. The many windows where blocked is null or zero dominate, so the 'top' clients are just the chattiest. Here it hands second place to 'partner-api'.

Ranks actual blocked volume

SUM(blocked) adds the per-window magnitudes, so a client with a few high-count windows outranks one with many tiny ones. Here it correctly gives second place to 'webhook-svc'.

How to get there

Step 1: Scope to the endpoint under attack

WHERE endpoint = '/api/v1/auth' runs per row, before grouping, so only login traffic ever enters a client's total. Drop it and you rank blocks across every endpoint, which is a different question and a different top two. This filter is exactly what makes 'webhook-svc' beat 'batch-worker' for second place.

Step 2: Collapse to one row per client

GROUP BY client sets the grain to the answer's grain, and SUM(blocked) adds the per-window magnitudes within each client. Everything in the SELECT is now the grouping key or an aggregate over it. If you find yourself sorting raw rows instead, that's the tell you skipped this step.

Step 3: Rank and take the top two

ORDER BY total_blocked DESC puts the worst offenders on top and LIMIT 2 keeps the two you were asked for. The two totals here (840 and 696) are distinct, so the top two are unambiguous and the engine can keep a small top-two heap rather than materializing a full sort.

SELECT client, SUM(blocked) AS total_blocked
FROM rate_limits
WHERE endpoint = '/api/v1/auth'
GROUP BY client
ORDER BY total_blocked DESC
LIMIT 2

Common follow-up questions

• What if two clients tie for second place and you're asked to include both? (`LIMIT 2` truncates ties. Steers toward `RANK()` / `DENSE_RANK()` with an outer filter, or `FETCH FIRST 2 ROWS WITH TIES` where supported.)
• Now break each of the top-two clients' blocked totals down by `window`, keeping only those two clients. (Requires computing the top two in a CTE, then joining back for the per-window detail, testing whether they can separate ranking from breakdown.)
• If `blocked` could be negative to represent reversed blocks, does your ranking still mean what you think? (Forces them to reconsider whether `SUM(blocked)` should net out reversals and whether a `WHERE blocked > 0` guard would change the answer.)