Most Recent Token Usage

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

L4

Asked in

OpenDoor

Problem

The security team is auditing API token activity. For each token owner, pull only the single most recently used record, working through owners from the lowest owner ID up to the highest. Include all token fields (ID, owner, scope, status, issued date, expiration, last used date, request count) and a row indicator.

Each user's latest token activity.

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

Why this problem exists in real interviews

Per-entity latest-row queries are the bread and butter of operational reporting. Interviewers use this prompt to see whether you reach for ROW_NUMBER with a deterministic tie-break instead of GROUP BY + MAX, which silently loses non-aggregated columns or breaks on ties.

Break down the requirements

Step 1: Pick one row per owner_id, latest first

Number each token within its owner_id partition by last_used descending (token_id descending as a deterministic tie-breaker) using ROW_NUMBER().

Step 2: Tie-break on largest token_id

Keep only rn = 1, the single most recently used record per owner.

Step 3: Filter rn = 1 in the outer query and project all columns

SELECT * so every token field (token_id, owner_id, scope, status, issued, expires, last_used, requests) plus the rn row indicator is returned.

The solution

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY owner_id ORDER BY last_used DESC, token_id DESC) AS rn
  FROM api_tokens
)
WHERE rn = 1
ORDER BY owner_id

Common follow-up questions

• How would you write this without a window function in plain SQLite? (Forces a self-join: `api_tokens` t JOIN (SELECT `owner_id`, MAX(`last_used`) AS mx FROM `api_tokens` GROUP BY `owner_id`) m ON ... and a follow-up tie-break by `token_id`. The candidate should mention this is more code and slower than `ROW_NUMBER`.)
• What changes if `last_used` can be NULL and you still want one row per owner? (Tests whether the candidate orders NULLs correctly. SQLite sorts NULL first in ascending and last in descending, so DESC pushes NULL last, which usually matches intent but should be stated.)
• How would you adapt this to return the latest two tokens per owner instead of one? (Trivial change to WHERE rn <= 2, but the candidate should note that the tie-break suddenly matters more because two rows are kept.)