The Token Census

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

Asked in

Apple

Problem

The platform team needs a headcount of how many token owners issued at least one API token during 2026. Return a single count of unique owners.

How many tokens are out there?

Tables

api_tokens

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

Expected output

Worked solution and explanation

What this really is

This is a headcount across a one-to-many table dressed up as a platform metric. api_tokens holds one row per token, and the same owner_id turns up on several of them: the ten owners who issued anything in 2026 issued two tokens each. Everyone gets the year filter roughly right. What separates candidates is whether the count collapses tokens back to people. Count rows and you report 20, a token count wearing an owner label, and it grows every time someone rotates a key.

Break down the requirements

Step 1: Force the grain question

'Headcount of owners who issued at least one token' means COUNT(DISTINCT owner_id), not COUNT(*) and not COUNT(DISTINCT token_id). Say the cohort out loud: unique owner_id values from api_tokens, restricted to the year.

Step 2: Filter on issuance, not expiry

The prompt says 'issued ... during 2026', so the predicate is on issued, not expires or last_used. Every sample row was last used in 2026, so filtering on activity quietly answers a different question.

Step 3: Read the year off issued

issued is stored as 'YYYY-MM-DD' text, so strftime('%Y', issued) = '2026' reads the year straight off it. Say out loud that in production you would write the half-open range issued >= '2026-01-01' AND issued < '2027-01-01' instead, so an index or partition on issued can be used.

Step 4: Return one scalar

One SELECT, one COUNT(DISTINCT owner_id), aliased distinct_owners. No GROUP BY, no HAVING. The output is one row and one column.

The solution

SELECT COUNT(DISTINCT owner_id) AS distinct_owners
FROM api_tokens
WHERE strftime('%Y', issued) = '2026'

Common follow-up questions

Common follow-up questions

• Rewrite the predicate to be sargable and explain why it matters at 100M rows. (Tests whether you can turn a function-on-column into a half-open range and explain what that does for the index.)
• How would you return monthly unique owners for the same year in one query? (Probes grouping by `strftime('%Y-%m', issued)`, and whether you know that per-month `COUNT(DISTINCT)` values do not sum to the yearly figure.)
• An owner can have tokens under several `scope` values. How does that affect the count? (Checks that you see the unique count on `owner_id` already collapses scopes, while a per-scope breakdown would not.)
• How would you exclude owners whose only token in the year was revoked? (Forces a `status` predicate, the realisation that 'revoked' and 'REVOKED' are one state, and a choice between filtering rows first and a per-owner `HAVING`.)