# The Green Light

> Success hides behind a dozen spellings. Find who actually shipped.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The release team wants the roster of engineers who have landed at least one successful deployment. Return one entry per author, skipping any record with a blank author.

## Worked solution and explanation

### What this problem really is

Strip the deploy-audit costume and this is a case-folded DISTINCT over a filtered set. Anyone can write WHERE status = 'success' GROUP BY author. The trap is that both status and author are dirty: the seed carries 'success' and 'Success', and the same human shows up as 'Alice', 'alice', and 'BOB'/'bob'. Match status on the raw literal and you silently drop eve's 'Success' row; group on the raw author and you report 'Alice' and 'alice' as two different engineers. The whole problem is folding case on BOTH columns before you compare and before you dedupe.

---

### Build it up

#### Step 1: Filter to successful deploys

Keep only successful deploys. status is case-mixed in the seed ('success', 'Success'), so compare on LOWER(status) = 'success' to catch every spelling instead of the one that happens to match your literal.

#### Step 2: Exclude empty or NULL authors

The prose says skip blank authors. A NULL author and a whitespace-only author both count as blank, so require author IS NOT NULL AND TRIM(author) <> '' to drop them.

#### Step 3: Deduplicate authors on folded case

The same engineer appears under different casings ('Alice'/'alice', 'BOB'/'bob'). Collapse them by grouping on LOWER(author) so each person is returned exactly once, and project that folded value so the output is consistent.

#### Step 4: Sort by author

The expected preview is alphabetical, so finish with ORDER BY the folded author to make the result deterministic.

---

### The solution

**Case-folded distinct authors of successful deploys**

```sql
SELECT LOWER(author) AS author
FROM deploy_logs
WHERE LOWER(status) = 'success'
  AND author IS NOT NULL
  AND TRIM(author) <> ''
GROUP BY LOWER(author)
ORDER BY author
```

> **Trick to solving**
>
> The one line that cracks this is folding case in TWO places, not one: LOWER(status) in the WHERE and LOWER(author) in the GROUP BY. Fixing only the filter still double-counts people; fixing only the grouping still misses the 'Success' rows.

> **Interviewers watch for**
>
> A candidate who normalizes status but forgets to normalize author (or vice versa) is the tell. Strong candidates say out loud that dirty text columns need folding on every column they touch, then handle both.

> **Common pitfall**
>
> Grouping on the raw author reports 'Alice' and 'alice' as two engineers, inflating the roster. This is the exact bug that turns a headcount report into nonsense in production, and it passes a naive eyeball check because both rows look plausible.

> **Performance insight**
>
> At 200K rows with only about 60 distinct authors, this is a single sequential scan feeding a small aggregate. The LOWER() calls are per-row CPU but there is no join and no sort blow-up, so the plan stays cheap even without an index.

---

## Common follow-up questions

- If this roster is queried constantly, would you store a normalized author column or a functional index on LOWER(author) instead of folding at query time? _(Tests whether they push normalization to write time instead of every read.)_
- How would your answer change if authors also had trailing whitespace, like 'alice ' versus 'alice'? _(Tests awareness that trimming and casing are separate normalization axes.)_
- If deploy_logs were partitioned by deploy_at, would this query prune partitions or scan them all, and how would you confirm it? _(Tests understanding of partition pruning versus a full scan.)_
- Could you get the same distinct roster with SELECT DISTINCT instead of GROUP BY? When would you prefer one over the other? _(Tests knowledge of alternatives to GROUP BY for deduplication.)_

## Related

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