# Deployments per Environment

> Dev, staging, prod. Where do most deploys land?

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

Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: ESPN

## Problem

Staging has been overloaded lately and the team suspects it's getting more deploys than production. Show the deployment count for each environment so they can confirm.

## Tables

### deploy_logs

- log_id INTEGER
- svc_name TEXT
- version TEXT
- env_name TEXT
- status TEXT
- deploy_at TEXT
- dur_secs INTEGER
- author TEXT

Sample rows (10 of 200):

| log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob |
| 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie |
| 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana |
| 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve |
| 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank |
| 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice |
| 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB |
| 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice |
| 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob |
| 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie |

## Expected output

First 5 of 7 rows:

| env_name | deploy_count |
| --- | --- |
| Dev | 1 |
| Production | 34 |
| STAGING | 32 |
| canary | 34 |
| dev | 33 |

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic GROUP BY with COUNT. It tests the simplest form of aggregation for comparing groups.

---

### Break down the requirements

#### Step 1: Group by environment

`GROUP BY env_name` with `COUNT(*)` for deployment count.

---

### The solution

**Simple environment count**

```sql
SELECT env_name, COUNT(*) AS deploy_count
FROM deploy_logs
GROUP BY env_name
```

> **Cost Analysis**
>
> Scan of 700K rows. Trivially fast. A handful of environments in the output.

> **Interviewers Watch For**
>
> Whether the candidate keeps the query minimal. No sorting or filtering is required by the prompt.

> **Common Pitfall**
>
> Adding ORDER BY or HAVING when not requested adds unnecessary complexity. Read the prompt for exact output requirements.

---

## Common follow-up questions

- How would you confirm whether staging has more deploys than production? _(Add ORDER BY `deploy_count` DESC or a WHERE filter.)_
- How would you show this as a percentage of total deploys? _(Tests 100.0 * COUNT(*) / SUM(COUNT(*)) OVER ().)_
- What if some deployments have NULL `env_name`? _(They form a NULL group. Tests whether to exclude or label them.)_

## Related

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