# Deploy Outcomes by Service

> Success, failure, rollback - side by side.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

For each service, pivot its deployment outcomes into columns: successful, failed, and rolled-back deploy counts. Status values may vary in casing. Return one row per service, ordered by service name.

## Worked solution and explanation

### Why this problem exists in real interviews

The interviewer wants to see you apply pivot and conditional aggregation to deploy_logs.svc_name while accounting for the distribution of version. This surfaces in senior-level rounds because small logic errors produce results that look correct at a glance.

> **Trick to Solving**
>
> SQL lacks a native PIVOT operator in most dialects. The trick is conditional aggregation with `CASE WHEN` inside aggregate functions.
> 
> 1. Identify the column whose values become output columns
> 2. Write `SUM(CASE WHEN col = 'val' THEN metric END)` for each pivot value
> 3. Group by the row identifier

---

### Break down the requirements

#### Step 1: Filter to the target rows

Group deploy_logs by service.

#### Step 2: Aggregate with SUM

Use conditional SUM(CASE WHEN ...) to pivot status into success, failed, and rolled_back count columns (case-insensitive on status).

#### Step 3: Pivot with CASE WHEN

Order by service name.

#### Step 4: Order the final output

Apply `ORDER BY` as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.

---

### The solution

**CASE WHEN pivot for selected months**

```sql
SELECT svc_name, SUM(CASE WHEN LOWER(status) LIKE '%success%' THEN 1 ELSE 0 END) AS success_count, SUM(CASE WHEN LOWER(status)='failed' THEN 1 ELSE 0 END) AS failed_count, SUM(CASE WHEN status='rolled_back' THEN 1 ELSE 0 END) AS rolled_back_count FROM deploy_logs GROUP BY svc_name ORDER BY svc_name
```

> **Cost Analysis**
>
> The query scans 2M rows from `deploy_logs`. The aggregation reduces the row count before any downstream processing, which is the key performance lever.

> **Interviewers Watch For**
>
> Naming the output grain ("one row per X") before writing the GROUP BY shows you think about data shape, not just syntax. Knowing that conditional aggregation replaces PIVOT in standard SQL is a strong signal of cross-dialect experience.

> **Common Pitfall**
>
> Comparing dates stored as TEXT without casting can produce lexicographic instead of chronological ordering. Always confirm the column type.

---

## Common follow-up questions

- What happens to your result if deploy_logs.dur_secs contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on dur_secs.)_
- What happens to your CASE expressions if a new category value appears in svc_name, version? _(Tests whether the candidate recognizes that hard-coded CASE values miss future categories.)_
- With millions of distinct values in deploy_logs.log_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like log_id.)_

## Related

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