# The Switchboard

> On or off. Every flag at a glance.

Canonical URL: <https://datadriven.io/problems/the_switchboard>

Domain: SQL · Difficulty: medium · Seniority: L3

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

## 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

**Current on/off state per flag**

```sql
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
ORDER BY flag_name;
```

> **Trick to solving**
>
> Because enabled is guaranteed to be exactly 0 or 1, the disabled indicator is just its arithmetic complement, 1 - enabled, with no CASE expression required. The moment enabled could be NULL, that shortcut breaks and you fall back to explicit CASE WHEN logic.

> **Interviewers watch for**
>
> The tell is whether you ask about the grain before writing SQL. A candidate who says 'feat_flags looks like a history table, do you want the current state per flag?' has already shown they think about output shape first. One who selects enabled straight from the table has missed that flags repeat.

> **Common pitfall**
>
> Skipping the dedup and selecting directly from feat_flags returns one row per historical toggle, so a flag flipped three times appears three times with conflicting indicators. The second trap is an unstable tiebreaker: ordering only by updated leaves same-timestamp toggles non-deterministic across runs.

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

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_switchboard)
- [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.