# Slow Production Deploys

> Production deploys that took way too long.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: LinkedIn

## Problem

We're auditing every deployment to the production environment that ran long, meaning it took more than 150 seconds, whatever its final status. Environments are labeled by hand, so treat only the exact tag 'production' as the real production line and ignore any differently-cased variants. Return each such deployment's service, version, duration in seconds, and deploy timestamp, longest-running first.

## Tables

### deploy_logs

- log_id INTEGER
- svc_name TEXT
- version TEXT
- env_name TEXT
- status TEXT
- deploy_at TEXT
- dur_secs INTEGER
- author TEXT

Sample rows (10 of 200):

| log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob |
| 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie |
| 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana |
| 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve |
| 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank |
| 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice |
| 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB |
| 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice |
| 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob |
| 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie |

## Expected output

First 5 of 20 rows:

| svc_name | version | dur_secs | deploy_at |
| --- | --- | --- | --- |
| user-svc | v1.1.0 | 580 | 2026-01-07 18:30:00 |
| user-svc | v1.1.0 | 580 | 2026-09-07 18:30:00 |
| gateway | v2.1.0 | 478 | 2022-12-09 12:36:00 |
| analytics | v3.0.0-rc1 | 424 | 2024-06-23 06:18:00 |
| auth-svc | v1.0.1 | 346 | 2023-12-17 00:12:00 |

## Worked solution and explanation

### Why this problem exists in real interviews

Interviewers use this deployment scenario to test filtering and projection against the `deploy_logs` table. The focus is on how you handle columns like `svc_name`, `version`, and `env_name` when building the result.

---

### Break down the requirements

#### Step 1: Apply the range filter

The WHERE clause restricts rows to the target range. Applying this filter early reduces the volume flowing into downstream operations.

#### Step 2: Sort the final output

The `ORDER BY` clause ensures the result appears in the expected sequence. Interviewers check that the sort direction matches the prompt.

---

### The solution

**Apply the range filter to find slow production deploys**

```sql
SELECT svc_name, version, dur_secs, deploy_at
FROM deploy_logs
WHERE env_name = 'production' AND dur_secs > 150
ORDER BY dur_secs DESC, deploy_at
```

> **Cost Analysis**
>
> With ~600K rows, the query performs a single sequential scan. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether the query returns exactly the columns and ordering the prompt specifies; how quickly you identify the core operation and write clean, minimal code.

> **Common Pitfall**
>
> Returning extra columns that the prompt did not ask for, or using the wrong column alias, causes a grading mismatch even when the logic is correct.

---

## Common follow-up questions

- The `dur_secs` column in `deploy_logs` has a 1% null rate. How does your query handle rows where `dur_secs` is NULL, and could that silently change the result count? _(Tests whether the candidate accounts for NULLs in `deploy_logs.dur_secs` and understands how aggregates skip NULL values.)_
- If `deploy_logs` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `deploy_at`? _(Tests ability to identify performance hotspots related to `deploy_logs.deploy_at` at scale.)_
- `deploy_logs.status` only has 4 distinct values. If a new category were added, would your query automatically include it? _(Tests whether the query hard-codes values or dynamically adapts to `status` changes.)_

## Related

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