# The Biggest Fish

> In every aisle, one customer outspends the rest. Find them.

Canonical URL: <https://datadriven.io/problems/the-biggest-fish>

Domain: SQL · Difficulty: medium · Seniority: mid

## Problem

A loyalty team wants the customer who spends the most in each product category so they can plan outreach. For every category, return that customer and their total spend across the category.

## Worked solution and explanation

### What this problem is really testing

Strip the loyalty framing and this is the classic top-1-per-group. The skill being probed is whether you keep two concerns separate: rolling spend up to the (category, customer) grain, and then picking the winner within each category. Anyone can write the SUM. The tell is which window function you reach for, because RANK and ROW_NUMBER give different answers the moment two customers tie at the top, and only one of them is correct here.

---

### Break down the requirements

#### Step 1: Attach a category to every transaction

The category lives in products, not transactions, so join on product_id first. This is an inner join on purpose: a transaction whose product has no category row cannot belong to any category leaderboard, so dropping it is correct, not a bug.

#### Step 2: Roll up to (category, customer) spend

GROUP BY category, user_id and SUM(total_amount). This collapses many transactions per customer into one total per category, which is the grain the question actually cares about. Do this in a CTE so the ranking step reads cleanly.

#### Step 3: Rank within each category and keep the top

RANK() OVER (PARTITION BY category ORDER BY total_spent DESC) numbers customers per category, biggest spender first. Filter spend_rank = 1. RANK gives every tied top spender the value 1, so a genuine tie surfaces both customers instead of silently discarding one.

---

### The solution

**Top spender per category**

```sql
WITH category_spend AS (
  SELECT p.category AS category,
         t.user_id AS user_id,
         SUM(t.total_amount) AS total_spent
  FROM transactions t
  JOIN products p ON t.product_id = p.product_id
  GROUP BY p.category, t.user_id
),
ranked AS (
  SELECT category,
         user_id,
         total_spent,
         RANK() OVER (PARTITION BY category ORDER BY total_spent DESC) AS spend_rank
  FROM category_spend
)
SELECT category, user_id, total_spent
FROM ranked
WHERE spend_rank = 1
ORDER BY category, user_id
```

**RANK() (correct)**

Ties both get rank 1, so a category with two customers at the same top total returns both rows. That matches 'the customer who spends the most', which is a set, not a guarantee of one row.

**ROW_NUMBER() (subtly wrong)**

ROW_NUMBER assigns 1 and 2 to tied customers arbitrarily, so filtering to 1 keeps whichever row the engine happened to order first and drops a real co-leader. It looks right on clean fixtures and hides the bug until a tie appears in production.

> **Common Pitfall**
>
> Reaching for ROW_NUMBER out of habit, or trying to do it with a self-join to MAX(total_spent) per category. The MAX self-join works but recomputes the aggregate twice and gets awkward the moment you want the runner-up too. The windowed CTE scales to top-N with a one-character change.

> **Cost Analysis**
>
> At scale imagine transactions at 200M rows and 20 GB. The join and GROUP BY hash-aggregate in one pass down to a few million (category, customer) buckets, and the window partition sorts only that small intermediate, not the raw fact table. If transactions is partitioned by transaction_date, add a date filter to prune before the aggregate; without one you scan the whole fact table by design.

> **Interviewers Watch For**
>
> Whether you say out loud what happens on a tie before you type, and whether you can name why RANK beats ROW_NUMBER here. Bonus signal: noticing that the inner join silently excludes transactions with no matching product, and confirming that is the intended behavior rather than an accident.

---

## Common follow-up questions

- Now return the top three spenders per category instead of just the leader. _(Tests whether they see the window CTE generalizes: change the filter to spend_rank <= 3, and whether they switch to DENSE_RANK to avoid gaps skipping the third slot.)_
- Scope this to the last full year of activity. Where does the date predicate go? _(Probes partition pruning: the transaction_date filter belongs before the aggregate so it prunes the fact scan, not after the window.)_
- What if a category has no transactions at all? Should it appear? _(Surfaces inner-join semantics and whether they would switch to a products-driven LEFT JOIN to show empty categories with a NULL top spender.)_

## Related

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