Crowned Together

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

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.

Some thrones seat more than one.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

transactions

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

Expected output

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.

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.

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