# Shared Endpoints > Shared credentials across endpoints. Canonical URL: 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. ## Tables ### api_calls - call_id INTEGER - endpoint TEXT - method TEXT - status INTEGER - latency REAL - user_id INTEGER - call_time TEXT - err_msg TEXT Sample rows (10 of 670): | 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)`. > **Counting calls lets the chatty endpoint back in** > > Testing the count in `WHERE` errors, because `WHERE` runs before the grouping and the count does not exist yet. The subtler miss is `HAVING COUNT(*) > 60`: it keeps the login endpoint that a few retrying clients pushed past the bar. It also reports call totals, like 133 for `/api/v1/orders`, where the review wanted 100 people. #### 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** ```sql 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. > **The dedup belongs in the `HAVING` too** > > The tell is whether `COUNT(DISTINCT user_id)` shows up in the `HAVING` and not just the `SELECT`. A candidate who fixes the metric but gates on `COUNT(*)` stopped one clause early, and their row set is wrong here. > **One grouped scan, deduplicated per endpoint** > > There is no join to blow up; the whole cost is one aggregation over `api_calls`. The distinct count forces per-group dedup of `user_id`. An index on (`endpoint`, `user_id`) lets the engine stream sorted pairs instead of hashing every row. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/shared_endpoints) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.