# Deploy Cadence

> Which environments ship the most?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The platform engineering team is benchmarking deployment frequency across environments and wants a breakdown. For each environment, show the number of deployments, the average deployment duration in seconds, and the number of unique services that have been deployed there. Present environments from the highest deployment tally to the lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-metric GROUP BY with COUNT, AVG, and COUNT(DISTINCT). It verifies that you can compute multiple aggregates in a single pass.

---

### Break down the requirements

#### Step 1: Group by environment

`GROUP BY env_name` produces one row per environment.

#### Step 2: Compute three metrics

`COUNT(*)` for deployments, `AVG(dur_secs)` for average duration, `COUNT(DISTINCT svc_name)` for unique services.

#### Step 3: Sort by count descending

Highest deployment tally first.

---

### The solution

**Multi-metric environment summary**

```sql
SELECT
    env_name,
    COUNT(*) AS deploy_count,
    AVG(dur_secs) AS avg_duration,
    COUNT(DISTINCT svc_name) AS unique_services
FROM deploy_logs
GROUP BY env_name
ORDER BY deploy_count DESC
```

> **Cost Analysis**
>
> Scan of 5M rows. Low cardinality GROUP BY (handful of environments). Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate uses three different aggregate functions correctly in one query. Mixing COUNT(*) with COUNT(DISTINCT) tests understanding of the DISTINCT keyword within aggregates.

> **Common Pitfall**
>
> Using COUNT(svc_name) instead of COUNT(DISTINCT svc_name) would count total deploy entries per service, not unique services.

---

## Common follow-up questions

- How would you add the most recent deploy date per environment? _(Add MAX(deploy_at).)_
- How would you identify environments with below-average deployment frequency? _(Compare each environment's count to the overall average using a HAVING clause.)_
- What if you needed monthly trends per environment? _(Add DATE_TRUNC('month', deploy_at) to the GROUP BY.)_

## Related

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