# The Spender Leaderboard

> Spending speaks. The leaderboard does the listening.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: IBM

## 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.

## Tables

### transactions

- transaction_id INTEGER
- user_id INTEGER
- product_id INTEGER
- quantity INTEGER
- total_amount REAL
- transaction_date TEXT

Sample rows (10 of 200):

| transaction_id | user_id | product_id | quantity | total_amount | transaction_date |
| --- | --- | --- | --- | --- | --- |
| 1067 | 197 | 1001 | 2 | 23.46 | 2026-02-02 |
| 1134 | 294 | 1050 | 3 | 36.93 | 2026-03-03 |
| 1201 | 391 | 1099 | 4 | 50.4 | 2026-04-04 |
| 1268 | 488 | 1148 | 5 | 63.87 | 2026-05-05 |
| 1335 | 585 | 1197 | 1 | 77.34 | 2026-06-06 |
| 1402 | 682 | 1246 | 2 | 90.81 | 2026-07-07 |
| 1469 | 779 | 1295 | 3 | 104.28 | 2026-08-08 |
| 1536 | 876 | 1344 | 4 | 117.75 | 2026-09-09 |
| 1603 | 973 | 1393 | 5 | 131.22 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 5 rows:

| user_id | total_spend | rnk |
| --- | --- | --- |
| 1070 | 10511.76 | 1 |
| 973 | 10323.18 | 2 |
| 876 | 10134.6 | 3 |
| 779 | 9946.02 | 4 |
| 682 | 9757.44 | 5 |

## 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/top_spenders_dense_rank)
- [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.