# Weekly Build Status Report

> Every CI run, bucketed by week.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

The platform reliability team wants a weekly build health summary. For each week in 2026 (starting Sunday), count CI builds by final status: success, failed, and canceled. Show the week start date and counts for each status.

## 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_order_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). 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.