Auth Endpoint Callers

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

Endpoint paths bury the authentication routes inside the versioned path, so a call counts when its endpoint contains 'auth' anywhere, and the security review needs every such call in api_calls tied back to the account in users that made it. List the user's ID, username, email, call ID and endpoint, by user ID and then call ID, lowest first.

Identify users who have called authentication API endpoints

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

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_idendpointmethodstatuslatencyuser_idcall_timeerr_msg
8079/api/v1/ordersPOST2004.21002026-02-02 01:07:00NULL
8158/api/v2/productsPUT2007.91972026-03-03 02:14:00NULL
8237/api/v1/searchDELETE20111.62942026-04-04 03:21:00NULL
8316/api/v1/auth/loginPATCH20415.33912026-05-05 04:28:00NULL
8395/api/v1/auth/logoutget30119NULL2026-06-06 05:35:00NULL
8474/api/v2/analyticspost40022.75852026-07-07 06:42:00Not found
8553/api/v1/paymentsGET40126.46822026-08-08 07:49:00Unauthorized
8632/api/v1/users/POST40330.17792026-09-09 08:56:00Rate limit exceeded
8711/api/v2/products/PUT40433.88762026-01-10 09:03:00Internal server error
8790/api/v1/usersDELETE50037.5NULL2026-02-11 10:10:00Bad gateway

Expected output

First 5 of 120 rows
user_idusernameemailcall_idendpoint
100alicealice@example.com21394/api/v1/auth/login
100alicealice@example.com21435/api/v1/auth/logout
197aaron42aaron42@example.com15268/api/v1/auth/login
197aaron42aaron42@example.com15347/api/v1/auth/login
197aaron42aaron42@example.com21436/api/v1/auth/logout

Worked solution and explanation

What this is really asking

This is an enrichment join with a substring filter, dressed up as a security audit. api_calls already has call_id, endpoint and user_id, which tempts people to answer from one table, but username and email live only in users. The other half is the filter: every endpoint is a full path like '/api/v1/auth/login', so endpoint = 'auth' matches nothing. The review comes back empty and quietly reports that nobody touched authentication when 120 calls did.

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; the seed has several anonymous logout hits that should not appear.

Step 3: Project and sort

Select the five requested columns and sort by u.user_id, then call_id. Both keys are returned columns and unique together, so the order is fixed. call_id is not call_time: user 197's June login retries carry lower ids than their February logout.

The solution

AUTH ENDPOINT CALLERS
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

Common follow-up questions

Common follow-up questions