Shared Endpoints

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

Problem

Raw call counts in api_calls make some endpoints look widely used when a few chatty users drive most of the traffic. For the platform team's deprecation review, surface the endpoints called by more than 60 different users, with the headcount for each.

Shared credentials across endpoints.

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 4 of 4 rows
endpoint	user_count
/api/v1/orders	100
/api/v1/search	70
/api/v1/users	100
/api/v2/products	100

Worked solution and explanation

What this really is

Strip the deprecation story and this is a per-endpoint headcount with a gate on it. Anyone can bucket api_calls by endpoint and count something. What separates candidates is refusing to count rows. COUNT(*) folds in every repeat call and every anonymous hit with a null user_id, so an endpoint a few chatty clients hammer looks widely adopted. On this data /api/v1/auth/login takes more than 60 calls, but no more than 60 people stand behind them. Count rows and it stays on the list; count people and it drops off. The number you show and the number you gate on are the same COUNT(DISTINCT user_id).

Step 1: Measure reach in people, not calls

Bucket the calls by endpoint and measure reach with COUNT(DISTINCT user_id). The dedup collapses repeat calls from one person to a single head. Aggregates skip nulls, so the unauthenticated rows fall out without a separate filter.

Step 2: Gate after the grouping on the same headcount

Keep only the widely used endpoints with HAVING COUNT(DISTINCT user_id) > 60. HAVING runs after the grouping, so the headcount exists to test. The dedup has to appear here too: drop it and the gate quietly reverts to counting calls.

The solution

Endpoints with more than 60 different users

SELECT endpoint, COUNT(DISTINCT user_id) AS user_count
FROM api_calls
GROUP BY endpoint
HAVING COUNT(DISTINCT user_id) > 60

Gate on calls: HAVING COUNT(*) > 60 counts every row, so repeat calls and null user_id hits push /api/v1/auth/login over the bar, and every headcount is inflated.
Gate on people: HAVING COUNT(DISTINCT user_id) > 60 counts each identified user once, so only the four endpoints genuinely used by more than 60 people survive.

Common follow-up questions

How would you also flag endpoints where a single user made more than half the calls? (Probes mixing a distinct count with a total count in the same query.)
The bar is a fixed 60 today. How would you make it the top 10 percent of endpoints by headcount instead? (Tests replacing a static threshold with a data-driven one.)
A couple of `endpoint` values carry stray spaces, like ' /api/v2/products'. How would you fold them into their clean route? (Tests noticing dirty grouping keys and normalizing them before counting.)