Feature Vote Winner

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

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.

Users voted with their clicks. Who won?

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 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. On this data only 'product' and 'platform' filled in any rollout. Skip the filter and 'engineering', 'growth', 'mobile' and 'data' each spread a whole vote over entries they never backed, so the totals sum to 6 instead of 2 and the percentages drift.

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.

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

No abstention filter

ballot_sizes and the tally read all of feat_flags: the four teams that filled in no rollout each hand out a full vote across their entries, and the flag totals sum to 6.

Filter at the source

Both read backings: only 'product' and 'platform' vote, each team's weights sum to exactly 1.0, and the flag totals sum to 2, the number of voting teams.

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.)