# Loaded Dice > Every flag's rollout is a bet. Map the odds from longest shot to favorite. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L5 ## Problem An A/B testing platform picks which feature flag to show by treating each flag's rollout percentage as its weight, so a flag with no rollout set is out of the running. For every flag still in play, report its selection probability (its share of the total rollout) and a cumulative probability that adds up those shares starting from the smallest rollout and climbing to the largest. ## Worked solution and explanation ### What this really is Strip off the feature-flag costume and this is a discrete probability distribution over the flags that are actually in play. Flags with no rollout weight are not candidates, so the quiet first move is dropping them. Over what remains you need two aggregates on the exact same rows: one grand total of every weight to act as a shared denominator, and one running subtotal walked in weight order to trace the cumulative curve. Anyone can divide two columns. What separates candidates is computing the cumulative from probabilities, not from raw weights, and reusing a single global total as the denominator instead of recomputing it per row. Get the denominator wrong, or leave the no-rollout flags in it, and your probabilities sum to something other than 1.0, so the cumulative column never lands on 1.0 at the last row and any downstream random pick is silently biased. > **Two windows, one denominator** > > The whole query hangs on one number: SUM(rollout) OVER () with an empty frame is the total across every flag still in play, evaluated once and reused in both output columns. Filter out the flags with no rollout first so they cannot dilute that total. Probability is rollout over the total. Cumulative probability is a running SUM(rollout) in weight order, divided by that same total. No CTE, no self join, no second pass. --- ### Build it in three moves #### Step 1: Get the grand total of the candidates Restrict to flags that have a rollout weight, then take SUM(rollout) OVER () with no ORDER BY and no partition. It collapses to a single scalar: the sum across the surviving flags. That is your denominator. Computing it as a window here rather than a scalar subquery lets the planner touch the rows a single time. #### Step 2: Turn each weight into a share Divide each flag's rollout by that grand total to get its selection probability. Cast to REAL first, otherwise integer division truncates every share to 0. Across all candidate flags these probabilities now sum to exactly 1.0. #### Step 3: Accumulate the shares in weight order The cumulative column is a second window: SUM(rollout) ordered by rollout, framed from UNBOUNDED PRECEDING to CURRENT ROW, then divided by the same grand total. flag_id breaks ties so the walk order is deterministic. Because you divide the running weight sum by the total, the last row lands exactly on 1.0. Order matters: the same probabilities in a different order give a different curve. --- ### The solution **Probability and cumulative distribution via window functions** ```sql SELECT flag_id, flag_name, rollout, CAST(rollout AS REAL) / SUM(rollout) OVER () AS probability, SUM(CAST(rollout AS REAL)) OVER ( ORDER BY rollout, flag_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) / SUM(rollout) OVER () AS cumulative_prob FROM feat_flags WHERE rollout IS NOT NULL ORDER BY rollout, flag_id ``` **Running sum of weights** SUM(rollout) OVER (ORDER BY rollout) on its own climbs to the grand total of every weight, not 1.0. It is a cumulative count of weight, not a probability, and a random draw in [0,1) can never index into it. **Running sum of probabilities** Dividing that running weight sum by the grand total rescales the curve into [0,1] and ends exactly at 1.0 on the last row. Now a uniform random draw between 0 and 1 falls into exactly one flag's band. > **Interviewers watch for** > > Three tells. First, the filter: strong candidates drop the flags with no rollout before summing, so they never pollute the denominator; weaker ones sum over everything and watch the probabilities fall short of 1.0. Second, the shared denominator: evaluate SUM(rollout) OVER () once and reference it in both columns rather than recomputing it or wrapping the query in an extra CTE. Third, a deterministic order: ordering the running sum by weight alone leaves ties unbroken, so the cumulative bands jitter between runs. Adding flag_id nails the order down. > **Cost at scale** > > Both windows read the same filtered set of candidate flags. The empty-frame total is a single pass, and the ordered running sum costs one sort on rollout. That is O(n log n) dominated by the sort, with no self join and no correlated subquery, so it stays cheap into the millions of rows. --- ## Common follow-up questions - How would you draw a random flag from this distribution? _(Generate a uniform value in [0,1) and return the first flag whose cumulative_prob is at least that value.)_ - What breaks if you drop the flag_id tie-breaker on the running sum? _(Ties in rollout leave the running-sum order undefined, so the cumulative bands shift between runs even though the final 1.0 stays stable.)_ - How would you rewrite this without nesting SUM() OVER () inside another expression? _(Stage the grand total in a CTE, then cross join it and divide, for engines that reject a window function nested inside another expression.)_ - What is the difference between a flag with rollout 0 and a flag with no rollout set? _(A rollout of exactly 0 is a candidate but gets probability 0 and a cumulative value equal to the previous row's, forming a zero-width band no random draw can land in; a NULL rollout is excluded entirely by the filter.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/loaded_dice) - [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.