Users With API Errors
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
Problem
How many unique users have triggered at least one API error? An API error is any call that returned a status code of 400 or higher.
Count unique users who have triggered an API error response
Tables
users
Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT
| user_id | username | signup_date | account_status | age_bucket | |
|---|---|---|---|---|---|
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 |
| 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 |
| 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 |
| 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ |
| 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL |
| 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 |
| 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 |
| 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 |
| 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 |
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
| users_with_errors |
|---|
| 67 |
Worked solution and explanation
Count people, not errors
This is a per-user cardinality dressed up as an error report. Anyone can write the status >= 400 filter. The trap is counting rows: a user who hits a broken endpoint shows up once per failed call, so COUNT(*) over the error rows counts error events, not the people who hit them. Report that as an incident's blast radius and you overstate it by every retry. Only a distinct count on user_id collapses the repeats back to people.
Step 1: Define the error as status >= 400
status is a numeric HTTP code, so an error is a range covering both 4xx and 5xx. Writing > 400 silently drops 400 Bad Request. Matching on err_msg is also a mistake, because some calls that did fail have no message logged.
Step 2: Count each user_id once
COUNT(DISTINCT user_id) counts a user once however many times they failed. Like any aggregate over a column, it skips NULL, so a failed call with no attributed user adds nothing to the count.
SELECT COUNT(DISTINCT user_id) AS users_with_errors
FROM api_calls
WHERE status >= 400- Counting events
COUNT(*)with the same filter returns one per failed call, including the rows with nouser_id.COUNT(user_id)drops those rows but still counts a user once per failure.- Counting people
COUNT(DISTINCT user_id)returns 67: every user who failed at least once, each counted once.
Common follow-up questions
- How would you break this down by error code? (Add `GROUP BY status` for a per-code user count; a user who hits two codes counts once in each.)
- What if you needed the error rate per user? (Group by `user_id` and compare error calls to total calls, over all calls, not just error rows.)
- How would you find the most common error message for these users? (Group the error rows by `err_msg` and count, keeping in mind that some failures have no message.)