# Auth Endpoint Callers > Identify users who have called authentication API endpoints Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem A security review needs to trace which users have hit authentication endpoints. Find every API call whose endpoint path contains 'auth', showing the user's ID, username, email, call ID, and endpoint, working through the users from the lowest ID to the highest and, within each user, laying their calls out in the order they were made. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### 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 5 of 120 rows: | user_id | username | email | call_id | endpoint | | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 21394 | /api/v1/auth/login | | 100 | alice | alice@example.com | 21435 | /api/v1/auth/logout | | 197 | aaron42 | aaron42@example.com | 15268 | /api/v1/auth/login | | 197 | aaron42 | aaron42@example.com | 15347 | /api/v1/auth/login | | 197 | aaron42 | aaron42@example.com | 21436 | /api/v1/auth/logout | ## Worked solution and explanation ### What this is really asking `api_calls` holds 2B rows, `users` holds 6M. The auth filter sits on the giant side, and one user maps to many calls. Output is a row per matching call, not per user. --- ### Break down the requirements #### Step 1: Filter calls to auth endpoints `endpoint LIKE '%auth%'` on `api_calls`. The leading wildcard kills any btree index; this is a scan or trigram lookup. #### Step 2: Join to users INNER JOIN on `user_id`. Anonymous calls with null `user_id` drop out by design. #### Step 3: Project and sort Select the five requested columns, ORDER BY `u.user_id` to cluster each user's calls together. --- ### The solution **AUTH ENDPOINT CALLERS** ```sql SELECT u.user_id, u.username, u.email, ac.call_id, ac.endpoint FROM users u INNER JOIN api_calls ac ON u.user_id = ac.user_id WHERE ac.endpoint LIKE '%auth%' ORDER BY u.user_id, call_id ``` > **Cost Analysis** > > Push the LIKE filter on `api_calls` first, hash-join with 6M `users` on the build side. `LIKE '%auth%'` is non-sargable; a trigram index turns the 2B scan into a seek. > **Interviewers Watch For** > > Recognizing the small table is the hash build side. Knowing leading `%` defeats btree. Defending INNER over LEFT here. > **Common Pitfall** > > `endpoint LIKE 'auth%'` or `= 'auth'`. The prompt says contains, and real paths look like `/api/v2/auth/login` or `/oauth/token`. Anchoring misses every nested route. > **The False Start** > > First instinct is FROM users LEFT JOIN `api_calls` with the endpoint filter in WHERE. A non-null predicate on the right side collapses LEFT to INNER, so you pay LEFT's cost for INNER's result. Pivot to INNER JOIN up front. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you accelerate `LIKE '%auth%'` at 2B rows? _(Pre-tag `endpoint` with a category column on write, or build a trigram index.)_ - How do you count anonymous auth hits where `user_id IS NULL`? _(UNION ALL a second query selecting from `api_calls` where `user_id IS NULL`, with NULLs for user columns.)_ - What if you want one row per user instead of per call? _(GROUP BY the user fields, COUNT(`call_id`), MIN/MAX(`call_time`).)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/auth_endpoint_callers) - [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.