Still Breathing

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
Asked in
LinkedIn

Problem

After API credentials leaked on July 1 of 2025, every owner who held a token in api_tokens that was valid that day, going by its issue and expiry dates alone, must rotate, and the security team wants each such owner listed once, lowest owner id first. A token counts as valid if it was issued before that day and had not yet expired, and a token with no expiration date never expires.

One specific day. Which tokens were still alive?

Tables

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 37 rows
owner_id
100
197
294
391
488

Worked solution and explanation

What this problem is really testing

This is a point-in-time validity check dressed up as incident response. Each row in api_tokens carries an issued date and, most of the time, an expires date, and the question is who held a token that was valid on one day in the past. Anyone writes the two comparisons. What separates candidates is the token with a NULL expires: it never lapses, which makes it the most dangerous credential in a leak, and a bare comparison throws it away without a word. Get the grouping wrong while rescuing it and you name owners whose tokens did not exist yet.

Build it step by step

Step 1: Keep tokens that already existed

Filter api_tokens on issued < '2025-07-01'. A token minted on or after the leak date was not around to be stolen, so the bound is strictly less-than.

Step 2: Keep tokens that had not lapsed

Add (expires IS NULL OR expires > '2025-07-01'). The NULL branch keeps never-expiring tokens; the parentheses keep both branches under the issue-date condition.

Step 3: Do not trust today's status for a past date

Leave status alone. It records whether a token is active or revoked today, and the audit is about a day more than a year ago. A token revoked last month was still a live credential on the day of the leak.

Step 4: Collapse to one row per owner

An owner can hold several valid tokens, so SELECT DISTINCT owner_id, then sort by owner_id so the rotation list reads lowest id first. Because each owner appears once, that sort is total.

Owners holding a valid token on the leak date
SELECT DISTINCT owner_id
FROM api_tokens
WHERE issued < '2025-07-01'
  AND (expires IS NULL OR expires > '2025-07-01')
ORDER BY owner_id

Strict issued bound and a parenthesized NULL branch for never-expiring tokens.

The version that looks fine and is wrong
SELECT DISTINCT owner_id
FROM api_tokens
WHERE issued < '2025-07-01'
  AND expires IS NULL
   OR expires > '2025-07-01'
ORDER BY owner_id

Without parentheses the OR splits the filter, and tokens issued after the leak slip through.

Common follow-up questions