Love and Loathing

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
hard
Seniority
L5
Asked in
Meta

Problem

We run a feature-flag platform where every flag has an owner team, an on/off state, and a rollout percentage. Line up the enabled flags from the highest rollout down and the disabled flags from the lowest rollout up, then pair them off by position: the top enabled flag with the top disabled flag, the second with the second, and so on. Break ties within each side by the lower flag ID first, and return each pair's two owner teams.

Some teams champion the flag. Others would kill it.

Tables

feat_flags

Columns: flag_id INTEGER, flag_name TEXT, enabled INTEGER, rollout REAL, owner TEXT, created TEXT, updated TEXT

feat_flags: 10 of 200 rows
flag_idflag_nameenabledrolloutownercreatedupdated
131new_checkout0NULLengineering2025-02-022026-02-02
162beta_search0NULLgrowth2025-03-032026-03-03
193ai_recs139platform2025-04-042026-04-04
224live_chat0NULLmobile2025-05-052026-05-05
255video_upload0NULLdata2025-06-062026-06-06
286two_factor178product2025-07-072026-07-07
317bulk_export0NULLengineering2025-08-082026-08-08
348new_dashboard0NULLgrowth2025-09-092026-09-09
379mobile_v2117platform2025-10-102026-01-10
410dark_mode0NULLmobile2025-11-112026-02-11

Expected output

First 5 of 66 rows
fan_owneropponent_owner
platformengineering
platformgrowth
platformmobile
productengineering
productgrowth

Worked solution and explanation

Why this problem exists in real interviews

This is a positional zip of two oppositely-sorted groups that share no natural join key. Nothing in the data links an enabled flag to its opposite-rank disabled counterpart, so you have to manufacture the key yourself with ROW_NUMBER() over each filtered slice and then equi-join on that position. The trap: reach for RANK or DENSE_RANK and the moment two flags tie on rollout they collect the same rank, the position join goes many-to-many, and one clean pair explodes into a grid of junk rows. Get it wrong and the top fan lands against the wrong detractor, or the output Cartesian-multiplies.

Break down the requirements

Step 1: Split the universe in two

Two CTEs over feat_flags, one with WHERE enabled = 1, one with WHERE enabled = 0. Same table, two filtered slices. Don't try to do this in a single window with PARTITION BY enabled, you'd still need to align positions across partitions.

Step 2: Rank each side oppositely

Fans: ROW_NUMBER() OVER (ORDER BY rollout DESC, flag_id ASC). Detractors: ROW_NUMBER() OVER (ORDER BY rollout ASC, flag_id ASC). The flag_id ASC tiebreaker is required, not optional, since rollout is not unique.

Step 3: Zip on the rank

INNER JOIN ... ON f.rn = o.rn. The join key is the position, not any business column. This is the move people miss: rank becomes a synthetic primary key for alignment.

Step 4: Order the output

Final ORDER BY f.rn so pair #1 is on top. Without it the planner may surface pairs in hash-join order, which looks random to a reviewer.

The solution

POSITIONAL ZIP VIA `ROW_NUMBER`
WITH fans AS (
  SELECT owner,
         ROW_NUMBER() OVER (ORDER BY rollout DESC, flag_id ASC) AS rn
  FROM feat_flags
  WHERE enabled = 1
),
opponents AS (
  SELECT owner,
         ROW_NUMBER() OVER (ORDER BY rollout ASC, flag_id ASC) AS rn
  FROM feat_flags
  WHERE enabled = 0
)
SELECT f.owner AS fan_owner,
       o.owner AS opponent_owner
FROM fans f
INNER JOIN opponents o ON f.rn = o.rn
ORDER BY f.rn;
`DENSE_RANK` with a 3-way tie at rollout=80
fans: (A,1) (B,1) (C,1) (D,2) detractors: (W,1) (X,1) (Y,1) (Z,2) Join on rank=1 produces 9 rows: A-W, A-X, A-Y, B-W, B-X, B-Y, C-W, C-X, C-Y. Not what you want.
`ROW_NUMBER` with `flag_id` tiebreaker
fans: (A,1) (B,2) (C,3) (D,4) detractors: (W,1) (X,2) (Y,3) (Z,4) Join on rn produces 4 rows: A-W, B-X, C-Y, D-Z. Clean 1:1 zip, deterministic across runs.

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions