# What Set It Off

> Every build has a cause. Tally the work behind each one.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

A CI platform's reliability team wants to see how build activity breaks down by what kicked each build off. For each trigger type, show how many builds it produced and the average build duration in seconds, listed alphabetically by trigger.

## Worked solution and explanation

### What this is really testing

This looks like a CI reporting task, but underneath it is the simplest shape in SQL: one grouped tally. The only thing separating a clean answer from a buggy one is how you treat a build that never recorded a duration. The 'push' trigger fired a single build whose dur_secs is null. Your count must still report 1, but the average has to come back as null, not 0. Anyone who reaches for SUM(dur_secs) / COUNT(*) has quietly built the wrong denominator.

**Per-trigger build summary**

```sql
SELECT
  trigger,
  COUNT(*) AS build_count,
  AVG(dur_secs) AS avg_duration
FROM ci_builds
GROUP BY trigger
ORDER BY trigger;
```

*One pass, grouped by trigger, with AVG doing the null handling for free.*

> **Let AVG own the nulls**
>
> AVG(dur_secs) divides the sum of non-null durations by the count of non-null durations. You never write that division yourself, so the null-duration build simply drops out of both the numerator and the denominator. That is exactly the behavior you want, and it is why a group made entirely of null durations comes back as null rather than 0.

**AVG(dur_secs)**

The denominator is the number of builds that actually recorded a duration. A group with one real value and one null averages the single real value.

**SUM(dur_secs) / COUNT(*)**

The denominator is every row in the group, including the null-duration build. The average is dragged toward zero, and an all-null group can return 0 instead of null depending on the engine.

#### Step 1: Pick the grain

One row per trigger means GROUP BY trigger. Every column in the SELECT list is then either the grouping key or an aggregate over that group.

#### Step 2: Count every build

COUNT(*) counts rows in the group regardless of nulls, so the null-duration push build still contributes 1 to build_count. That is deliberate: the build happened even if its duration was never recorded.

#### Step 3: Order for the reader

trigger is unique per group, so sorting alphabetically gives a stable, tie-free order a reviewer can scan top to bottom.

> **The tell**
>
> A strong candidate spots the null dur_secs in the sample rows and says out loud that COUNT(*) and AVG treat it differently. That one sentence signals they reason about null semantics before the data bites them.

> **COUNT(*) vs COUNT(dur_secs)**
>
> If the ask had been 'how many builds recorded a duration', COUNT(*) would be wrong and you would need COUNT(dur_secs), which skips nulls. Always know which of the two counts the question wants.

## Common follow-up questions

- How would you also report, per trigger, the number of builds that never recorded a duration? _(Tests COUNT(*) minus COUNT(dur_secs), or a conditional SUM over dur_secs IS NULL.)_
- Now restrict the report to builds from the current year. How does that change the query? _(Tests a strftime date filter in the WHERE clause applied before the grouping.)_

## Related

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