# Currently Active Feature Flags

> Which flags are live right now?

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Based on each feature flag's most recent state change, how many flags are currently enabled?

## Worked solution and explanation

### Why this problem exists in real interviews

This tests the last-value-per-group pattern. To determine the current state, you need the most recent record per flag, then count those that are enabled. This probes window functions or DISTINCT ON.

---

### Break down the requirements

#### Step 1: Find each flag's latest state

Use `DISTINCT ON (flag_id) ... ORDER BY updated DESC` or `ROW_NUMBER()` to get the most recent row per flag.

#### Step 2: Count enabled flags

Filter to `enabled = true` and COUNT(*).

---

### The solution

**Latest state per flag with count**

```sql
WITH latest AS (
    SELECT DISTINCT ON (flag_id)
        flag_id, enabled
    FROM feat_flags
    ORDER BY flag_id, updated DESC
)
SELECT COUNT(*) AS active_flags
FROM latest
WHERE enabled = true
```

> **Cost Analysis**
>
> Scan of 800 rows with DISTINCT ON sort. Trivially fast at this scale. The DISTINCT ON pattern is PostgreSQL-specific but very efficient.

> **Interviewers Watch For**
>
> Whether the candidate uses DISTINCT ON (PostgreSQL-idiomatic) vs ROW_NUMBER (portable). Both are correct. Strong candidates mention the portability trade-off.

> **Common Pitfall**
>
> Simply counting `WHERE enabled = true` without considering the latest state would overcount if flags have been toggled multiple times.

---

## Common follow-up questions

- How would you solve this without DISTINCT ON? _(ROW_NUMBER() OVER (PARTITION BY flag_id ORDER BY updated DESC) with a filter on row_num = 1.)_
- What if the table had millions of flags? _(The sort for DISTINCT ON or ROW_NUMBER scales with table size; an index on (flag_id, updated DESC) helps.)_
- How would you also show which flags are currently enabled? _(Return flag_name from the latest CTE instead of just counting.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/currently_active_feature_flags)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.