# Flag Check > Which flags are actually live? Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Release engineering is auditing the feature flag system before a major rollout. They want to see the overall adoption rate of enabled flags, broken down by owner. For each flag owner, show how many flags they own, the number that are currently enabled, and the average rollout percentage across their flags. Skip any flags with no rollout value on file. Present owners from the highest number of enabled flags to the lowest. ## Worked solution and explanation ### Why this problem exists in real interviews Feature flag platforms have noisy ownership. Interviewers use this prompt to see whether you can produce a per owner rollup that distinguishes total flags from currently enabled flags, treats the rollout column as a numeric average, and applies the right NULL filter so partially configured flags do not skew the rollout average. --- ### Break down the requirements #### Step 1: Filter rows that have a real rollout value The scope says rollout IS NOT NULL, so this belongs in the WHERE clause before aggregation. Filtering after the GROUP BY (in HAVING) would still work but it scans more rows and changes the AVG semantics if any owner only has NULL rollouts. #### Step 2: Aggregate three signals per owner in one pass GROUP BY owner and project COUNT(*) AS total_flags, SUM(enabled) AS enabled_count, and AVG(rollout) AS avg_rollout. Because enabled is stored as INTEGER (0 or 1), SUM(enabled) is the count of enabled flags without needing a CASE expression. #### Step 3: Sort by enabled_count descending, owner ascending Ties on enabled_count must resolve alphabetically by owner. Specify ORDER BY enabled_count DESC, owner ASC so the result is deterministic across SQLite runs. --- ### The solution **Single GROUP BY pass with the NULL filter pushed down** ```sql SELECT owner, COUNT(*) AS total_flags, SUM(enabled) AS enabled_count, AVG(rollout) AS avg_rollout FROM feat_flags WHERE rollout IS NOT NULL GROUP BY owner ORDER BY enabled_count DESC, owner ASC ``` > **Cost Analysis** > > feat_flags has about 10,000 rows, so a single hash aggregate on owner is trivial. The dominant cost is reading the table once. Pushing rollout IS NOT NULL into WHERE skips rows before they enter the aggregate state and keeps AVG(rollout) honest. No index is needed at this size. > **Interviewers Watch For** > > They want to see SUM(enabled) instead of SUM(CASE WHEN enabled = 1 THEN 1 END), since the column is already 0/1. They also check whether you push the NULL filter into WHERE rather than HAVING and whether your tie-break on owner is explicit. > **Common Pitfall** > > Forgetting the rollout IS NOT NULL filter pulls owners whose flags have no rollout target into the result, and AVG silently ignores NULLs so the row counts and rollout averages disagree. Sorting by total_flags by mistake also produces a different leaderboard than the one the prompt asks for. --- ## Common follow-up questions - How would the answer change if rollout were stored as an integer percentage from 0 to 100 instead of a DOUBLE between 0 and 1? _(Tests whether the candidate spots that AVG over INTEGER columns still returns DOUBLE in SQLite, but readers may misread the scale. A CAST or a /100.0 normalization keeps the output in a single unit.)_ - How would you extend this to show only owners whose enabled_count is at least 50 percent of their total_flags? _(Forces a HAVING clause referencing two aggregates, plus the integer-division trap on SUM(enabled) * 1.0 / COUNT(*) >= 0.5.)_ - What changes if owner can be NULL? _(GROUP BY owner would produce a NULL bucket. The candidate should mention either filtering owner IS NOT NULL or COALESCE(owner, '(unowned)') depending on whether unowned flags should appear in the leaderboard.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/flag_check) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.