# Bronze Medal

> Two ahead of you. The rest below.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The FinOps team is investigating the top spending tiers and needs the 3rd highest cost amount on record. Multiple entries can share the same amount, so return the specific value that ranks third.

## Worked solution and explanation

### Why this problem exists in real interviews

Top-N selection with ties is the classic ranking trap. The interviewer is checking that you pick a window function whose tie behavior matches the prompt and that you understand why `LIMIT 1 OFFSET 2` is wrong here: when two line items share the highest amount, the third row in a sorted list is still rank 2.

> **Trick to Solving**
>
> Three ranking functions, three different answers when there are ties on `amount`:
> 
> - `ROW_NUMBER`: every row gets a unique number; OFFSET-style logic.
> - `RANK`: ties share the rank, then jumps (1, 1, 3).
> - `DENSE_RANK`: ties share the rank, no jump (1, 1, 2).
> 
> The prompt says "the specific value that ranks third," which is DENSE_RANK semantics: skip-no-ranks counting of distinct values.

---

### Break down the requirements

#### Step 1: Rank rows by amount with DENSE_RANK

Inside a CTE, attach `DENSE_RANK() OVER (ORDER BY amount DESC)` to every row. Tied amounts share a rank, and the rank counter only advances when the next distinct value appears.

#### Step 2: Pick rank 3 and collapse to one row

Filter the ranked output to `rnk = 3` and project `amount`. Multiple rows can satisfy `rnk = 3` if several line items share the third-highest amount. They all carry the same value, so `LIMIT 1` collapses them to the single answer.

---

### The solution

**DENSE_RANK then filter to rank 3**

```sql
SELECT amount
FROM (
    SELECT
        amount,
        DENSE_RANK() OVER (ORDER BY amount DESC) AS rnk
    FROM cloud_costs
) ranked
WHERE rnk = 3
LIMIT 1
```

> **Time and Space Complexity**
>
> **Time:** O(n log n) for the global sort over 5M `cloud_costs` rows. The window function is one ordered pass.
> 
> **Space:** O(n) for the ranked materialization, though planners that pipeline the window cut that down.

> **Interviewers Watch For**
>
> Strong candidates name the tie semantics out loud ("third distinct amount, not third row") before writing SQL. They also reach for `DENSE_RANK` rather than `LIMIT 1 OFFSET 2`, which silently returns whichever row happens to land in the third slot of a sort order, not the third distinct value.

> **Common Pitfall**
>
> Using `SELECT DISTINCT amount ... LIMIT 1 OFFSET 2`. It works when there are at least 3 distinct amounts, but the moment ties matter the prompt and a tied row appear, the interviewer wants to see you defend the tie behavior. DENSE_RANK encodes that behavior in the query.

---

## Common follow-up questions

- If only two distinct amounts exist in `cloud_costs`, what does this query return? _(Tests handling missing data with the engine's NULL semantics.)_
- How would you adapt the query to take an arbitrary N as input without changing the structure? _(Tests generalizing the ranking pattern.)_
- How would the query change if FinOps wanted the third-highest amount per region instead of overall? _(Tests partition-aware ranking for the per-region or per-provider variant.)_

## Related

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