API Calls With and Without 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

An engineer on the API reliability team is working through an alphabetical checklist of every endpoint in api_calls, judging each call only by whether it logged an error message. Going down that list in order, show each endpoint's clean calls, its calls that produced an error message, and its total call volume.

Some calls succeed. Some do not. Break it down.

Tables

api_calls

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

Expected output

Worked solution and explanation

This is a census of a nullable column dressed up as reliability triage. The skill is splitting every endpoint's calls into NULL and non-NULL err_msg buckets in one grouped pass, and reporting both buckets next to the total. Anyone can write GROUP BY endpoint. What separates candidates is remembering that a clean call is stored as NULL, not as an empty string, and that COUNT(err_msg) skips exactly those rows. Fall for it and the clean column either vanishes or quietly reports the error count, on a table where 615 of 670 calls carry no message at all.

Walking to the query

Step 1: Let err_msg decide clean versus errored

The statement counts a call as errored when it produced an error message, so the split lives in err_msg, not in status. A clean call is NULL there, which is what the blank cells in the sample rows are. IS NULL and IS NOT NULL are therefore the two tests, and between them they cover every row exactly once.

Step 2: Count both buckets in one grouped pass

Group by endpoint, turn each test into a 1 or a 0 with CASE, then SUM it. Keep the ELSE 0. Without it the unmatched rows become NULL, and an endpoint with no clean calls at all, like the one-call ' /api/v2/products' line, would report NULL instead of 0.

Step 3: Take the total from COUNT(*)

COUNT(*) counts rows, NULLs and all, so it gives the full call volume. It also gives you a check to say out loud: on every line, no_error_count + error_count must equal total_count, because the two tests partition the rows.

Step 4: Return endpoints in checklist order

ORDER BY endpoint puts the lines in alphabetical order. After grouping, each endpoint value appears once, so the sort key is unique and no two rows can trade places.

The solution

SELECT endpoint,
       SUM(CASE WHEN err_msg IS NULL THEN 1 ELSE 0 END) AS no_error_count,
       SUM(CASE WHEN err_msg IS NOT NULL THEN 1 ELSE 0 END) AS error_count,
       COUNT(*) AS total_count
FROM api_calls
GROUP BY endpoint
ORDER BY endpoint

Counting the column

COUNT(err_msg) skips NULLs, so it returns the errored calls, not the clean ones. Labelled no_error_count, it reports 8 for '/api/v1/auth/login' where the truth is 66. A filter like err_msg = '' does worse and matches nothing, because a NULL never equals anything.

Counting a condition

SUM(CASE WHEN err_msg IS NULL THEN 1 ELSE 0 END) counts the NULL rows on purpose, and its IS NOT NULL twin counts the rest. Both read the same rows in the same scan, so the two counts always add up to COUNT(*).

Common follow-up questions

• How would you add an error rate per endpoint without risking a division by zero? (Tests `NULLIF` on the denominator and avoiding integer division.)
• What changes if some services log a missing message as an empty string instead of `NULL`? (Tests folding both encodings into one test, for example `NULLIF(TRIM(err_msg), '')`.)
• The team wants ' /api/v2/products' and '/api/v2/products' on one line. What do you change, and where does that fix belong long term? (Tests normalizing the grouping key and pushing the fix upstream.)
• Break the errored calls down by message, with one column per message type. (Tests extending the conditional aggregate into a crosstab.)
• Restrict it to the last seven days on the 200M-row table. What does the plan do differently? (Tests partition pruning on `call_time`.)