# Left On > Some switches never got flipped back. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We're auditing feature flags that got left on too long: surface the ones created more than 730 days before May 1, 2026. For each, show its name, owner, how many whole years it's been since creation, and whether it's still enabled, counting a flag with no updated timestamp as still on. ## 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 SQLite's 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 you silently label every never-touched flag as 'No', quietly dropping exactly the stale flags an audit exists to catch. > **Trick to solving** > > Read the still_enabled rule as two independent reasons a flag is on, joined by OR. Write the CASE first, before anything else, because it is where wrong answers come from. Then the date math is mechanical: JULIANDAY gives you a day count you can threshold and divide. --- ### 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. Doing the subtraction on JULIANDAY values (not on the raw text dates) is what makes the day count correct. #### 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 that was never 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: Order the output Order by flag_name, then owner, then years_since_creation descending, then still_enabled, so the output is stable and matches the expected shape exactly. --- ### The solution **Date arithmetic with conditional formatting** ```sql 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 pitfall** > > The most common wrong answer keys still_enabled on enabled alone: CASE WHEN enabled = 1 THEN 'Yes' ELSE 'No' END. That flips every never-updated flag to 'No', which is precisely the population the audit cares about. Always spell out both branches of the OR. > **Interviewers watch for** > > Interviewers watch how you handle the boundary and the null. State out loud that 730 is a strict cutoff and that a missing updated timestamp means 'on', not 'unknown'. Candidates who quietly assume enabled alone decides the status get the whole column wrong without noticing. > **Cost analysis** > > This is a single scan of 600 rows: JULIANDAY is computed per row for the filter, then a sort satisfies the ORDER BY. Because the predicate is a computed expression, a plain index on created cannot help it; if this ran on a large table you would precompute an age column or a created cutoff constant so the filter becomes sargable. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/left_on) - [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.