The Spender Leaderboard

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

L3

Asked in

IBM

Problem

A spend leaderboard that stops at exactly five rows can drop a user who is level with the fifth-place spender, and the growth team's rewards review needs nobody left off. From the transactions table, list every user in the top five spending positions with their total spend and position, biggest spenders first. Users with equal totals share a position, and the next total takes the following number.

Spending speaks. The leaderboard does the listening.

Tables

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

Five positions, not five rows

This is a top-N by standing dressed up as a leaderboard. Anyone can sum total_amount per user_id. What separates candidates is reading 'top five' as five positions, not five rows. A LIMIT 5 returns five rows whatever the totals do. So the first time two users finish level at fifth place, one of them silently falls off the board, and nothing errors to tell you.

Step 1: Total each user's spend first

Collapse transactions to one row per user_id with SUM(total_amount). A standing belongs to a user's total, not to a purchase. Rank individual transactions and one heavy buyer can take several places on the board. Refunds and voided sales stay in the sum: they lower what the user actually spent.

Step 2: Number the positions with DENSE_RANK()

DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) gives equal totals the same number, and the next total takes the very next one. RANK() would skip after a tie (1, 2, 2, 4). ROW_NUMBER() would invent an order between users the data says are level. The window can sit in the same SELECT as the GROUP BY because windows are evaluated after grouping.

Step 3: Filter on the position one level up

A window result does not exist yet when WHERE runs, so rnk <= 5 lives in an outer query over the derived table ranked. That filter keeps five positions: if two users ever tie for fifth, both stay on the board.

SELECT user_id, total_spend, rnk
FROM (
    SELECT
        user_id,
        SUM(total_amount) AS total_spend,
        DENSE_RANK() OVER (ORDER BY SUM(total_amount) DESC) AS rnk
    FROM transactions
    GROUP BY user_id
) ranked
WHERE rnk <= 5
ORDER BY rnk, user_id;

Five rows

ORDER BY total_spend DESC LIMIT 5 stops after the fifth row it emits. With a tie at fifth, the engine's sort decides which of the level users survives, and the other one vanishes.

Five positions

DENSE_RANK() plus WHERE rnk <= 5 keeps every user whose total sits in the first five positions, so a tie grows the board instead of truncating it.

Common follow-up questions

• What if the board must show exactly five rows, breaking ties by `user_id`? (Now `ROW_NUMBER()` with `user_id` as a second sort key is the right tool.)
• Should refunds and voided sales count toward a user's total? (Tests whether you notice negative and zero `total_amount` rows and can argue the business rule.)
• Could you solve this without a subquery? (Some engines support `QUALIFY` for filtering on a window result inline.)