# Disabled-Flag Share by Owner > Which teams ship everything off by default. Canonical URL: 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. ## Tables ### feat_flags - flag_id INTEGER - flag_name TEXT - enabled INTEGER - rollout REAL - owner TEXT - created TEXT - updated TEXT Sample rows (10 of 200): | 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 First 5 of 6 rows: | owner | stale_ratio | | --- | --- | | data | 0.875 | | engineering | 0.912 | | growth | 0.912 | | mobile | 0.882 | | product | 0.906 | ## 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. **Per-owner stale fraction** ```sql 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 ``` > **Integer division eats the fraction** > > `SUM(...) / COUNT(*)` is integer over integer, so SQLite returns 0 for every fraction below 1. Since no team here is fully stale or fully clean, forgetting the cast sends all six rows to 0.0. `CAST(... AS REAL)` (or multiplying by 1.0) is the single move that makes this problem pass. > **Do they cast before dividing** > > The tell is whether the candidate reaches for `CAST(... AS REAL)` unprompted. On a seed where every team is partially stale, skipping it flattens the whole ranking to 0.0, and a candidate who never inspects their own output ships that silently. **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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/disabled_flag_share_by_owner) - [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.