# The Spender Leaderboard

> Spending speaks. The leaderboard does the listening.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Show the top 5 users by total transaction value. Tied users share the same rank with no gaps. Include all tied users at each rank.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a foundational window function problem. Interviewers check whether you know the difference between `ROW_NUMBER`, `RANK`, and `DENSE_RANK`, and whether you aggregate before ranking.

> **Trick to Solving**
>
> "Tied users share the same rank with no gaps" and "include all tied users" are the two signals for `DENSE_RANK`. `ROW_NUMBER` would break ties arbitrarily; `RANK` would skip numbers after ties.
> 
> 1. Aggregate total spend per user
> 2. Apply `DENSE_RANK()` over the aggregate
> 3. Filter to rank `<= 5`

---

### Break down the requirements

#### Step 1: Aggregate total per user

Aggregate per user: SUM(total_amount) AS total_spend, grouped by user_id, to get each user's total transaction value.

#### Step 2: Rank with DENSE_RANK

Apply DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) so tied totals share the same rank with no gaps (vs RANK which gaps, or ROW_NUMBER which breaks ties arbitrarily).

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

Filter to rnk <= 5 in an outer query: this keeps the top 5 distinct rank levels and automatically includes every tied user at each rank.

---

### The solution

**Aggregate then dense rank for tie-inclusive top-N**

```sql
SELECT user_id, total_spend, rnk
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 <= 5
ORDER BY rnk, user_id;
```

> **Cost Analysis**
>
> The `GROUP BY` reduces 80M rows to 4M. The window sort on 4M rows is the bottleneck. If performance is critical, a materialized view of user-level totals would eliminate the repeated aggregation.

> **Interviewers Watch For**
>
> Using `LIMIT 5` instead of `DENSE_RANK` is the most common error. `LIMIT` silently drops tied users and produces non-deterministic output.

> **Common Pitfall**
>
> Ranking individual transactions instead of user-level aggregates. Always aggregate to the output grain before applying window functions.

---

## Common follow-up questions

- What if you needed exactly 5 rows, breaking ties by user_id? _(Now `ROW_NUMBER` with a compound ORDER BY is the correct choice.)_
- How would the result change if some users had negative transaction amounts? _(Tests awareness that SUM includes negatives, potentially reordering rankings.)_
- Could you solve this without a subquery? _(Some engines support `QUALIFY` for inline window-function filtering.)_

## Related

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