Turned Away

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

Domain: SQL
Difficulty: easy
Seniority: L3

Problem

Which clients had at least one rate-limit window in which more than a quarter of the requests were blocked? Each row of rate_limits is one client's window, and its requests are the ones allowed plus the ones blocked.

Every gate keeps a record of who it stopped.

Tables

rate_limits

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

rate_limits: 10 of 200 rows
limit_id	client	endpoint	allowed	blocked	window	checked
129	client-002	/api/v1/search	107	NULL	5m	2026-02-02 01:03:00
158	client-003	/api/v1/orders	114	NULL	15m	2026-03-03 02:06:00
187	mobile-app	/api/v2/analytics	121	NULL	1h	2026-04-04 03:09:00
216	partner-api	/api/v1/auth	128	12	1d	2026-05-05 04:12:00
245	internal-svc	/api/v1/payments	135	NULL	1m	2026-06-06 05:15:00
274	batch-worker	/api/v1/users	142	NULL	5m	2026-07-07 06:18:00
303	admin-ui	/api/v1/search	149	NULL	15m	2026-08-08 07:21:00
332	webhook-svc	/api/v1/orders	156	24	1h	2026-09-09 08:24:00
361	client-010	/api/v2/analytics	163	NULL	1d	2026-01-10 09:27:00
390	client-001	/api/v1/auth	170	NULL	1m	2026-02-11 10:30:00

Expected output

First 5 of 5 rows
client
client-003
batch-worker
client-001
partner-api
webhook-svc

Worked solution and explanation

What this really tests

Strip the rate-limit costume and this is a fraction compared with a threshold, followed by a dedup. Anyone can write the WHERE and the DISTINCT. The trap is the share itself: blocked and allowed are both integers, so blocked / (allowed + blocked) is integer division and floors every window to 0. Write it that way and the query runs cleanly, returns nothing, and tells the review that no client was ever heavily throttled when five of them were.

Break down the requirements

Step 1: Define a window's requests

A row of rate_limits is one client's window. The requests it saw are the ones it let through plus the ones it turned away, so the denominator is allowed + blocked, not allowed alone. Dividing by allowed inflates every share, which pushes borderline windows over the line on other data.

Step 2: Make the share a real fraction

Both counts are INTEGER. On this seed the biggest share is 300 blocked out of 1,100 requests, about 0.27, so integer division turns every window into 0 and the > 0.25 test never passes. CAST(blocked AS REAL) keeps the fraction intact.

Step 3: Let empty counts fall out

138 of the 200 windows have no blocked value. A NULL numerator makes the share NULL, and NULL > 0.25 is not true, so those windows drop without an explicit test. Windows that recorded 0 blocks have a share of 0 and drop too.

Step 4: Collapse to one row per client

26 windows clear a quarter, and every qualifying client owns several of them (client-001 alone has six). DISTINCT client turns those windows into the 5 clients the review needs.

The solution

Clients with a window more than a quarter blocked

SELECT DISTINCT client
FROM rate_limits
WHERE CAST(blocked AS REAL) / (allowed + blocked) > 0.25

The version that returns nothing

SELECT DISTINCT client
FROM rate_limits
WHERE blocked / (allowed + blocked) > 0.25

Runs without error and returns zero rows: integer division floors every share to 0.

Common follow-up questions

Also return each client's worst window share as a percentage. What changes? (Moves from a filter to a per-client `MAX` of the share and tests whether the cast survives the rewrite.)
What if `allowed + blocked` can be 0 for an idle window? (Tests division-by-zero handling, usually `NULLIF(allowed + blocked, 0)` in the denominator.)
Product wants the quarter judged per client across all windows, not per window. How does the query change? (Tests the difference between a per-row share and a ratio of sums, `SUM(blocked)` over `SUM(allowed + blocked)`.)