# Top 3 Monthly Costs per Team

> Three priciest months per team.

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

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

### Why this problem exists in real interviews

This is a per-group top-N problem requiring a window function with PARTITION BY. It tests whether you can aggregate costs by team and month, then rank months within each team. This two-level aggregation-then-ranking pattern is common in financial analytics.

> **Trick to Solving**
>
> "Top N per group" always requires a window function with PARTITION BY. A simple ORDER BY and LIMIT gives a global top N, not per-group.
> 
> 1. Aggregate cost per team per month
> 2. Use `ROW_NUMBER() OVER (PARTITION BY team ORDER BY cost DESC)`
> 3. Filter to rank `<= 3`

---

### Break down the requirements

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

`GROUP BY team_name, period` with `SUM(amount)` produces the monthly cost for each team.

#### Step 2: Rank months within each team

`ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY SUM(amount) DESC)` ranks each team's months from most to least expensive.

#### Step 3: Filter to top 3 months

`WHERE rn <= 3` keeps only the 3 most expensive months per team.

---

### The solution

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

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

> **Cost Analysis**
>
> The GROUP BY on 15M rows produces ~1,800 rows (50 teams x 36 periods). The window function sorts 36 rows per partition. Total cost is dominated by the aggregation scan.

> **Interviewers Watch For**
>
> Interviewers look for the PARTITION BY clause. Without it, you get a global top 3, not per team.

> **Common Pitfall**
>
> Using `ORDER BY monthly_cost DESC LIMIT 3` instead of a window function. LIMIT gives 3 rows globally, not per team.

---

## Common follow-up questions

- How would you also show each month's rank in the output? _(Tests including the rn column in the outer SELECT.)_
- What if you wanted the bottom 3 months instead? _(Tests flipping the ORDER BY direction.)_
- How would you handle teams with fewer than 3 months of data? _(Tests that the query naturally returns fewer rows.)_
- What if ties in cost should share the same rank? _(Tests switching from ROW_NUMBER to DENSE_RANK.)_
- How would you add a running total column? _(Tests cumulative SUM with a window function.)_

## Related

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