# The Weight of a Verdict

> Every run carries the full measure of all who ended the same way.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The data platform team wants each pipeline run row enriched with a status-level benchmark: the total rows ingested across all pipelines sharing the same status. Preserve every original column and add the status-wide total as an additional column.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a window-function problem wearing a data-enrichment costume. The real question: can you add a group-level total to every row without collapsing those rows? Anyone can reach for a GROUP BY, but that folds 80,000 runs down to four status buckets and destroys the per-run detail the prompt explicitly asks you to keep. The move is a partitioned window aggregate that computes each status total once and pins it back onto every row that belongs to that status.

> **Trick to Solving**
>
> "Preserve every original column and add the status-wide total" is the tell for a window function over GROUP BY. GROUP BY collapses; a window aggregate keeps the grain and layers the total on top.
> 
> 1. Notice the output row count matches the input row count
> 2. Use `SUM(rows_in) OVER (PARTITION BY status)` to compute the status-level total
> 3. Select all original columns plus the new window column

---

### Break down the requirements

#### Step 1: Identify the enrichment pattern

The output must carry every original column from `data_pipes` (80K rows) plus one new column. Same grain in, same grain out means no GROUP BY, just a window function.

#### Step 2: Compute status-level total as a window

`SUM(rows_in) OVER (PARTITION BY status)` computes the total `rows_in` for each status and attaches it to every row sharing that status.

---

### The solution

**Window function for row-level enrichment**

```sql
SELECT
    pipe_id,
    pipe_name,
    status,
    rows_in,
    rows_out,
    start_at,
    dur_secs,
    SUM(rows_in) OVER (PARTITION BY status) AS status_total_rows_in
FROM data_pipes
```

> **Cost Analysis**
>
> With 80K rows and 4 distinct statuses, the window partitions into 4 groups of ~20K rows. It stays fast with no sort required, since a partition-wide SUM has no ORDER BY.

> **Interviewers Watch For**
>
> The candidate who computes the totals with a GROUP BY and then joins back to the original table. It gets the right numbers, but it is an extra pass and a join the window function makes unnecessary. The one-line window aggregate is the tell you have seen this shape before.

> **Common Pitfall**
>
> Adding ORDER BY inside the OVER clause. `SUM(...) OVER (PARTITION BY status ORDER BY ...)` quietly turns the partition total into a running sum, and your totals stop matching across rows in the same status. Omit ORDER BY for the full-partition aggregate.

---

## Common follow-up questions

- What if you needed a running total within each status ordered by start time? _(Tests adding ORDER BY start_at to the window frame for cumulative sums.)_
- How would you also include the overall total across all statuses? _(Add a second window: `SUM(rows_in) OVER ()` with no partition.)_
- What if rows_in had NULL values? _(SUM ignores NULLs, so the total would exclude them. COALESCE might be needed.)_

## Related

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