The Comfortable Middle

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

City and County of San Francisco

Problem

An allocation among its team's three largest is an obvious outlier, so the FinOps team's audit of cost_allocs looks past those for the ones that still run hot. Find each allocation above its team's average amount that falls outside that team's three largest, with its team name and amount.

Above average but not extreme.

Tables

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

What this problem really is

Strip the FinOps costume and this is two per-team questions asked of every row at once: is this allocation above its team's mean, and does it sit outside its team's three largest amounts? Anyone can compute the mean or the positions alone. The trap is collapsing each team to one row to get the average and losing the allocations you were asked to return, or keeping only one of the two filters. Remember too that 'three largest' means three amount tiers: equal amounts share a place. Get either half wrong and the audit lists the team's whales, or every row above average, which are exactly the allocations it was meant to look past.

Break down the requirements

Step 1: Compute the per team average separately

AVG(amount) with GROUP BY team_name lives in its own CTE, team_avg, because every allocation needs its team mean attached later. The mean covers every allocation the team has, the top three included, so it is computed before anything is filtered.

Step 2: Position allocations within each team

DENSE_RANK() OVER (PARTITION BY team_name ORDER BY amount DESC) lets equal amounts share a place with no gaps, so positions 1 to 3 are exactly the three largest amount tiers. RANK() leaves a gap after a tie, so the third tier can be numbered 4 and leak into the output. ROW_NUMBER() splits equal amounts across 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. Use strict inequalities on both, because the third tier and the average itself are excluded.

The solution

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

Common follow-up questions

• How would the answer change if 'team average' had to exclude the row being evaluated? (Tests whether the candidate knows leave-one-out averages: a window average whose frame excludes the current row, or the algebraic form (team sum minus current amount) divided by (team count minus one).)
• Why `DENSE_RANK()` rather than `RANK()` for this prompt? (Tests whether the candidate can articulate gap behavior. With two allocations tied for first, `RANK()` numbers the third-largest amount 4, so `rnk > 3` lets a top-three tier into the output.)
• If the table grew to 1B rows, how would you avoid reading it twice? (Tests awareness that `team_avg` and `ranked` both read `cost_allocs`. Computing the average as a window over the same `PARTITION BY team_name` puts both values on one sort per team.)