# Department Cost by Status

> Headcount and compensation. The dashboard view.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The finance team wants a department-level cost summary. For each team, count the number of allocations categorized as 'active' and compute the total cost across all allocations for that team.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests conditional aggregation alongside unconditional aggregation. Counting 'active' allocations while summing all amounts probes whether you can mix filtered and unfiltered aggregates.

---

### Break down the requirements

#### Step 1: Group by team

`GROUP BY team_name` produces one row per team.

#### Step 2: Count active allocations

`SUM(CASE WHEN category = 'active' THEN 1 ELSE 0 END)` counts active entries.

#### Step 3: Sum total cost

`SUM(amount)` computes total across all categories.

---

### The solution

**Mixed conditional and unconditional aggregation**

```sql
SELECT
    team_name,
    SUM(CASE WHEN category = 'active' THEN 1 ELSE 0 END) AS active_count,
    SUM(amount) AS total_cost
FROM cost_allocs
GROUP BY team_name
```

> **Cost Analysis**
>
> Single scan of 12M rows. The conditional and unconditional aggregates run in the same pass. Output is one row per team.

> **Interviewers Watch For**
>
> Whether the candidate computes both metrics in a single pass vs two separate queries. The single-pass approach is more efficient and shows aggregate function fluency.

> **Common Pitfall**
>
> Using a WHERE filter for 'active' would exclude non-active rows from the total cost sum. The conditional CASE inside SUM is the correct pattern.

---

## Common follow-up questions

- How would you also show the percentage of active allocations per team? _(Divide active_count by COUNT(*) and multiply by 100.)_
- What if 'active' is determined by a date range, not a category? _(Tests changing the CASE condition to a date-based filter.)_
- How would you pivot this to show counts per category as columns? _(Tests CROSSTAB or multi-CASE conditional aggregation.)_

## Related

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