Left On

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

Salesforce

Problem

Flags created more than 730 days before May 1, 2026 are what the platform team's left-on audit is chasing, and a flag with no updated timestamp still counts as on regardless of enabled. For each one, show its name, its owner, whether it's still enabled as 'Yes' or 'No', and its whole years since creation, taken as elapsed days divided by 365 and rounded down. The audit reads alphabetically by flag name and then owner, with the most whole years first where those match and switched-off flags ahead of running ones.

Some switches never got flipped back.

Tables

feat_flags

Columns: flag_id INTEGER, flag_name TEXT, enabled INTEGER, rollout REAL, owner TEXT, created TEXT, updated TEXT

Expected output

Worked solution and explanation

What this problem is really testing

This is a date-arithmetic problem wearing a hygiene costume. The skill being probed: can you measure elapsed time with JULIANDAY, filter on a strict day threshold without an off-by-one, and get the two-part still-enabled rule right? The trap is that a flag is on when enabled = 1 OR updated IS NULL, not just when enabled = 1. Miss the OR and every never-touched flag reads 'No', quietly hiding exactly the stale flags the audit exists to catch.

Breaking it down

Step 1: Filter to the stale flags

Keep only flags older than the threshold with JULIANDAY('2026-05-01') - JULIANDAY(created) > 730. The comparison is strict, so a flag exactly 730 days old is excluded. Subtracting JULIANDAY values rather than comparing the raw text dates is what makes the day count real.

Step 2: Derive still_enabled with the null rule

Build still_enabled with CASE WHEN enabled = 1 OR updated IS NULL THEN 'Yes' ELSE 'No' END. The OR is the whole point: a flag nobody ever touched has no updated timestamp, so it counts as on even when enabled reads 0.

Step 3: Convert days to whole years

Turn elapsed days into whole years with CAST((JULIANDAY('2026-05-01') - JULIANDAY(created)) / 365 AS INTEGER). The CAST truncates toward zero, giving completed years rather than a rounded fraction.

Step 4: Lay out the register

Sort by flag_name, then owner, then years_since_creation descending, then still_enabled, so the register reads the way the audit lays it out.

The solution

SELECT flag_name, owner,
       CASE WHEN enabled = 1 OR updated IS NULL THEN 'Yes' ELSE 'No' END AS still_enabled,
       CAST((JULIANDAY('2026-05-01') - JULIANDAY(created)) / 365 AS INTEGER) AS years_since_creation
FROM feat_flags
WHERE JULIANDAY('2026-05-01') - JULIANDAY(created) > 730
ORDER BY flag_name, owner, years_since_creation DESC, still_enabled

Common follow-up questions

• The filter uses 730 days but the years column divides by 365. When could those two disagree, and does it matter here? (Tests whether the candidate keeps the day threshold and the divide-by-365 consistent, and understands leap-year drift.)
• These dates are stored as text. How would you handle timestamps that include a time and a timezone offset? (Tests awareness that `created` and `updated` are stored as text and may carry timezones in production.)
• If `feat_flags` had 500 million rows, how would you make the age filter fast? (Tests index and precomputation strategy for a non-sargable date predicate.)
• How would you convince yourself this query is correct on a fresh dataset? (Tests verification approach: spot checks, row counts, and boundary cases.)