# Third Highest Spender

> Bronze medal in spending.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Find the user with the third highest total spend across all transactions. If there is a tie, include all users sharing that rank. Show the user ID, a reference country from the customers table, and total spend.

## Worked solution and explanation

### Why this problem exists in real interviews

This is the classic Nth-highest value problem. Interviewers use it to test whether you know how to rank aggregated values with a window function and extract a specific position.

> **Trick to Solving**
>
> "Third highest" signals a ranking problem. Use `DENSE_RANK` if you want the third distinct value; use `ROW_NUMBER` if you want exactly one row.
> 
> 1. Aggregate the metric per entity
> 2. Apply `DENSE_RANK() OVER (ORDER BY metric DESC)`
> 3. Filter to rank = 3

---

### Break down the requirements

#### Step 1: Aggregate spend per entity

`SUM(total_amount)` grouped by the spender produces per-entity totals.

#### Step 2: Rank by total spend

`DENSE_RANK() OVER (ORDER BY total_spend DESC)` assigns positions. DENSE_RANK ensures tied values share the same rank.

#### Step 3: Filter to rank 3

Wrap in a subquery and filter `WHERE rnk = 3` to extract the third-highest spender.

---

### The solution

**DENSE_RANK for Nth-position extraction**

```sql
SELECT user_id, total_spend
FROM (
    SELECT
        user_id,
        SUM(total_amount) AS total_spend,
        DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rnk
    FROM transactions
    GROUP BY user_id
) ranked
WHERE rnk = 3
```

> **Cost Analysis**
>
> The GROUP BY reduces 120M rows to the number of distinct users. The window function sorts those rows once. Cost is dominated by the aggregation scan.

> **Interviewers Watch For**
>
> Interviewers specifically watch for the choice between DENSE_RANK and ROW_NUMBER. If two users tie for 2nd, ROW_NUMBER arbitrarily picks one as 3rd, which may be wrong.

> **Common Pitfall**
>
> Using `ORDER BY total_spend DESC LIMIT 1 OFFSET 2` skips ties. If two users tie at rank 2, the offset approach returns one of them as the "3rd," which is actually tied for 2nd. DENSE_RANK handles this correctly.

---

## Common follow-up questions

- What if multiple users tie for 3rd place? _(Tests understanding that DENSE_RANK returns all tied rows at position 3.)_
- How would you find the Nth highest for each category? _(Tests adding PARTITION BY category to the window function.)_
- Could you solve this without a window function? _(Tests correlated subquery: COUNT(DISTINCT higher values) = 2.)_
- What if there are fewer than 3 distinct spend values? _(Tests edge case awareness: the query returns no rows.)_

## Related

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