# Top Cost Entry per Team

> The single biggest bill per team.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

For each team in the cost allocation table, surface the highest-cost entry showing the team name, a label combining service name and region, and the amount.

## Worked solution and explanation

### Why this problem exists in real interviews

This uses `cost_allocs` to probe per-group ranking via `ROW_NUMBER()` or `DENSE_RANK()` partitioned by a grouping key. It reveals whether a candidate understands the difference between `ROW_NUMBER`, `RANK`, and `DENSE_RANK` and picks the right one for the output contract.

---

### Break down the requirements

#### Step 1: Aggregate per team_name

`GROUP BY team_name` with the appropriate aggregate function produces one summary row per group from the `cost_allocs` table.

#### Step 2: Rank within each svc_name

Use `ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY aggregate DESC)` to rank entries within each partition.

#### Step 3: Filter to top entries

Wrap in a subquery and filter `WHERE rn <= N` to keep only the top entries per group.

---

### The solution

**Window row_number per team_name ordered by amount to pick each team's costliest entry**

```sql
SELECT svc_name, team_name, total_amount
FROM (
    SELECT
        svc_name,
        team_name,
        SUM(amount) AS total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY svc_name
            ORDER BY SUM(amount) DESC
        ) AS rn
    FROM cost_allocs
    GROUP BY svc_name, team_name
) ranked
WHERE rn <= 10
ORDER BY svc_name, total_amount DESC
```

> **Cost Analysis**
>
> The GROUP BY reduces the 12M-row `cost_allocs` table to the number of distinct `team_name` values. The window function sorts within each partition. A covering index on `(team_name, amount)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers specifically test whether you use `PARTITION BY` in the window function. Omitting it gives a global ranking instead of per-group, which is at its core different.

> **Common Pitfall**
>
> Using `ORDER BY ... LIMIT` instead of a window function for per-group ranking. LIMIT gives N rows globally, not per group. Per-group top-N always requires a window function.

---

## Common follow-up questions

- If two entries for the same team share the same highest amount, does ROW_NUMBER pick one arbitrarily? _(Tests that ROW_NUMBER breaks ties non-deterministically; RANK or DENSE_RANK with a filter on rank = 1 returns both.)_
- How do you construct the label combining svc_name and region? Does CONCAT handle NULLs? _(Tests NULL propagation in concatenation; CONCAT returns NULL if any argument is NULL in standard SQL.)_
- Would a correlated subquery approach (WHERE amount = (SELECT MAX(amount) FROM ... WHERE team_name = outer.team_name)) be more or less efficient? _(Tests query plan reasoning; correlated subqueries can be slower than a single window-function pass.)_

## Related

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