# Three Peaks

> Every team's steepest months, and nothing below them.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

For each team, surface the three highest unique monthly cost amounts, listed alphabetically by team and then by amount from highest to lowest.

## Worked solution and explanation

### What this really is

Strip the finance costume and this is a per-team top-3 on a value you have to build first. The skill being probed: can you rank within each team, not across the whole table? Anyone can sum amounts by team and month. The trap is the ranking scope. Reach for ORDER BY monthly_cost DESC LIMIT 3 and you get the three most expensive months in the entire company, then you hand back three rows total when the ask was three per team. The fix is a window function partitioned by team_name, and because the prompt wants the three highest unique amounts, that ranking has to run on distinct totals or two months tied at the same cost quietly eat a slot.

> **Trick to Solving**
>
> "Top N per group" always wants a window function with PARTITION BY. A single ORDER BY and LIMIT gives a global top N, never one list per group.
> 
> 1. Aggregate cost per team per month
> 2. Deduplicate to unique monthly totals per team
> 3. Rank with `DENSE_RANK() OVER (PARTITION BY team_name ORDER BY monthly_cost DESC)` and keep rank `<= 3`

---

### Break down the requirements

#### Step 1: Aggregate monthly costs per team

Aggregate to the monthly grain: cost_allocs has multiple rows per (team_name, period) (e.g. DATA-ENG/2026-04 has 3 rows), so a team's monthly cost is SUM(amount) GROUP BY team_name, period, not the raw per-row amount.

#### Step 2: Keep unique monthly amounts

Reduce to UNIQUE monthly cost amounts per team with SELECT DISTINCT team_name, monthly_cost, so two months with an identical total collapse to one value (the prose asks for the 'three highest unique monthly cost amounts').

#### Step 3: Filter to the top three per team

Rank the distinct monthly costs within each team with DENSE_RANK() OVER (PARTITION BY team_name ORDER BY monthly_cost DESC) and keep rnk <= 3. PARTITION BY is what scopes the ranking to each team instead of the whole table.

---

### The solution

**Per-team monthly ranking with window function**

```sql
WITH monthly AS (
    SELECT team_name, period, SUM(amount) AS monthly_cost
    FROM cost_allocs
    GROUP BY team_name, period
),
distinct_costs AS (
    SELECT DISTINCT team_name, monthly_cost
    FROM monthly
),
ranked AS (
    SELECT
        team_name,
        monthly_cost,
        DENSE_RANK() OVER (
            PARTITION BY team_name
            ORDER BY monthly_cost DESC
        ) AS rnk
    FROM distinct_costs
)
SELECT team_name, monthly_cost
FROM ranked
WHERE rnk <= 3
ORDER BY team_name, monthly_cost DESC;
```

> **Cost Analysis**
>
> The GROUP BY on 25M rows collapses to ~2,500 rows (70 teams x 36 periods). The window function then sorts a handful of rows per partition, so total cost is dominated by the single aggregation scan, not the ranking.

> **Interviewers Watch For**
>
> The tell is the PARTITION BY clause. Drop it and you have written a global top three, which returns three rows for the whole company instead of three per team.

> **Common Pitfall**
>
> Using `ORDER BY monthly_cost DESC LIMIT 3` in place of a window function. LIMIT yields three rows globally, so you surface the wrong teams entirely and miss every team below the top spenders.

---

## Common follow-up questions

- How would you also show each month's rank in the output? _(Tests including the rank column in the outer SELECT.)_
- What if you wanted the bottom three months instead? _(Tests flipping the ORDER BY direction.)_
- How would you handle teams with fewer than three months of data? _(Tests that the query naturally returns fewer rows.)_
- What changes if ties in cost should each occupy their own slot rather than sharing a rank? _(Tests switching between ROW_NUMBER and DENSE_RANK.)_
- How would you add a running total column across a team's months? _(Tests cumulative SUM with a window function.)_

## Related

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