Top API Caller

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: Walmart

Problem

Our API load runs higher than our known accounts can explain, and calls logged without a user are a suspect too. From api_calls, find the single biggest caller with its total number of calls.

One user triggered more API calls than anyone.

Tables

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_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

First 1 of 1 rows
user_id	call_count
NULL	17

Worked solution and explanation

What this is really asking

This is a find-the-mode question in an API costume: count every row of api_calls per user_id and keep the top bucket. Anyone writes the grouping. What separates candidates is what they do with the 17 calls that carry no user_id. The reflex is to scrub them out with WHERE user_id IS NOT NULL as dirty data. But the grouping already gathers them into one bucket, and that bucket is the biggest caller in the log. Filter it away and you report a known user with 13 calls: the wrong caller and the wrong count.

Step 1: Count every row per `user_id`

COUNT(*) counts rows, so failed calls, redirects and calls with no user all add to the total. GROUP BY user_id puts every NULL into a single group, which is the unattributed traffic the statement names as a suspect.

Step 2: Keep the single top bucket

Sort the counts with call_count DESC and cut with LIMIT 1. The top of this log is not tied (17 against 13), so no tiebreaker can change which row survives.

The solution

Top API caller

SELECT
  user_id,
  COUNT(*) AS call_count
FROM api_calls
GROUP BY user_id
ORDER BY call_count DESC
LIMIT 1;

The scrubbed version

SELECT
  user_id,
  COUNT(*) AS call_count
FROM api_calls
WHERE user_id IS NOT NULL
GROUP BY user_id
ORDER BY call_count DESC
LIMIT 1;

Dropping NULL user_id rows first returns a 13-call user instead of the 17-call bucket.

Scrubbed first: WHERE user_id IS NOT NULL deletes 17 real calls before counting, and the winner becomes a known user with 13.
Grouped as logged: GROUP BY user_id keeps the NULLs together as one caller, and that caller wins with 17.

Follow-up questions

Common follow-up questions

How would the query change for the top caller in the last 7 days? (A `call_time` range prunes partitions, so the scan touches a week instead of a year.)
What if two callers tie for the top spot? (`LIMIT 1` keeps one of them arbitrarily; `RANK() OVER (ORDER BY call_count DESC)` filtered to `rnk = 1` keeps all of them.)
How would you make this cheap at 200M rows? (Maintain a per-caller counter incrementally, so the read is a top-1 over a small summary table.)