The Switchboard

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

L3

Asked in

Credit Karma

Problem

A feature-flag service writes a new record every time a flag is switched on or off, so one flag can appear many times with different settings. For each flag, give its current state as two indicators: a 1/0 for whether it is enabled and a 1/0 for whether it is disabled, listed alphabetically by flag name.

On or off. Every flag at a glance.

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 problem really is

Under the 'binary indicators' costume, this is a latest-record-per-flag problem. The feat_flags table is an append-only toggle log: every on/off switch writes a fresh row, so a flag you picture as one thing is really a stack of historical settings. Turning enabled into a 1/0 pair is the easy half that everyone gets. What separates candidates is noticing they must first collapse each flag to its single current record. Skip that step and you emit one row for every toggle a flag ever had, with stale on/off values mixed into the result.

Reading the grain

Step 1: Recognize the toggle log

flag_name is not unique in feat_flags. The same flag shows up once per toggle, each with its own updated timestamp and enabled value. 'For each flag' therefore means one row per flag_name reflecting its most recent setting, not one row per raw event.

Step 2: Collapse to the current record

Number the rows within each flag using ROW_NUMBER() OVER (PARTITION BY flag_name ORDER BY updated DESC, flag_id DESC) and keep rn = 1. Ordering by updated DESC picks the newest toggle; flag_id DESC is a deterministic tiebreaker so two switches on the same day still resolve to one stable winner.

Step 3: Emit the binary pair

enabled is stored as 0 or 1, so enabled_flag is simply enabled and disabled_flag is its complement, 1 - enabled. Order the final result by flag_name so downstream consumers get a stable, predictable ordering.

The solution

WITH latest AS (
    SELECT
        flag_name,
        enabled,
        ROW_NUMBER() OVER (
            PARTITION BY flag_name
            ORDER BY updated DESC, flag_id DESC
        ) AS rn
    FROM feat_flags
)
SELECT
    flag_name,
    enabled AS enabled_flag,
    1 - enabled AS disabled_flag
FROM latest
WHERE rn = 1
;

Naive: read enabled directly

SELECT flag_name, enabled AS enabled_flag, 1 - enabled AS disabled_flag FROM feat_flags ORDER BY flag_name; returns every toggle ever recorded, duplicating flags and leaking stale states.

Correct: dedup then transform

Rank rows per flag by recency, keep rn = 1, then compute the binary pair. Exactly one current row per flag, no stale values.

Common follow-up questions

• The enabled column is guaranteed 0 or 1 today. If a third state (say NULL for 'unset') were introduced, how would you keep `enabled_flag` and `disabled_flag` correct? (Tests whether the 1 - enabled shortcut is understood as depending on a strict 0/1 domain.)
• Two toggles for the same flag share the exact same updated timestamp. Which one wins, and how do you make that deterministic? (Tests understanding that ordering on a non-unique key needs a tiebreaker to be deterministic.)
• How would you turn this into an incremental job that only recomputes flags toggled since the last run? (Tests awareness of incremental processing over an append-only log.)
• Product also wants the current rollout percentage next to each indicator. How does that change your latest-record selection? (Tests whether the current-record selection generalizes to carrying extra columns from the winning row.)