What Set It Off

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Problem

Each row of ci_builds is one CI build, labeled with the trigger that kicked it off and how long it ran in seconds. Show how many builds each trigger produced and their average duration, listed alphabetically by trigger.

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

Tables

ci_builds

Columns: build_id INTEGER, repo_name TEXT, branch TEXT, status TEXT, dur_secs INTEGER, trigger TEXT, built_at TEXT

Expected output

Worked solution and explanation

What this is really testing

This looks like a CI reporting task, but underneath it is one grouped tally. Its only real question is how you treat a build that never recorded a duration. On this data every 'push' build has a null dur_secs: 32 builds, and not one timing among them. The count for 'push' must still say 32, and its average has to come back null, not 0. Write COUNT(dur_secs) for the count and 'push' reports zero builds. Hand-roll the average and you invite a wrong denominator or a truncated one.

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

`AVG(dur_secs)`

The denominator is the number of builds that recorded a duration. A trigger with no timings returns null, which is exactly what 'push' shows.

`SUM(dur_secs) / COUNT(*)`

The denominator is every row in the group. Because dur_secs is an integer, many engines do integer division here, so 'manual' reads 454 instead of 454.588. On any trigger that mixes timed and untimed builds, the nulls also sit in the denominator and drag the average toward zero.

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 rows, not durations

COUNT(*) counts rows regardless of nulls, so all 32 untimed 'push' builds land in build_count. That is deliberate: the builds happened even though nobody recorded how long they took.

Step 3: Order for the reader

trigger is unique per output row, so sorting on it alphabetically gives one fixed order a reviewer can scan top to bottom.

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 year filter on `built_at` applied before the grouping.)