Live Wire

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

Netflix

Problem

Every change to a feature flag, a handoff to another team included, lands in feat_flags as a new row, so a single flag shows up many times across its history. Show each flag as it stands now: the team that owns it, whether it is switched on, and when it last changed, most recently changed flag first.

The last switch each team flipped.

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

This is a latest-version-per-record problem dressed up as a feature-flag report. feat_flags is an append-only change log: flag_id names one change, flag_name names the flag, and a handoff or a toggle is just another row. The skill being probed: can you collapse each flag's history to the one row that is current, and read everything about the flag off that row? Anyone can find MAX(updated) per flag. The trap is attaching owner and enabled to that max. Group by them too and you get one row per owner the flag ever had; leave them bare and you get values from a row you never chose. Either way you report that 'new_dashboard' still belongs to a team that handed it off long ago.

How to get there

Step 1: Decide what identifies a record

flag_id is unique on every row, so partitioning by it puts each change in its own group and keeps all 200 rows. The flag is flag_name, and that is the partition key.

Step 2: Number each flag's versions newest first

ROW_NUMBER() OVER (PARTITION BY flag_name ORDER BY updated DESC, flag_id DESC). updated is ISO text, so a descending string sort puts the newest first. The flag_id key settles two changes to one flag on the same day, so the pick stays deterministic when dates collide.

Step 3: Keep version one and read the flag off it

Filter version_rank = 1, project flag_name, owner, enabled and updated as last_changed, and sort by last_changed descending. Every column comes from the same physical row, so owner and toggle are the ones that held at the last change.

The solution

WITH versions AS (
    SELECT
        flag_name,
        owner,
        enabled,
        updated,
        ROW_NUMBER() OVER (
            PARTITION BY flag_name
            ORDER BY updated DESC, flag_id DESC
        ) AS version_rank
    FROM feat_flags
)
SELECT
    flag_name,
    owner,
    enabled,
    updated AS last_changed
FROM versions
WHERE version_rank = 1
ORDER BY last_changed DESC

SELECT
    flag_name,
    owner,
    enabled,
    MAX(updated) AS last_changed
FROM feat_flags
GROUP BY flag_name, owner, enabled

Naive: aggregate the date

GROUP BY flag_name, owner, enabled with MAX(updated). Each flag returns once per owner it ever had, each row with that combination's newest date.

Correct: isolate the current row

ROW_NUMBER() partitioned by flag_name and ordered newest first, then keep version_rank = 1. One row per flag, every column from the latest change.

Common follow-up questions

• How would you show each flag as it stood at the end of last quarter? (Filter `updated` to the cutoff inside the CTE, so only versions that existed then are numbered.)
• What happens if two changes to one flag share the same `updated` date? (The `flag_id DESC` key breaks the tie, so each flag still yields exactly one row.)
• How would you list only the flags that are switched on right now? (Filter `enabled = 1` after keeping version one, never before, or a flag that was later switched off resurfaces.)