# Deploy Author Performance Score

> Not all deployers are equally reliable.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Rate each deploy author over the past 12 months using the formula (100 minus average duration) times number of deploys. Round the score and average duration to 2 decimal places. Show each author with their average duration, score, and rank, sorted from highest score to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a custom scoring formula using aggregates and window functions. Computing a composite metric from averages and counts, then ranking, probes multi-step analytical query skills.

---

### Break down the requirements

#### Step 1: Filter to last 12 months

`WHERE deploy_at >= CURRENT_DATE - INTERVAL '12 months'` scopes the data.

#### Step 2: Compute per-author metrics

`AVG(dur_secs)` for average duration, `COUNT(*)` for deploy count, and the formula `(100 - avg_dur) * count` for the score.

#### Step 3: Rank by score

`RANK() OVER (ORDER BY score DESC)` assigns positions.

---

### The solution

**Custom composite score with ranking**

```sql
SELECT
    author,
    ROUND(AVG(dur_secs), 2) AS avg_duration,
    ROUND((100 - AVG(dur_secs)) * COUNT(*), 2) AS score,
    RANK() OVER (ORDER BY (100 - AVG(dur_secs)) * COUNT(*) DESC) AS rank
FROM deploy_logs
WHERE deploy_at >= CURRENT_DATE - INTERVAL '12 months'
GROUP BY author
ORDER BY score DESC
```

> **Cost Analysis**
>
> Scan of 1.2M rows filtered to 12 months. GROUP BY author reduces to a small set. The RANK window function sorts on the computed score.

> **Interviewers Watch For**
>
> Whether the candidate reproduces the formula correctly: `(100 - avg_duration) * deploy_count`. Getting the formula wrong or mixing up the aggregation level is a common error.

> **Common Pitfall**
>
> If average duration exceeds 100, the score becomes negative. The formula assumes durations are well under 100 seconds. In production, you would normalize or clip the values.

---

## Common follow-up questions

- What if some authors have very few deploys? _(Their score would be low due to the COUNT multiplier. Tests whether a minimum deploy threshold should be applied.)_
- How would you normalize the score to a 0-100 range? _(Tests min-max normalization: (score - min) / (max - min) * 100.)_
- What if duration had outliers? _(Tests whether to use median instead of average, or to filter outliers.)_

## Related

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