# Weekly Build Status Report

> Every CI run, bucketed by week.

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

Domain: SQL · Difficulty: hard · Seniority: L5 · Asked in: Amazon

## Problem

The platform reliability team wants a weekly build health summary they can read straight down the calendar. For each week in 2026 (starting Sunday), count CI builds by final status: success, failed, and canceled, and lay the weeks out in chronological order from the year's first week onward. Show the week start date and counts for each status.

## Tables

### ci_builds

- build_id INTEGER
- repo_name TEXT
- branch TEXT
- status TEXT
- dur_secs INTEGER
- trigger TEXT
- built_at TEXT

Sample rows (10 of 200):

| build_id | repo_name | branch | status | dur_secs | trigger | built_at |
| --- | --- | --- | --- | --- | --- | --- |
| 5047 | backend-api | develop | failed | 34 | pull_request | 2026-02-02 01:13:00 |
| 5094 | infra | feature/auth | canceled | 53 | schedule | 2026-03-03 02:26:00 |
| 5141 | ml-pipeline | fix/login-bug | running | 72 | manual | 2026-04-04 03:39:00 |
| 5188 | mobile-app | release/v2.0 | queued | 91 | tag | 2026-05-05 04:52:00 |
| 5235 | data-platform | hotfix/payment | success | 110 | webhook | 2026-06-06 05:05:00 |
| 5282 | frontend | feature /spaces | failed | NULL | push | 2026-07-07 06:18:00 |
| 5329 | backend-api | main | canceled | 148 | pull_request | 2026-08-08 07:31:00 |
| 5376 | infra | develop | running | 167 | schedule | 2026-09-09 08:44:00 |
| 5423 | ml-pipeline | feature/auth | queued | 186 | manual | 2026-01-10 09:57:00 |
| 5470 | mobile-app | fix/login-bug | success | 205 | tag | 2026-02-11 10:10:00 |

## Expected output

First 5 of 33 rows:

| week_start | success_count | failed_count | canceled_count |
| --- | --- | --- | --- |
| 2026-01-05 | 2 | 1 | 0 |
| 2026-01-12 | 2 | 0 | 1 |
| 2026-01-26 | 1 | 1 | 1 |
| 2026-02-02 | 0 | 2 | 0 |
| 2026-02-23 | 1 | 1 | 1 |

## Worked solution and explanation

### Why this problem exists in real interviews

This tests conditional aggregation (pivot) combined with date truncation. Interviewers check whether you can bucket rows by week, pivot status values into columns in a single scan, and filter out non-terminal statuses before aggregating.

> **Trick to Solving**
>
> 'Count by status with one column per status' signals a CASE-based pivot. 'Week starting Sunday' requires date truncation.
> 
> 1. Filter to terminal statuses (success, failed, canceled) - drop running/queued
> 2. Truncate `built_at` to Sunday week start
> 3. SUM(CASE WHEN status = 'X' THEN 1 ELSE 0 END) for each status column
> 4. GROUP BY `week_start`

---

### The solution

**Conditional pivot by week with Sunday start**

```sql
WITH weekly AS (
    SELECT
        date(built_at, 'weekday 0', '-6 days') AS week_start,
        status
    FROM ci_builds
    WHERE built_at BETWEEN '2026-01-01' AND '2026-12-31'
      AND status IN ('success', 'failed', 'canceled')
)
SELECT
    week_start,
    SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) AS success_count,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed_count,
    SUM(CASE WHEN status = 'canceled' THEN 1 ELSE 0 END) AS canceled_count
FROM weekly
GROUP BY week_start
ORDER BY week_start
```

> **Common Pitfall**
>
> Forgetting to filter out 'running' and 'queued' rows inflates the denominator. Those builds haven't finished - counting them misrepresents pass/fail rates.

---

## Common follow-up questions

- How would you add a total builds column that includes all statuses? _(Add COUNT(*) before filtering, or remove the status IN filter and use an 'other' CASE branch.)_
- What if you needed a pass rate percentage per week instead of raw counts? _(CAST(`success_count` AS REAL) / NULLIF(`success_count` + `failed_count`, 0) - use NULLIF to avoid divide-by-zero on empty weeks.)_
- What if new statuses are added in the future? _(Static CASE breaks for unknown values. Either add an 'other' catch-all or move to a key-value output and let the BI layer pivot.)_

## Related

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