Search Endpoint Status Distribution
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
- Asked in
- Yelp
Problem
The '/api/v1/search' endpoint has been flaky. Surface the distribution of HTTP status codes for calls to that endpoint. Show each status and its count, with statuses in ascending order.
Status codes on the health endpoint.
Tables
api_calls
Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT
| call_id | endpoint | method | status | latency | user_id | call_time | err_msg |
|---|---|---|---|---|---|---|---|
| 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL |
| 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL |
| 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL |
| 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL |
| 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL |
| 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found |
| 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized |
| 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded |
| 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error |
| 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway |
Expected output
| status | call_count |
|---|---|
| 200 | 66 |
| 201 | 3 |
| 204 | 2 |
| 400 | 1 |
| 403 | 3 |
Worked solution and explanation
What this really is
This is a filtered histogram dressed up as an incident ticket. Anyone can type GROUP BY status. What separates candidates is scoping the rows to the one endpoint before the tally, and counting calls rather than callers. Drop the filter and the 200 bucket swallows every endpoint's healthy traffic, so a flaky search path looks perfectly fine on the very report meant to expose it.
Step 1: Scope the rows to the search endpoint
WHERE endpoint = '/api/v1/search' runs before any bucket forms, so every count describes search alone. An exact match is the right call here: api_calls stores '/api/v1/users' and '/api/v1/users/' as separate endpoints, and the search path has no variants to fold in.
Step 2: Bucket by status and count every row
COUNT(*) counts calls, which is what flakiness looks like: a client that retries a 503 three times is three failed calls. COUNT(DISTINCT user_id) answers a different question (how many callers saw each code) and flattens exactly the retry pattern you were asked to surface.
Step 3: Sort by the code itself
ORDER BY status ASC keeps the 2xx, 4xx and 5xx families together, so the eye lands on the failure tail at the bottom. Because status is the grouping key, every output row is unique on it and the order is total without a tiebreaker.
SELECT status, COUNT(*) AS call_count
FROM api_calls
WHERE endpoint = '/api/v1/search'
GROUP BY status
ORDER BY status ASC- Counting calls
COUNT(*)adds one per request. A retry storm on '/api/v1/search' shows up as a fat 5xx bucket, which is the signal an on-call engineer needs.- Counting callers
COUNT(DISTINCT user_id)adds one per caller per code. Five retries from one client become a single tick, so the storm reads as a blip.
Common follow-up questions
- How would you add each status's share of all search calls next to its count? (Tests dividing a group count by the endpoint total without a second scan.)
- How would you roll the codes up into 2xx, 4xx and 5xx families? (Tests deriving a bucket from the integer `status` before counting.)
- What changes if some `endpoint` values carry stray whitespace, like '/api/v1/orders '? (Tests normalizing the filter column so padded rows are not silently dropped.)