# Live Wire > The last switch each team flipped. Canonical URL: 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. ## Tables ### feat_flags - flag_id INTEGER - flag_name TEXT - enabled INTEGER - rollout REAL - owner TEXT - created TEXT - updated TEXT Sample rows (10 of 200): | flag_id | flag_name | enabled | rollout | owner | created | updated | | --- | --- | --- | --- | --- | --- | --- | | 131 | new_checkout | 0 | NULL | engineering | 2025-02-02 | 2026-02-02 | | 162 | beta_search | 0 | NULL | growth | 2025-03-03 | 2026-03-03 | | 193 | ai_recs | 1 | 39 | platform | 2025-04-04 | 2026-04-04 | | 224 | live_chat | 0 | NULL | mobile | 2025-05-05 | 2026-05-05 | | 255 | video_upload | 0 | NULL | data | 2025-06-06 | 2026-06-06 | | 286 | two_factor | 1 | 78 | product | 2025-07-07 | 2026-07-07 | | 317 | bulk_export | 0 | NULL | engineering | 2025-08-08 | 2026-08-08 | | 348 | new_dashboard | 0 | NULL | growth | 2025-09-09 | 2026-09-09 | | 379 | mobile_v2 | 1 | 17 | platform | 2025-10-10 | 2026-01-10 | | 410 | dark_mode | 0 | NULL | mobile | 2025-11-11 | 2026-02-11 | ## Expected output First 5 of 10 rows: | flag_name | owner | enabled | last_changed | | --- | --- | --- | --- | | new_dashboard | growth | 0 | 2026-09-09 | | video_upload | data | 0 | 2026-09-08 | | beta_search | growth | 0 | 2026-09-07 | | dark_mode | product | 1 | 2026-09-07 | | bulk_export | data | 0 | 2026-09-06 | ## 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. > **Grouping by every column brings the history back** > > `SELECT flag_name, owner, enabled, MAX(updated) ... GROUP BY flag_name, owner, enabled` compiles everywhere, which is why it tempts. But the grain is now one row per owner-and-state combination the flag ever passed through, so every flag comes back several times. It looks like current state and is really a compressed history. --- ### 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 **Current version of each flag** ```sql 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 ``` **The tempting version that returns history** ```sql SELECT flag_name, owner, enabled, MAX(updated) AS last_changed FROM feat_flags GROUP BY flag_name, owner, enabled ``` *Grouping by `owner` and `enabled` splits each flag into one row per past combination.* **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. > **Name the record key out loud** > > The strongest signal is saying up front that `flag_id` identifies a change and `flag_name` identifies the flag, then asking how a same-day tie breaks. A window with a tie-break and a correlated latest-row subquery both work; knowing why `MAX` alone cannot carry the other columns is what matters. > **One scan, one sort per flag** > > On the 200 seed rows this is one scan plus a sort inside each of ten flag partitions. On a real log with millions of versions, an index on `(flag_name, updated DESC, flag_id DESC)` lets the engine read each flag's newest row without sorting its history, and teams often materialize the result as a current-state table. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/live_wire) - [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.