# Successful Production Deploys

> Successful production deploys with duration.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The release team is investigating slow production rollouts. Pull all successful production deployments that have a recorded duration, from the longest-running to the shortest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests whether a candidate can demonstrate handling null values correctly. This is a foundational check that interviewers use early in a round to verify baseline proficiency.

---

### Break down the requirements

#### Step 1: Filter out null values

Exclude rows where `dur_secs` is NULL. This prevents nulls from polluting aggregations or creating phantom groups.

#### 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

**Filter deploy_logs by env, status, and non-null duration then sort descending**

```sql
SELECT *
FROM deploy_logs
WHERE env_name = 'production' AND status = 'success' AND dur_secs IS NOT NULL
ORDER BY dur_secs DESC
```

> **Cost Analysis**
>
> With ~500K 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 how you handle NULL values and whether you account for them in filters and aggregations.

> **Common Pitfall**
>
> Forgetting to filter NULLs creates phantom groups or inflated counts. Always check `null_fraction` in the schema before assuming columns are clean.

---

## Common follow-up questions

- If you omit the IS NOT NULL check on dur_secs, how does ORDER BY dur_secs DESC treat those NULL rows in PostgreSQL vs. MySQL? _(Tests knowledge of NULL sort positioning differences across engines.)_
- Would adding a composite index on (env_name, status, dur_secs) help, and in what order should the columns appear? _(Tests index design for multi-predicate queries with an ORDER BY tail column.)_
- If the prompt changed to 'top 10 longest', how would you handle ties at position 10? _(Tests awareness of LIMIT vs. DENSE_RANK for tie-inclusive cutoffs.)_

## Related

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