Disabled-Flag Share by Owner

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

Meta

Problem

A platform team is auditing feature-flag hygiene by owning team, where a stale flag is one created before this year. For each team, give the fraction of its flags that are stale, as a decimal rounded to three places.

Which teams ship everything off by default.

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 really tests

Strip the hygiene story and this is a per-owner ratio of a conditional count over a total count, where the trap is arithmetic, not logic. Every team on this seed is between 87% and 91% stale, so all six fractions sit strictly between 0 and 1. Write SUM(CASE WHEN created < '2026-01-01' THEN 1 ELSE 0 END) / COUNT(*) and both operands are integers, so SQLite does integer division and truncates every one of those fractions to 0. Your ranking collapses into a six-way tie at zero and the audit is worthless. The whole problem is whether you see that coming and CAST one side to REAL before you divide.

Build it up

Step 1: Count the stale flags without dropping the rest

Group feat_flags by owner and count the stale flags conditionally with SUM(CASE WHEN created < '2026-01-01' THEN 1 ELSE 0 END). Resist putting that date test in a WHERE: filtering first throws away every current-year flag, so the COUNT(*) you divide by would count only the stale ones and you would have lost the denominator.

Step 2: Divide float-safe

The denominator is COUNT(*), every flag the team owns. CAST the numerator to REAL before the division so the ratio keeps its decimals instead of truncating, then wrap it in ROUND(..., 3) to land on three places.

Step 3: Order worst-first with a stable tie-break

ORDER BY stale_ratio DESC floats the messiest teams to the top. Three teams tie at 0.912, so append , owner to break the tie alphabetically and make the order fully deterministic.

SELECT owner,
       ROUND(CAST(SUM(CASE WHEN created < '2026-01-01' THEN 1 ELSE 0 END) AS REAL) / COUNT(*), 3) AS stale_ratio
FROM feat_flags
GROUP BY owner

WHERE then COUNT

WHERE created < '2026-01-01' drops every current-year flag before grouping, so COUNT(*) counts only the stale ones. The numerator equals the denominator and every team reports 1.0.

Conditional SUM

SUM(CASE WHEN created < '2026-01-01' THEN 1 ELSE 0 END) counts stale flags while COUNT(*) still sees the whole team. That is the only shape that yields a real fraction.

Common follow-up questions

• What if `created` were stored with a time or timezone component? (Tests truncating a timestamp to a date before comparing it to the year boundary.)
• How would you report the stale share as a percentage instead of a fraction? (Multiply the ratio by 100, still casting to REAL first.)
• A team that owns zero flags must still appear. How do you handle it? (COUNT(*) is 0, so the ratio divides by zero; source owners from a teams table with a LEFT JOIN and guard with NULLIF and COALESCE.)