# Still Breathing > One specific day. Which tokens were still alive? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The security team is auditing which owners held a live API token on November 1 of 2026, and each qualifying owner should appear once. Different services write the status column inconsistently, so treat a token as enabled only when its status reads exactly as the lowercase word 'active'. A live token also had to be issued before that date and not yet expired, with a missing expiration date treated as still valid. ## Worked solution and explanation ### What this problem is really testing This wears a date-window costume, but the real probe is two-fold: do you treat the literal text in `status` as authoritative, and do you read a NULL `expires` as 'never expires' rather than 'unknown'? The `api_tokens` rows arrive from several services that disagree on casing, so 'active', 'Active', and 'REVOKED' all coexist in the same column. The owners you want are exactly the ones tagged with the lowercase token 'active'. Match loosely and you pull in tokens the security team already considers disabled. > **Match the value, not a normalized version of it** > > The single decision that cracks this is `status = 'active'` as a plain case-sensitive equality. The data deliberately mixes 'Active' and 'active'; the business rule says only the exact lowercase value counts. The moment you reach for `LOWER(status)` or `ILIKE`, you have quietly changed the question and started counting tokens that were never meant to qualify. > **The case-folding over-match** > > `LOWER(status) = 'active'` and `status ILIKE 'active'` feel safer, but here they are wrong: they fold the capital-A 'Active' rows back in and inflate the result. On the sample seed that is the difference between keeping owner 585 and 1070 versus also wrongly returning 391 and 876, whose tokens carry the 'Active' state. > **Dropping the never-expiring tokens** > > Writing `expires > '2026-11-01'` alone silently discards every row where `expires` is NULL, because any comparison against NULL evaluates to NULL, never true. Around 8% of tokens never expire. You must keep them explicitly with `expires IS NULL OR ...`. ### Build it step by step #### Step 1: Keep only the exactly-active tokens Filter `api_tokens` with a case-sensitive `status = 'active'`. This is where most rows fall away, and where the casing trap lives: the capital-A and uppercase variants must not survive this predicate. #### Step 2: Cut to tokens that existed on the target date Add `issued < '2026-11-01'`. A token issued on or after November 1 was not yet live that morning, so the bound is strictly less-than, not less-than-or-equal. #### Step 3: Keep tokens that had not lapsed A token is still valid if it has no expiration at all or its expiration is after the target date: `expires IS NULL OR expires > '2026-11-01'`. The IS NULL branch is the part that saves the never-expiring tokens from being dropped. #### Step 4: Collapse to one row per owner An owner can hold several qualifying tokens, so select DISTINCT owner_id. Ordering by owner_id gives a stable, reviewable result. **Owners with a live token on the target date** ```sql SELECT DISTINCT owner_id FROM api_tokens WHERE status = 'active' AND issued < '2026-11-01' AND (expires IS NULL OR expires > '2026-11-01') ORDER BY owner_id ``` *Case-exact status, strict issued bound, and an IS NULL branch for never-expiring tokens.* > **Why the equality keeps the plan cheap** > > `status` has only four distinct values over 500K rows with one hot value, so a plain `status = 'active'` equality is sargable and can ride an index or a fast scan. Wrapping it in `LOWER(...)` makes the predicate non-sargable, forcing a full table scan plus a per-row function call. The literal comparison is both more correct and faster. > **What the interviewer is listening for** > > The tell of seniority is asking, before writing anything, whether 'active' is stored canonically or whether casing varies, and how a NULL expiration should be read. Candidates who name those two forks up front and then write a sargable, case-exact predicate are the ones who pass. ## Common follow-up questions - Suppose the security team later decides 'Active' and 'ACTIVE' should also count as enabled. How would you change the predicate, and what does that cost the query plan? _(Tests whether they understand the sargability trade-off between case-exact equality and case-folding.)_ - How does your query behave for a token whose `expires` equals exactly '2026-11-01'? Is that token live on the target date? _(Probes the inclusive-versus-exclusive boundary decision on the expiration comparison.)_ - If you needed the count of live tokens per owner instead of just the owner list, how would the query change? _(Tests moving from a DISTINCT projection to a grouped aggregate.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/still_breathing) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.