# Rollback Roulette

> Some ships sink before they leave the harbor.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The platform team is auditing release stability for the payment-api service before its next on-call rotation. Deployment outcomes are logged in deploy_logs, but the status column was populated by several different CI tools over the years, so the same outcome appears in mixed casing (for example 'FAILED' and 'failed'). Count how many deployments of the service named 'payment-api' ended with a failed status. Treat status values case-insensitively so no failed deploy is missed.

## Worked solution and explanation

### What this is really asking

Two predicates on `deploy_logs`, both case-insensitive: `LOWER(svc_name) = 'payment-api'` AND `LOWER(status) = 'failed'`, then count. The mixed casing from different CI tools is the whole twist; a naive `status = 'failed'` would silently miss the rows logged as 'FAILED'.

---

### Break down the requirements

#### Step 1: Filter to one service

Only rows for the `payment-api` service. Because the data was written by several tools, normalize with `LOWER(svc_name) = 'payment-api'` rather than an exact-case match.

#### Step 2: Filter to failed status

A failed outcome on the same rows, combined with AND in one WHERE. Use `LOWER(status) = 'failed'` so 'FAILED', 'Failed', and 'failed' all count.

#### Step 3: Count, do not sum

`COUNT(*)` over the filtered set, aliased to `failed_deployments` so the on-call audit can quote a named column.

---

### The solution

**FAILED DEPLOYMENT COUNT**

```sql
SELECT COUNT(*) AS failed_deployments
FROM deploy_logs
WHERE LOWER(status) = 'failed'
  AND LOWER(svc_name) = 'payment-api'
```

> **Cost Analysis**
>
> Wrapping `status` and `svc_name` in `LOWER()` makes both predicates non-sargable, so a plain B-tree index on those columns cannot be used and you fall back to a scan. If this query ran hot, an expression index on `LOWER(svc_name), LOWER(status)` (or a normalized stored column) would restore index access.

> **Interviewers Watch For**
>
> Recognizing the mixed-casing trap and reaching for case-insensitive comparison on BOTH columns, aliasing the count, keeping both filters in one WHERE rather than nesting a subquery, and not reaching for COUNT(status) which invites a NULL tangent.

> **Common Pitfall**
>
> Matching only `status = 'failed'` looks correct but silently drops every row a different CI tool wrote as 'FAILED' or 'Failed', undercounting the failures. Normalize with LOWER() (or UPPER()) on both sides before comparing.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What is the failure rate for payment-api? _(Wrap the count in a ratio against total payment-api deploys using conditional aggregation, still lowercasing status and svc_name.)_
- Which author shipped the most failed deploys? _(Add `author` to GROUP BY with the same case-insensitive WHERE, then ORDER BY count DESC LIMIT 1.)_
- How would you split failures by env_name? _(Group by `env_name` to see whether prod, staging, or canary dominates the failure pattern.)_

## Related

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