# Feature Vote Winner > Users voted with their clicks. Who won? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: EY ## Problem An entry in `feat_flags` with no `rollout` figure is an abstention, and each team (`owner`) splits exactly one vote evenly across the entries it did fill in. The product council's planning review needs every flag's effective vote total as a decimal, alongside that total as a percentage of the leading flag's. ## 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 10 rows: | flag_name | effective_votes | pct_of_leader | | --- | --- | --- | | ai_recs | 0.23529411764705882 | 94.11764705882352 | | beta_search | 0.1875 | 75 | | bulk_export | 0.1764705882352941 | 70.58823529411764 | | mobile_v2 | 0.23529411764705882 | 94.11764705882352 | | two_factor | 0.25 | 100 | ## Worked solution and explanation ### What this really is This is a reciprocal-weighted tally dressed up as a feature vote. There is no ballots table: `feat_flags` is the ballot, `owner` is the voter, and a filled-in `rollout` is the mark on the paper. Anyone can sum per flag. The trick is that **the denominator and the tally must read the same filtered set**: count a team's abstentions in its ballot size and its one vote quietly becomes a fraction of one, so every total and every percentage drifts. The second trap is arithmetic. `1 / backed_flags` with two integers floors to 0 for any team with two or more backings, which is every team here. Every total collapses to zero and the percentage divides by zero. --- ### Building it #### Step 1: Isolate the backings once Put `WHERE rollout IS NOT NULL` in a `backings` CTE and read only from it afterwards. Filtering in one place is what guarantees the ballot size and the tally agree on which entries count. #### Step 2: Count each team's ballot `COUNT(*)` grouped by `owner` over `backings` gives `backed_flags`, the number a team's single vote gets divided by. #### Step 3: Join the denominator back and sum reciprocals Join `backings` to `ballot_sizes` on `owner`, then `SUM(CAST(1 AS REAL) / bs.backed_flags)` per `flag_name`. The cast makes the division fractional; each team now contributes exactly 1.0 across all its entries. #### Step 4: Measure everyone against the leader The leader's total is an aggregate over `flag_totals`, not over raw rows, so a scalar subquery `(SELECT MAX(effective_votes) FROM flag_totals)` supplies it to every row. Multiply by `100.0` so the leader reads 100. **Split ballots** ```sql WITH backings AS ( SELECT flag_name, owner FROM feat_flags WHERE rollout IS NOT NULL ), ballot_sizes AS ( SELECT owner, COUNT(*) AS backed_flags FROM backings GROUP BY owner ), flag_totals AS ( SELECT b.flag_name, SUM(CAST(1 AS REAL) / bs.backed_flags) AS effective_votes FROM backings b JOIN ballot_sizes bs ON b.owner = bs.owner GROUP BY b.flag_name ) SELECT flag_name, effective_votes, effective_votes * 100.0 / (SELECT MAX(effective_votes) FROM flag_totals) AS pct_of_leader FROM flag_totals ``` **Filter only in the tally** `ballot_sizes` built from all of `feat_flags`: a team with 30 entries and 10 backings divides by 30, handing out a third of a vote. Totals shrink unevenly by team. **Filter at the source** `ballot_sizes` built from `backings`: the same team divides by 10, its weights sum to exactly 1.0, and the flag totals add up to the number of voting teams. > **The totals must sum to the team count** > > A one-line sanity check: `SUM(effective_votes)` over `flag_totals` must equal the number of teams with at least one backing. If it comes out lower, abstentions leaked into a denominator; if it is 0, the division was integer. > **Naming the voter is the first signal** > > Strong candidates say out loud that `owner` is the voter and a filled `rollout` is the ballot mark before writing anything. The second tell is reaching for the cast without being prompted by a zero in the output. > **One scan, two tiny aggregates** > > `ballot_sizes` has one row per team and the scalar subquery reads ten summed rows, so at any realistic size the cost is a single scan of `feat_flags` plus a hash join against a handful of rows. ## Common follow-up questions - Return only the flags within 10 points of the leader. _(Moves `pct_of_leader` into a filter and checks the candidate keeps the leader computed over `flag_totals`.)_ - What if a team listed the same flag twice? _(Probes whether the candidate notices the tally counts entries, not unique flags, and how a dedup on `owner` and `flag_name` changes the weights.)_ - Rewrite the leader comparison without a subquery. _(Tests `MAX(effective_votes) OVER ()` as a window over the per-flag totals.)_ **Pipeline stages** - feat_flags (source, table) - backings (transform, rollout filled) - ballot_sizes (transform, count per team) - flag_totals (transform, sum of reciprocals) - report (consumer, share of leader) **Data flow** - feat_flags -> backings - backings -> ballot_sizes - backings -> flag_totals - ballot_sizes -> flag_totals - flag_totals -> report *`backings` feeds both the denominator and the tally, so they always agree on which entries count.* ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/feature_vote_winner) - [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.