# The Loudest in the Room > Somewhere in the noise, two keep knocking. Find who. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The API gateway team is cracking down on abusive traffic patterns. Find the two clients generating the most blocked requests, where a request counts as blocked when the blocked value is greater than 0. Show each client and their blocked count, sorted from most blocked to least. ## Worked solution and explanation Strip the abuse-detection costume off this and it's a per-client SUM over a mostly-null column, with a deterministic top-N stapled on. The skill being probed: do you know that `blocked` is the METRIC to add up, not a flag to count? Almost everyone gets the GROUP BY and the LIMIT. What separates people is the row-level filter and the tie-break. `blocked` is null on the overwhelming majority of rows, so anyone who writes `COUNT(*)` or treats a non-null `blocked` as 'one block' reports traffic volume, not blocked volume, and hands the interviewer two completely wrong clients with total confidence. ### The trap: null is not zero, and a row is not a block Look at the sample: nine of ten rows have `blocked = null`. A block only happened when `blocked` is a positive integer. There are two independent ways to get this wrong. First, `COUNT(*)` per client counts how often the client showed up in the rate-limit log, which is dominated by the null rows and answers a different question. Second, even `SUM(blocked)` without a filter is fine on this data (null sums to nothing), but the moment `blocked` can be zero or negative, an unfiltered sum quietly folds reversals and no-ops into your ranking. The fix is one clause: `WHERE blocked > 0` scopes the aggregate to real blocks before you ever group. **Ranks traffic, not blocks** SELECT client, COUNT(*) AS c FROM rate_limits GROUP BY client ORDER BY c DESC LIMIT 2; Counts every appearance. The null-heavy rows dominate, so the 'top' clients are just your chattiest clients. **Ranks actual blocked volume** SELECT client, SUM(blocked) AS total_blocked FROM rate_limits WHERE blocked > 0 GROUP BY client ORDER BY total_blocked DESC, client ASC LIMIT 2; Adds up real blocks only. A client with one noisy row can outrank a client with a thousand quiet ones. > **COUNT vs SUM is the whole question** > > The most common failure here isn't syntax, it's picking the wrong aggregate. `COUNT(*)` answers 'who appears most', `COUNT(blocked)` answers 'who has the most non-null rows', `SUM(blocked)` answers 'who was blocked the most'. Only the last one matches the prompt. Say the metric out loud before you type the aggregate. ### How to get there #### Step 1: Filter to real blocks first `WHERE blocked > 0` runs per row, before grouping. This is the part people conflate with HAVING. HAVING filters groups after the sum; WHERE drops the null and zero rows so they never enter a group at all. On a 3M-row table where most rows are null, this also shrinks what the aggregate has to touch. #### Step 2: Collapse to one row per client `GROUP BY client` sets the grain to the answer's grain: one row per client. Everything in the SELECT is now either the grouping key or an aggregate over it. If you ever find yourself sorting raw rows instead, that's the tell you skipped this step. #### Step 3: Rank with a deterministic tie-break `ORDER BY total_blocked DESC` puts the worst offenders on top, but `LIMIT 2` will silently truncate a tie for second place and return whichever row the engine felt like. Add `client ASC` as a secondary sort so the output is reproducible run to run. Without it, this query is non-deterministic and your grader's row-order check becomes a coin flip. **Canonical solution** ```sql SELECT client, SUM(blocked) AS total_blocked FROM rate_limits WHERE blocked > 0 GROUP BY client ORDER BY total_blocked DESC, client ASC LIMIT 2 ``` *Filter to real blocks, sum per client, rank with a tie-break, take two.* > **The tell that reads as senior** > > Adding `client ASC` unprompted is the move that signals you've been burned by non-deterministic LIMIT before. A mid-level candidate writes `ORDER BY total_blocked DESC LIMIT 2` and moves on. A senior one pauses, says 'ties for the cutoff are undefined here', and pins the order. Interviewers notice which one you are. > **Why this stays a single cheap scan** > > 3M rows, ~60k distinct clients. This is one aggregate scan plus a top-2 sort, no self-join and no subquery. `WHERE blocked > 0` is a sargable predicate, so a partial index on `blocked` (or a covering index on `(client, blocked) WHERE blocked > 0`) lets the planner skip the null mass entirely. The final ORDER BY only sorts the ~grouped rows, and LIMIT 2 lets the engine keep a top-2 heap rather than materializing a full sort. > **In production this is a dashboard tile** > > This exact shape backs the 'top abusers' panel on an API gateway dashboard. The null-heavy column is realistic: most rate-limit checks pass, so `blocked` is only populated on the interesting rows. Teams that count rows instead of summing the metric end up throttling their highest-traffic legitimate clients and never catch the low-volume attacker with a spike. ## 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 show each client's total blocked broken down by endpoint, keeping only the top-2 clients overall. _(Requires computing the top-2 clients in a CTE, then joining back for the per-endpoint breakdown, testing whether they can separate ranking from detail.)_ - If blocked can be negative to represent reversed blocks, does your ranking still mean what you think? _(Forces them to reconsider whether WHERE blocked > 0 is a filter or a sign convention, and whether the sum should net out reversals.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_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.