# First Among Many

> Each segment has a favorite category.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We group users by account status, and every segment leans toward one product category more than the rest. For each status, surface the category with the most purchases, where a purchase is a single transaction regardless of how many units it moved. Walk through the segments from the lowest account status upward, and when a status has two categories tied for the lead, let the one that comes first alphabetically take the spot.

## Worked solution and explanation

### What this is really asking

Strip the marketing veneer and this is top-per-group with ties kept, where the group is `account_status` and the winner count is one. Everyone can join three tables and count; the ranking step is what separates candidates. Aggregate to one row per (status, category) first, then rank those groups inside each status. Reach for `ROW_NUMBER` and you silently pick one winner when two categories tie, dropping legitimate answers; RANK keeps every category that reaches the top count. Over 200M transactions you also have no choice but to aggregate before you rank, so you rank a few hundred groups, not rows.

---

### Break down the requirements

#### Step 1: Join and count per pair

Join transactions to users (for `account_status`) and products (for category), then COUNT(*) grouped by (`account_status`, category). That collapses 200M rows to at most a few hundred groups. Note it is COUNT(*) of rows, not SUM(quantity): a five-unit basket is still one purchase, and weighting by quantity flips winners in any segment with a big-basket outlier.

#### Step 2: Rank inside each segment

RANK() OVER (PARTITION BY `account_status` ORDER BY COUNT(*) DESC). Using RANK keeps every tied top category; `ROW_NUMBER` would collapse a real tie to one arbitrary winner and quietly delete the rest.

#### Step 3: Filter and order

Keep rk = 1 and ORDER BY `account_status`, category. The outer sort is by category name, not by count, because all surviving rows already share the top count within their segment.

---

### The solution

**TOP CATEGORY PER ACCOUNT STATUS**

```sql
WITH ranked AS (
  SELECT
    u.account_status,
    p.category,
    COUNT(*) AS purchase_count,
    RANK() OVER (
      PARTITION BY u.account_status
      ORDER BY COUNT(*) DESC
    ) AS rk
  FROM transactions t
  JOIN users u    ON t.user_id    = u.user_id
  JOIN products p ON t.product_id = p.product_id
  GROUP BY u.account_status, p.category
)
SELECT account_status, category, purchase_count
FROM ranked
WHERE rk = 1
ORDER BY account_status, category;
```

> **Cost Analysis**
>
> Two hash joins fan transactions out by `user_id` and `product_id`, then a single GROUP BY collapses to (status, category) cardinality. The window runs over that small set, so ranking cost is negligible. The shuffle on `user_id` dominates the plan.

> **Interviewers Watch For**
>
> The tell is which ranking function you reach for. RANK and `DENSE_RANK` both keep every category tied at the top; `ROW_NUMBER` keeps exactly one and drops the rest. On the sample, the inactive segment ties Garden and Sports at two purchases each, so `ROW_NUMBER` would erase a correct row. Pick on purpose and say why.

> **Common Pitfall**
>
> Weighting by SUM(quantity) instead of COUNT(*). The transactions table dangles quantity and `total_amount`, but a purchase is one row. On the sample, quantity-weighting would hand active to Automotive (one nine-unit order) over Electronics (three separate orders), the wrong answer.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would the answer change if you used `DENSE_RANK` instead of RANK? _(Same result here because we only keep rk = 1, but the values differ for non-winners; explain when each matters.)_
- What if an `account_status` has zero transactions? _(It disappears from the output. Discuss a LEFT JOIN from a status dimension if you need every segment present, even empty ones.)_
- How would you weight by `total_amount` instead of count? _(Swap COUNT(*) for SUM(`t.total_amount`) in both the aggregate and the ORDER BY of the window. Ties become much rarer.)_

## Related

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