Active Token Owners in 2026
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
Problem
The platform team is auditing the API access granted in 2026 and needs the size of that year's token-holder base. Find how many different owners were issued a token during the year.
Active token owners this year.
Tables
api_tokens
Columns: token_id INTEGER, owner_id INTEGER, scope TEXT, status TEXT, issued TEXT, expires TEXT, last_used TEXT, requests INTEGER
| token_id | owner_id | scope | status | issued | expires | last_used | requests |
|---|---|---|---|---|---|---|---|
| 1043 | 197 | write | revoked | 2025-02-02 | 2027-02-02 | 2026-02-02 | 47 |
| 1086 | 294 | admin | expired | 2025-03-03 | 2027-03-03 | 2026-03-03 | 94 |
| 1129 | 391 | read:users | Active | 2025-04-04 | 2027-04-04 | 2026-04-04 | 141 |
| 1172 | 488 | write:orders | REVOKED | 2025-05-05 | 2027-05-05 | 2026-05-05 | 188 |
| 1215 | 585 | read:analytics | active | 2025-06-06 | 2027-06-06 | 2026-06-06 | 235 |
| 1258 | 682 | full_access | revoked | 2025-07-07 | NULL | 2026-07-07 | 282 |
| 1301 | 779 | read | expired | 2025-08-08 | 2027-08-08 | 2026-08-08 | 329 |
| 1344 | 876 | write | Active | 2025-09-09 | 2027-09-09 | NULL | 0 |
| 1387 | 973 | admin | REVOKED | 2025-10-10 | 2027-10-10 | 2026-01-10 | 423 |
| 1430 | 1070 | read:users | active | 2025-11-11 | 2027-11-11 | 2026-02-11 | 470 |
Expected output
| distinct_owners |
|---|
| 10 |
Worked solution and explanation
What this really is
Strip off the audit framing and this is a count of unique keys inside a one-year slice. The whole problem hinges on one word: owners, not tokens. Every owner in api_tokens holds several tokens, and the 2026 slice alone has 20 tokens spread across 10 owners, two apiece. Reach for COUNT(*) or COUNT(owner_id) and you count rows. The answer comes back as 20, double the real token-holder base, and nobody reviewing the query catches it until someone asks why the audit found twice as many people as the access list.
Step 1: Pin the window to issued
The year filter rides on issued, not on expires or last_used. A token issued in a prior year that is still valid this year does not belong to this year's grant cohort, and neither does a token used this year but issued earlier. strftime('%Y', issued) extracts the four-digit year as text, so compare it against the year as a string literal, not a number.
Step 2: Decide the grain before you count
The ask is the size of the token-holder base, which is a count of people. That is the owner_id grain. Tokens are the rows; owners are what you report. Saying this out loud is the difference between a correct answer and a plausible-looking wrong one.
Step 3: Count the uniques
Apply COUNT(DISTINCT owner_id) over the filtered rows. The result is a single integer, so alias it clearly and return one row.
SELECT COUNT(DISTINCT owner_id) AS distinct_owners
FROM api_tokens
WHERE strftime('%Y', issued) = '2026'One scan: filter to the year on issued, then dedup and count owners in a single aggregate.
- Counts rows (wrong)
COUNT(owner_id)returns the number of tokens issued this year: 20 here. With repeat holders it overshoots the headcount, and the bug is invisible because the number still looks reasonable.- Counts owners (correct)
COUNT(DISTINCT owner_id)returns the number of different people: 10 here, the token-holder base the audit asked for.
Common follow-up questions
- Break the same year's grants down by `scope`, returning the unique owner count per scope. (Tests moving from a single scalar to a grouped distinct count.)
- How would you find owners who were issued more than one token during the year? (Forces a grouped filter on the same grain and shows whether they understood the dedup.)
- If `issued` were a real timestamp column with an index, how would you rewrite the year filter to keep it index-friendly? (Probes the sargability point from the performance note.)