Revoked Tokens by Scope

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

L4

Asked in

Southwest Airlines

Problem

Count our revoked API tokens in api_tokens per scope, alphabetically by scope, keeping only those whose expires had not yet passed on December 1, 2025. A token with no expires never lapses, and status capitalization was never cleaned up, so 'REVOKED' and 'revoked' are the same.

Banned tokens, sorted by what they had access to.

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 liveness check against a cutoff date wearing a security dashboard's badge. Every token's life ends at expires, December 1, 2025 is the cutoff, and the question is which revocations were still guarding something on that day. Anyone can write GROUP BY scope. What separates candidates is remembering that a missing `expires` is a life with no end, and then grouping that alternative so OR does not leak past the status test. Miss the NULL and every permanent ban vanishes; drop the parentheses and active tokens start showing up as revoked.

Build the filter piece by piece

Step 1: Normalize the status

The column holds 'revoked' and 'REVOKED' side by side. LOWER(status) = 'revoked' folds them together; a raw equality quietly undercounts every scope.

Step 2: Keep tokens that had not expired by the cutoff

expires >= '2025-12-01' keeps tokens still alive on the first. A NULL expires makes that comparison unknown, and WHERE discards unknown, so expires IS NULL has to be an explicit alternative.

Step 3: Parenthesize the alternative

AND binds tighter than OR. Written bare, LOWER(status) = 'revoked' AND expires IS NULL OR expires >= ... parses as two separate branches, and the second one admits active and expired tokens of any status.

Unparenthesized

LOWER(status) = 'revoked' AND expires IS NULL OR expires >= '2025-12-01': any token expiring after November slips in, whatever its status.

Grouped

LOWER(status) = 'revoked' AND (expires IS NULL OR expires >= '2025-12-01'): the expiry check is one condition inside the chain, so the status test still applies to every row.

The solution

SELECT scope, COUNT(*) AS blocked_count
FROM api_tokens
WHERE LOWER(status) = 'revoked'
  AND (expires IS NULL OR expires >= '2025-12-01')
GROUP BY scope
ORDER BY scope

Common follow-up questions

• Security now wants only tokens whose life actually overlaps December 2025. What extra condition does that need? (Tests whether the candidate sees that overlap needs a start edge on `issued` as well as an end edge.)
• What if `expires` could also be an empty string instead of `NULL`? (Tests whether the candidate guards both encodings of a missing value.)
• Security wants scopes with zero qualifying tokens listed too. What changes? (Tests whether the candidate reaches for the full list of scopes rather than only the ones the filter keeps.)