The Biggest Fish

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

mid

Asked in

Barclays

Problem

We're planning loyalty outreach around whoever spends the most in each product category, where every purchase in transactions counts toward a customer's total in the category its product carries in products. Going through the categories alphabetically, return each one's top spender with that total; when two customers tie for the top, list the lower user_id first.

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

Tables

transactions

Columns: transaction_id INTEGER, user_id INTEGER, product_id INTEGER, quantity INTEGER, total_amount REAL, transaction_date TEXT

products

Columns: product_id INTEGER, product_name TEXT, category TEXT, price REAL, rating REAL, in_stock INTEGER

Expected output

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

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