Flag Check
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
Problem
A release engineer sweeping feat_flags before the next rollout wants to know: which owners are holding flags with no rollout value on file, how many such flags does each hold, and how many of those are switched on anyway?
Which flags are actually live?
Tables
feat_flags
Columns: flag_id INTEGER, flag_name TEXT, enabled INTEGER, rollout REAL, owner TEXT, created TEXT, updated TEXT
| flag_id | flag_name | enabled | rollout | owner | created | updated |
|---|---|---|---|---|---|---|
| 131 | new_checkout | 0 | NULL | engineering | 2025-02-02 | 2026-02-02 |
| 162 | beta_search | 0 | NULL | growth | 2025-03-03 | 2026-03-03 |
| 193 | ai_recs | 1 | 39 | platform | 2025-04-04 | 2026-04-04 |
| 224 | live_chat | 0 | NULL | mobile | 2025-05-05 | 2026-05-05 |
| 255 | video_upload | 0 | NULL | data | 2025-06-06 | 2026-06-06 |
| 286 | two_factor | 1 | 78 | product | 2025-07-07 | 2026-07-07 |
| 317 | bulk_export | 0 | NULL | engineering | 2025-08-08 | 2026-08-08 |
| 348 | new_dashboard | 0 | NULL | growth | 2025-09-09 | 2026-09-09 |
| 379 | mobile_v2 | 1 | 17 | platform | 2025-10-10 | 2026-01-10 |
| 410 | dark_mode | 0 | NULL | mobile | 2025-11-11 | 2026-02-11 |
Expected output
| owner | unconfigured_flags | enabled_count |
|---|---|---|
| data | 32 | 0 |
| engineering | 34 | 0 |
| growth | 34 | 0 |
| mobile | 34 | 0 |
Worked solution and explanation
What this is really asking
This is a NULL-selection problem wearing a release-audit costume. The rollup per owner is the easy part; anyone writes GROUP BY owner. The part that separates candidates is how you reach the flags with no rollout. Every aggregate you might point at rollout skips NULLs by design, so COUNT(rollout) reports zero unconfigured flags for everyone, and rollout = NULL matches no rows at all. Either way the release team hears that nobody has half-configured flags, while most owners are sitting on dozens.
Building it
Step 1: Select the rows with nothing in rollout
The unconfigured flags exist only as NULLs in rollout, so they have to be picked out as rows before anything aggregates. WHERE rollout IS NULL does it; IS NULL is the only test that is true for a missing value.
Step 2: Roll up per owner
GROUP BY owner collapses the filtered rows to one line per owner. COUNT(*) counts rows, not values, so it counts exactly the flags the filter kept.
Step 3: Count the switched-on ones with SUM(enabled)
enabled is stored as 0 or 1, so SUM(enabled) is the number switched on. No CASE needed.
SELECT owner, COUNT(*) AS unconfigured_flags, SUM(enabled) AS enabled_count FROM feat_flags WHERE rollout IS NULL GROUP BY ownerThe filter runs first, so both counts describe the same unconfigured set.
- Counting the column
COUNT(rollout)orCOUNT(*) - COUNT(rollout)in the select list tries to measure NULLs through an aggregate. The first is always zero here; the second works but hides the intent and cannot scopeSUM(enabled)to the same flags.- Counting the rows
WHERE rollout IS NULLkeeps only the unconfigured rows, soCOUNT(*)andSUM(enabled)both describe exactly that set.
Common follow-up questions
- How would you show every owner, including those with zero unconfigured flags? (Moves the NULL test into a conditional count (`SUM(CASE WHEN rollout IS NULL THEN 1 ELSE 0 END)`) with no `WHERE`, since the filter would drop those owners.)
- What if an empty string in `rollout` also meant 'not configured'? (Tests `rollout IS NULL OR rollout = ''` and awareness of legacy encodings of missing data.)
- How would you list only owners whose unconfigured flags make up more than half their catalog? (Needs both the full count and the NULL count per owner, compared in `HAVING`, with care over integer division.)