# The Comfortable Middle

> Above average but not extreme.

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

A FinOps team is auditing cloud spend and wants the allocations that run hot without being the obvious outliers. Within each team, find the cost allocations above that team's average allocation amount that still fall outside the team's three largest, and return each one's team name and amount.

## Worked solution and explanation

### What this problem really is

Strip the FinOps costume and this is two different shapes of computation stapled together: a per-group aggregate (each team's mean) and a per-row ordering within that same group (where this allocation sits among its team's amounts). Anyone can write either one alone. The trick is combining a group average with a per-row position without conflating their grains, and remembering that 'three largest' means three distinct amount tiers, not three rows. Reach for a self join here and you will both blow up the row count and get the tie behavior wrong.

---

### Break down the requirements

#### Step 1: Compute the per team average separately

AVG(amount) GROUP BY team_name lives in its own CTE because every allocation row needs the team mean attached. Trying to combine AVG with a window in one SELECT mixes aggregation grain and forces a subquery anyway.

#### Step 2: Position allocations within each team

DENSE_RANK() OVER (PARTITION BY team_name ORDER BY amount DESC) gives the no-gap behavior the prompt needs. RANK skips numbers after ties and would silently drop allocations that should qualify. ROW_NUMBER splits ties arbitrarily, so two equal amounts land on different positions.

#### Step 3: Apply BOTH filters with AND

Strictly above the team average AND strictly beyond position 3 means you keep rows with rnk = 4, 5, 6 and so on whose amount also beats the team mean. Strict inequalities on both, because position 3 and the average itself are excluded.

---

### The solution

**Join team average against window position**

```sql
WITH team_avg AS (
  SELECT team_name, AVG(amount) AS avg_amount FROM cost_allocs GROUP BY team_name
),
ranked AS (
  SELECT team_name, amount,
         DENSE_RANK() OVER (PARTITION BY team_name ORDER BY amount DESC) AS rnk
  FROM cost_allocs
)
SELECT r.team_name, r.amount
FROM ranked r
INNER JOIN team_avg ta ON r.team_name = ta.team_name
WHERE r.amount > ta.avg_amount AND r.rnk > 3
```

> **Cost Analysis**
>
> Both CTEs scan cost_allocs once. With 25M rows and 70 teams, the GROUP BY for team_avg collapses to 70 rows that broadcast cheaply during the join. The window is the dominant cost because it must sort each partition by amount; partitioning by team_name lets the planner sort within team rather than globally.

> **Interviewers Watch For**
>
> Whether you reach for DENSE_RANK (no gaps) over RANK (with gaps) or ROW_NUMBER (arbitrary ties), and whether you keep both filters in WHERE rather than splitting into HAVING. Strong candidates also ask whether 'team average' should exclude the row itself; here it does not, so all rows feed the average.

> **Common Pitfall**
>
> Writing rnk >= 3 instead of rnk > 3 keeps the third-largest tier, which the prompt excludes. The other common miss is using RANK instead of DENSE_RANK: a tie at position 3 then bumps the next position to 5 or later, dropping legitimate allocations that should have been the fourth distinct amount.

---

## Common follow-up questions

- How would the answer change if 'team average' had to exclude the row being evaluated? _(Tests whether the candidate knows about leave-one-out averages, which require a window AVG with a frame that excludes the current row, or an algebraic trick like (sum total minus current) divided by (count minus one).)_
- Why DENSE_RANK rather than RANK for this prompt? _(Tests whether the candidate can articulate the gap behavior. Two allocations tied for first cause RANK to skip position 2, so 'rnk > 3' would silently exclude rows the prompt expects.)_
- If the table grew to 1B rows, how would you avoid sorting it twice? _(Tests awareness that team_avg and ranked both scan cost_allocs. A single pass with conditional aggregation or partition pruning by team_name reduces wall time.)_

## Related

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