# Failed Payment Deployments

> Payment deploys that went wrong.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The payments-api team is writing their incident postmortem and needs to quantify deployment instability. Count the number of deployments for the service named 'payments-api' that have a status of 'failed'.

## Worked solution and explanation

### What this is really asking

Two predicates on `deploy_logs`: `svc_name = 'payments-api'` AND `status = 'failed'`, then count. The 400k row count tells you to expect a scan, not that anything fancy is needed.

---

### Break down the requirements

#### Step 1: Filter to one service

Only rows where `svc_name = 'payments-api'`. String equality, case-sensitive in most engines.

#### Step 2: Filter to failed status

`status = 'failed'` on the same rows, combined with AND in one WHERE.

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

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

---

### The solution

**FAILED DEPLOYMENT COUNT**

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

> **Cost Analysis**
>
> Full scan of 400k rows unless a composite index on (svc_name, status) exists. Either predicate alone is low-selectivity; the AND is what makes it cheap with the right index.

> **Interviewers Watch For**
>
> Aliasing the count, putting both filters in one WHERE rather than nesting a subquery, and not reaching for COUNT(status) which invites a NULL tangent.

> **Common Pitfall**
>
> `status LIKE 'fail%'` looks robust but catches 'failing' or 'failover' if those values land in the column. Equality is the contract; honor it.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What is the failure rate for payments-api? _(Wrap the count in a ratio against total payments-api deploys using conditional aggregation.)_
- Which author shipped the most failed deploys? _(Add `author` to GROUP BY with the same 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 pattern.)_

## Related

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