# Crowned Together

> Some thrones seat more than one.

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

Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: General Assembly

## Problem

Our VIP award goes to whoever has the most transactions on record in `transactions`, and when several users share that top count we honor every one of them. Pull the winners' usernames from `users`.

## Tables

### users

- user_id INTEGER
- username TEXT
- email TEXT
- signup_date TEXT
- account_status TEXT
- age_bucket TEXT

Sample rows (10 of 200):

| user_id | username | email | signup_date | account_status | age_bucket |
| --- | --- | --- | --- | --- | --- |
| 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 |
| 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 |
| 294 |  amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 |
| 391 | arjun  | arjun@example.com | 2025-05-05 | active | 55-64 |
| 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ |
| 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL |
| 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 |
| 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 |
| 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 |
| 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 |

### 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 | 0 | 0 | 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 | -45 | 2026-01-10 |
| 1670 | 1070 | 1442 | 1 | 144.69 | 2026-02-11 |

## Expected output

First 5 of 10 rows:

| username |
| --- |
| aaron42 |
|  amelia |
| arjun  |
| ava99 |
| andrew_k |

## Worked solution and explanation

### The crown is shared

This is a top-N-with-ties problem wearing the costume of a single winner. The phrase 'the user with the most transactions' begs for `ORDER BY COUNT(*) DESC LIMIT 1`, and that is the trap: on this data five users sit tied at the top, and `LIMIT 1` keeps one of them, chosen by whatever order the engine happened to produce. Everyone gets the count right. What separates candidates is whether they compare every user against the maximum instead of taking the first row. Miss it and four people who earned the award never hear about it.

### Build it in three moves

#### Step 1: Count each user's transactions as `per_user`

Group `transactions` by `user_id` and take `COUNT(*)` as `txn_count`, wrapped as the derived table `per_user`. Each row is one transaction, so `quantity` and `total_amount` are irrelevant here; summing either one answers a different question.

#### Step 2: Find the ceiling with `MAX(txn_count)`

The scalar subquery cannot see `per_user`, so it restates the same aggregate as `all_counts` and takes `MAX(txn_count)` over it. The result is one number: the ceiling every candidate winner is measured against.

#### Step 3: Keep everyone who reaches it

Filter `per_user.txn_count` against that ceiling in the `WHERE`. Every user at the number survives, however many there are, and the join to `users` exists only to swap `user_id` for `username`.

**All users tied at the top count**

```sql
SELECT u.username
FROM (
    SELECT user_id, COUNT(*) AS txn_count
    FROM transactions
    GROUP BY user_id
) per_user
JOIN users u ON u.user_id = per_user.user_id
WHERE per_user.txn_count = (
    SELECT MAX(txn_count)
    FROM (
        SELECT user_id, COUNT(*) AS txn_count
        FROM transactions
        GROUP BY user_id
    ) all_counts
)
```

**Picks one winner**

`ORDER BY COUNT(*) DESC LIMIT 1` returns a single row. With five users tied, which one survives depends on engine order, so it is both wrong and unstable between runs.

**Honors every winner**

`WHERE per_user.txn_count = (SELECT MAX(txn_count) ...)` compares each user to the ceiling. One winner or fifty, the result is exactly the set that earned it.

> **Ask about ties before you write**
>
> Before writing anything, strong candidates ask what happens if two people tie. Asking out loud shows you read 'the user' as a claim to verify against the data, not a promise that the answer is one row.

> **The two counts must stay twins**
>
> `per_user` and `all_counts` must be the same aggregate, character for character. Add a date filter to one and not the other and the ceiling can be a number nobody in `per_user` reaches: the query quietly returns zero winners.

> **Two heavy passes, then pennies**
>
> Each copy of the aggregate is a pass over roughly 80M `transactions` rows down to about 4M users, so this shape pays that cost twice. The `MAX` and the join to `users` for a handful of winners are pennies next to it.

## Common follow-up questions

- How would you compute the per-user count only once? _(Checks whether you reach for a named intermediate (a CTE) or a window like `MAX(txn_count) OVER ()` so the 80M-row aggregation runs a single time.)_
- How would you write this with `DENSE_RANK()` instead of the `MAX` comparison? _(Tests whether you know that `DENSE_RANK() OVER (ORDER BY txn_count DESC) = 1` also keeps ties, while `ROW_NUMBER()` would not.)_
- How would you honor the top three count levels, ties included? _(Checks the jump from one ceiling to `DENSE_RANK() <= 3`, and why `LIMIT 3` breaks the same way `LIMIT 1` did.)_

## Related

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