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 lands in feat_flags as a new row, so a single flag shows up many times across its history. For each owning team, find the flag they touched most recently and when that change landed, with the most recently active team 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 most-recent-row-per-group problem wearing a feature-flag costume. Each edit appends a new row, so one flag shows up many times and feat_flags is a change log, not a snapshot. The skill being probed: can you pull each team's single latest row and carry its flag_name along, not just the newest timestamp? Anyone can write MAX(updated) per owner. The trap is pairing that max date with a bare flag_name in the same GROUP BY. That either fails to compile or hands you a name you cannot vouch for, so you report the right date next to a flag you cannot defend. Get it wrong and you tell the platform team its newest change was to a flag it actually touched months ago.

How to get there

Step 1: Number each team's rows by recency

Number the rows within each owner by recency: ROW_NUMBER() OVER (PARTITION BY owner ORDER BY updated DESC, flag_id DESC). The row with rn = 1 is that team's most recent change. Because you keep the whole row, flag_name and updated stay attached. The flag_id tie-break makes the pick deterministic if two changes ever share a date.

Step 2: Keep the latest row and order by recency

Keep only rn = 1, then project owner, flag_name and the timestamp as last_changed. Sort by last_changed descending so the team that touched a flag most recently sits at the top. No grouping is needed: the window has already isolated one row per team.

The solution

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

Naive: aggregate one column

SELECT owner, flag_name, MAX(updated) FROM feat_flags GROUP BY owner. The MAX finds the newest timestamp, but flag_name is not bound to that row. Depending on the engine you get an error or a name you cannot justify.

Correct: isolate the whole row

ROW_NUMBER() partitions by owner and orders by recency, then you keep rn = 1. flag_name and updated travel together from the same physical row, so the pairing is always honest.

Common follow-up questions

• How would you show each team's three most recent changes, not just the latest one? (Keep the same window and filter `rn <= 3` instead of `rn = 1`.)
• What happens if two changes for a team share the same `updated` date? (The `flag_id DESC` tie-break in the window is what guarantees exactly one row per team.)
• How would you include teams that currently own no flags at all? (Left-join a full team list against the per-team latest rows and leave `flag_name` and the date empty for teams with no match.)