Users With Admin Tokens

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

A security review needs every user who holds at least one API token whose scope contains the substring 'admin'. Return each qualifying token's user_id, username, email, token_id, and scope, walking up from the lowest user_id and, within a single user, from their earliest-issued token onward to their most recent.

Admin tokens. Who holds them?

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_tokens

Columns: token_id INTEGER, owner_id INTEGER, scope TEXT, status TEXT, issued TEXT, expires TEXT, last_used TEXT, requests INTEGER

api_tokens: 10 of 200 rows
token_idowner_idscopestatusissuedexpireslast_usedrequests
1043197writerevoked2025-02-022027-02-022026-02-0247
1086294adminexpired2025-03-032027-03-032026-03-0394
1129391read:usersActive2025-04-042027-04-042026-04-04141
1172488write:ordersREVOKED2025-05-052027-05-052026-05-05188
1215585read:analyticsactive2025-06-062027-06-062026-06-06235
1258682full_accessrevoked2025-07-07NULL2026-07-07282
1301779readexpired2025-08-082027-08-082026-08-08329
1344876writeActive2025-09-092027-09-09NULL0
1387973adminREVOKED2025-10-102027-10-102026-01-10423
14301070read:usersactive2025-11-112027-11-112026-02-11470

Expected output

First 5 of 30 rows
user_idusernameemailtoken_idscope
100alicealice@example.com5300admin
100alicealice@example.com10005400admin
197aaron42aaron42@example.com3193admin
197aaron42aaron42@example.com10005351admin
294 ameliaamelia@example.com1086admin

Worked solution and explanation

A security audit wearing a join costume

This is a substring match on a free-text permission string, joined to the people who own it. Anyone can write the join. The trap is the predicate: 'contains admin' is not scope = 'admin'. Equality only catches the literal scope, and the privileged tokens in a real audit are the ones named 'admin:read', 'read,admin' or 'superadmin'. Miss those and the report under-counts exactly the accounts a reviewer cares about.

One user owns many tokens, so the join fans out to the token grain.
  • users: user_id PK, username, email
  • api_tokens: token_id PK, owner_id FK, scope, status
  • api_tokens.owner_id references users.user_id

Building it in three decisions

Step 1: Join on the owner foreign key

username and email live only in users, while scope lives only in api_tokens. A scan of api_tokens alone cannot project the columns the review wants, so users.user_id = api_tokens.owner_id is required. An inner join is right: a token with no matching user is not something to report here.

Step 2: Filter with a wildcard on both sides

scope LIKE '%admin%' matches the substring anywhere. The leading % catches 'superadmin' and 'read,admin', the trailing % catches 'admin:write'.

Step 3: Leave the grain alone

The ask is each qualifying token, so there is no DISTINCT and no GROUP BY. A user holding two admin tokens should appear twice. The ORDER BY just makes the output walk up by user.

Per-token admin audit
SELECT u.user_id, u.username, u.email, at.token_id, at.scope FROM users u JOIN api_tokens at ON u.user_id = at.owner_id WHERE at.scope LIKE '%admin%' ORDER BY u.user_id, token_id

A single pass: join, substring filter, no aggregation.

Exact equality
scope = 'admin' returns only tokens whose whole scope is the word admin. It runs fast and looks right on a quick eyeball, but it silently drops every compound scope.
Substring match
scope LIKE '%admin%' returns every token whose scope mentions admin anywhere. It costs the same single scan here and is the only form that matches the stated requirement.

Common follow-up questions