# Deployment Duration by Status

> Fast deploys versus slow ones. By outcome.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

During an incident retro, the SRE lead asked how much wall-clock time each service spends deploying, split by outcome. Show the total deployment duration in seconds for every service-status combination, listed alphabetically by service.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-column GROUP BY with SUM aggregation and a specific sort order. It verifies that you can group by two dimensions and sort as specified.

---

### Break down the requirements

#### Step 1: Group by service and status

`GROUP BY svc_name, status` with `SUM(dur_secs)` for total duration.

#### Step 2: Sort alphabetically by service

`ORDER BY svc_name` as specified.

---

### The solution

**Two-dimensional aggregation**

```sql
SELECT svc_name, status, SUM(dur_secs) AS total_duration
FROM deploy_logs
GROUP BY svc_name, status
ORDER BY svc_name
```

> **Cost Analysis**
>
> Scan of 500K rows. The GROUP BY produces (services x statuses) rows. Trivially fast.

> **Interviewers Watch For**
>
> Whether the candidate groups by both dimensions. Grouping by only svc_name would lose the status breakdown.

> **Common Pitfall**
>
> Adding status to ORDER BY when not specified could change the expected output order. Follow the prompt's sort specification exactly.

---

## Common follow-up questions

- How would you pivot this so each status is a column? _(Tests CASE WHEN pivot pattern.)_
- How would you also show the percentage of total duration per service that each status represents? _(Tests window function: SUM(dur_secs) OVER (PARTITION BY svc_name) for the denominator.)_
- What if dur_secs is NULL for some deployments? _(SUM ignores NULLs. Tests whether NULL rows should be flagged.)_

## Related

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