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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

api_calls: 10 of 670 rows
call_idendpointmethodstatuslatencyuser_idcall_timeerr_msg
8079/api/v1/ordersPOST2004.21002026-02-02 01:07:00NULL
8158/api/v2/productsPUT2007.91972026-03-03 02:14:00NULL
8237/api/v1/searchDELETE20111.62942026-04-04 03:21:00NULL
8316/api/v1/auth/loginPATCH20415.33912026-05-05 04:28:00NULL
8395/api/v1/auth/logoutget30119NULL2026-06-06 05:35:00NULL
8474/api/v2/analyticspost40022.75852026-07-07 06:42:00Not found
8553/api/v1/paymentsGET40126.46822026-08-08 07:49:00Unauthorized
8632/api/v1/users/POST40330.17792026-09-09 08:56:00Rate limit exceeded
8711/api/v2/products/PUT40433.88762026-01-10 09:03:00Internal server error
8790/api/v1/usersDELETE50037.5NULL2026-02-11 10:10:00Bad gateway

Expected output

First 1 of 1 rows
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.

Distinct count with numeric threshold filter
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 no user_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