# The Weight of Giants

> In every sky, only two hold the throne.

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

Domain: SQL · Difficulty: medium · Seniority: L6

## Problem

The FinOps team needs the top 2 highest-spending services within each cloud provider. If two services tie on spend within a provider, include both.

## Worked solution and explanation

### What this really is

Strip the FinOps costume and this is a partitioned top-N with a tie-inclusion catch. Anyone can write the `GROUP BY` and the outer filter; what actually separates candidates is the ranking function. Reach for `ROW_NUMBER` and you silently drop a service that tied for second, and your numbers never reconcile with the FinOps team's spreadsheet.

> **Trick to Solving**
>
> "Top 2 within each provider" plus "include both if tied" signals a partitioned `DENSE_RANK`. The partition column is `provider`, and the ordering column is the aggregated spend.
> 
> 1. Aggregate to one row per (provider, svc_name)
> 2. Apply `DENSE_RANK() OVER (PARTITION BY provider ORDER BY total_spend DESC)`
> 3. Filter to rank `<= 2` in an outer query

---

### Break down the requirements

#### Step 1: Aggregate spend per provider and service

`GROUP BY provider, svc_name` with `SUM(amount)` collapses the 15M-row table down to a few hundred (provider, svc_name) groups. Note that grouping is on the exact `provider` value, so distinct spellings are treated as distinct providers, which is why the standings are computed per reported provider name.

#### Step 2: Rank within each provider

`DENSE_RANK() OVER (PARTITION BY provider ORDER BY total_spend DESC)` ranks services independently within each provider, preserving ties.

#### Step 3: Filter to top 2 ranks

Wrap in a subquery, filter `WHERE rnk <= 2`, and order by `provider, total_spend DESC` for clean output.

---

### The solution

**Partitioned top-N with tie inclusion**

```sql
SELECT provider, svc_name, total_spend
FROM (
    SELECT
        provider,
        svc_name,
        SUM(amount) AS total_spend,
        DENSE_RANK() OVER (
            PARTITION BY provider
            ORDER BY SUM(amount) DESC
        ) AS rnk
    FROM cloud_costs
    GROUP BY provider, svc_name
) ranked
WHERE rnk <= 2
ORDER BY provider, total_spend DESC
```

> **Cost Analysis**
>
> The aggregation reduces the 15M rows to a few hundred groups. The window function then sorts each provider's services within its own partition, so per-partition work is tiny. The dominant cost is the full table scan feeding the initial `GROUP BY`.

> **Interviewers Watch For**
>
> L6 candidates should mention that `DENSE_RANK` is specifically chosen over `ROW_NUMBER` for tie inclusion, and articulate why the aggregation must happen before the ranking step.

> **Common Pitfall**
>
> Using `RANK` instead of `DENSE_RANK` would produce gaps (1, 1, 3 instead of 1, 1, 2), meaning the filter `<= 2` could miss the third-place service entirely.

---

## Common follow-up questions

- What if you needed the top 2 per provider per region? _(Tests compound partitioning: `PARTITION BY provider, region`.)_
- How would you break ties deterministically? _(Add a secondary sort key like `svc_name ASC` to the window ORDER BY.)_
- What if the table had 1,000 providers instead of 3? _(The number of partitions grows, and the window function's memory footprint increases.)_
- Could you solve this without window functions? _(A correlated subquery approach is valid but typically less readable and harder to optimize.)_

## Related

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