# Both Ends of the Pipe

> Dev and prod, both touched by the same hand. Who are they?

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The release engineering team wants the authors who carry a build all the way from dev into production, treating the same environment name in different casing as one. List each qualifying author with how many of those two environments they deployed to, alphabetically.

## Worked solution and explanation

### What this is really asking

This is a two-environment set-membership test wearing a deploy-log costume. `LOWER(env_name)` does double duty: it filters 500k rows down to the 'dev' and 'production' variants, and it deduplicates inside COUNT DISTINCT. Skip the fold and every 'Dev' or 'PRODUCTION' row silently drops out, quietly disqualifying people who actually shipped to both.

---

### Break down the requirements

#### Step 1: Case-insensitive filter

Normalize env_name with LOWER and keep only the two target values. A raw comparison misses 'Dev' or 'PRODUCTION' rows and hands you a wrong roster before you even aggregate.

#### Step 2: Require BOTH envs per author

GROUP BY author, then HAVING COUNT(DISTINCT LOWER(env_name)) = 2. The DISTINCT stops an author with 50 dev deploys and zero prod deploys from passing on sheer volume.

#### Step 3: Report and sort

Return author and env_count (always 2 after HAVING), alphabetized. Group on the raw author column so 'Alice' and 'alice' stay separate.

---

### The solution

**BOTH ENDS OF THE PIPE**

```sql
SELECT author,
       COUNT(DISTINCT LOWER(env_name)) AS env_count
FROM deploy_logs
WHERE LOWER(env_name) IN ('dev', 'production')
GROUP BY author
HAVING COUNT(DISTINCT LOWER(env_name)) = 2
ORDER BY author;
```

> **Cost Analysis**
>
> 500k rows is small, but LOWER(env_name) in the WHERE clause kills any plain index on env_name. For an hourly job, add a functional index on LOWER(env_name).

> **Interviewers Watch For**
>
> Whether you reach for LOWER unprompted, and whether you use COUNT DISTINCT vs COUNT(*). Bonus for noting env_count is always 2 post-HAVING, and for keeping the author column unfolded on purpose.

> **Common Pitfall**
>
> HAVING COUNT(*) = 2 passes a clean fixture and breaks the first time someone deploys to dev twice and never touches prod. Reach for DISTINCT whenever you mean 'how many different environments'.

> **The False Start**
>
> First instinct is WHERE env_name IN ('dev','production') with HAVING COUNT(*) = 2. That misses 'Dev' rows and lets two dev deploys falsely qualify. Pivot to LOWER() in the filter and COUNT DISTINCT inside HAVING.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Extend this to dev, staging, AND production? _(Swap the IN list to three values and HAVING to = 3. The shape generalizes cleanly.)_
- What if env_name has trailing whitespace from a CSV ingest? _(Wrap in TRIM(LOWER(env_name)). Better: clean at ingest so downstream queries stop paying the cost.)_
- Find authors who deployed to dev but NOT production? _(Conditional aggregation: HAVING SUM(CASE WHEN LOWER(env_name)='dev' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN LOWER(env_name)='production' THEN 1 ELSE 0 END) = 0.)_

## Related

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