Weekly Build Status Report

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Amazon

Problem

ci_builds logs one row per CI run across several years, each with a built_at timestamp and a status that may still read 'running' or 'queued' instead of a final outcome. Over the runs built in 2026, report each Sunday-to-Saturday week, labeled with the date of its opening Sunday, and how many of its runs ended as success, failed and canceled. A run with no outcome yet counts toward nothing, so a week holding only unfinished runs gets no row.

Every CI run, bucketed by week.

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

The pivot under the report

This is a pivot wearing a weekly report. Anyone can count builds by status. The grade turns on things the prompt barely says out loud. The output is one row per week with three count columns, so the values of status have to become columns, not rows. The week key is a real date, the Sunday on or before each built_at, which no week number can give you. And ci_builds spans several years, so the 2026 bound is what keeps last year's builds out of this year's weeks. Get the shape wrong and nothing matches. Get the anchor wrong by a single day and every week shifts, taking every count with it.

Step 1: Keep the finished 2026 builds

status IN ('success', 'failed', 'canceled') drops the 'running' and 'queued' rows before anything is grouped. Filtering here, not just ignoring them in the sums, is what keeps a week of nothing-but-pending runs from showing up as 0, 0, 0. For the year, bound built_at from the first second of January 1st to the last second of December 31st, so a build at 23:40 on the final day still counts.

Step 2: Anchor each build to its Sunday

date(built_at, '-6 days', 'weekday 0') steps back six days, then rolls forward to the next Sunday, staying put if it already is one. The landing spot is always the Sunday on or before the build: a Sunday build keeps its own date, and a Saturday build lands six days back.

Step 3: Pivot the outcomes into columns

Group by week_start alone. Each SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) counts one outcome inside the same scan, and a week with no cancellations reports 0 in that column. Three per-status counts joined on week_start would build the same table. The conditional sums just do it in one pass, with no join to lose a week.

WITH finished_builds AS (
    SELECT
        date(built_at, '-6 days', 'weekday 0') AS week_start,
        status
    FROM ci_builds
    WHERE built_at BETWEEN '2026-01-01 00:00:00' AND '2026-12-31 23:59:59'
      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 finished_builds
GROUP BY week_start

Roll forward, then step back

date(built_at, 'weekday 0', '-6 days') jumps to the coming Sunday and backs off six days, which lands on a Monday. Every week then runs Monday to Sunday under a Monday label, and not one row matches.

Step back, then roll forward

date(built_at, '-6 days', 'weekday 0') backs off first, so the forward roll can never pass the build itself. The result is the Sunday on or before built_at, which is exactly the week the report asks for.

SELECT
    strftime('%W', built_at) AS week,
    status,
    COUNT(*) AS builds
FROM ci_builds
WHERE status IN ('success', 'failed', 'canceled')
GROUP BY strftime('%W', built_at), status

Common follow-up questions

• How would you add a total builds column that includes every status? (Drop the `IN` filter and add `COUNT(*)`, keeping the three `CASE` sums for the finished outcomes; pending-only weeks then reappear by design.)
• What if the team wants a weekly pass rate instead of raw counts? (Divide `success_count` by `NULLIF(success_count + failed_count, 0)` so a week with no finished verdicts returns `NULL` instead of erroring.)
• What if new statuses appear next quarter? (Fixed `CASE` columns silently ignore unknown values. Add an 'other' catch-all, or emit long format and let the BI layer pivot.)