Dream11: Top Three Users by Net Winnings in Each Sport
For a season-end feature, Dream11's community team wants the most successful players in each sport by net winnings: everything a user won in the sport minus…
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #643
Problem statement
For a season-end feature, Dream11's community team wants the most successful players in each sport by net winnings: everything a user won in the sport minus every entry fee they paid in it.
Compute each user's net per sport over all their entries. Rank users within a sport by net, highest first, with dense ranking (equal nets share a rank, and the next distinct net takes the next number). Return the users at rank 3 or better whose net is greater than 0: sport, username, net_winnings and sport_rank. Order by sport, then sport_rank, then username.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: AppUser
| Column | Type |
|---|---|
| user_id | int |
| username | varchar |
Primary key: user_id.
Usernames are unique.
Table: FixtureMatch
| Column | Type |
|---|---|
| match_id | int |
| sport | enum(cricket, football, kabaddi) |
Primary key: match_id.
One row per match.
Table: Contest
| Column | Type |
|---|---|
| contest_id | int |
| match_id | int |
| entry_fee | int |
Primary key: contest_id.
One row per contest on a match.
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| contest_id | int |
| user_id | int |
| winnings | int |
Primary key: entry_id.
One row per team entered; winnings is 0 for a team that won nothing.
Examples
Example 1
AppUser
| user_id | username |
|---|---|
| 1 | aarav_21 |
| 2 | diya_07 |
| 3 | kabir_88 |
| 4 | meera_14 |
| 5 | rohan_33 |
FixtureMatch
| match_id | sport |
|---|---|
| 1 | cricket |
| 2 | cricket |
| 3 | kabaddi |
Contest
| contest_id | match_id | entry_fee |
|---|---|---|
| 10 | 1 | 50 |
| 11 | 2 | 100 |
| 12 | 3 | 25 |
ContestEntry
| entry_id | contest_id | user_id | winnings |
|---|---|---|---|
| 100 | 10 | 1 | 500 |
| 101 | 10 | 2 | 0 |
| 102 | 11 | 2 | 400 |
| 103 | 11 | 3 | 250 |
| 104 | 10 | 4 | 100 |
| 105 | 11 | 4 | 200 |
| 106 | 10 | 5 | 350 |
| 107 | 12 | 1 | 0 |
| 108 | 12 | 3 | 125 |
| 109 | 12 | 5 | 0 |
Output
| sport | username | net_winnings | sport_rank |
|---|---|---|---|
| cricket | aarav_21 | 450 | 1 |
| cricket | rohan_33 | 300 | 2 |
| cricket | diya_07 | 250 | 3 |
| kabaddi | kabir_88 | 100 | 1 |
How to solve Dream11: Top Three Users by Net Winnings in Each Sport
The money is spread over three tables: the winnings on the entry, the fee on the contest, the sport on the match. So the first CTE joins them and aggregates per (sport, user), giving SUM(winnings) − SUM(entry_fee) — a user who played five contests and won one may well be negative.
Ranking then happens inside each sport: DENSE_RANK() OVER (PARTITION BY sport ORDER BY net_winnings DESC). Dense ranking is the statement's rule — two users level on the top net are both rank 1 and the next net is rank 2, so "top three" can be more than three people but never skips a level. The filter on rank and on a positive net comes after ranking, in the outer query; filtering positive nets first would not change who is on top here, but filtering inside the window query would be wrong for any rule that counts everyone. A sport where nobody is up yields no rows.
The alternative computes the dense rank without a window: one plus the number of distinct nets above this one in the same sport. It is quadratic in the number of users per sport; the window function is a sort per sport.
Reference solution (MySQL)
WITH net AS (
SELECT m.sport, e.user_id, SUM(e.winnings) - SUM(c.entry_fee) AS net_winnings
FROM ContestEntry e
JOIN Contest c ON c.contest_id = e.contest_id
JOIN FixtureMatch m ON m.match_id = c.match_id
GROUP BY m.sport, e.user_id
),
ranked AS (
SELECT sport, user_id, net_winnings,
DENSE_RANK() OVER (PARTITION BY sport ORDER BY net_winnings DESC) AS sport_rank
FROM net
)
SELECT r.sport, u.username, r.net_winnings, r.sport_rank
FROM ranked r
JOIN AppUser u ON u.user_id = r.user_id
WHERE r.sport_rank <= 3 AND r.net_winnings > 0
ORDER BY r.sport, r.sport_rank, u.usernameAnother way
WITH net AS (
SELECT m.sport, e.user_id, SUM(e.winnings - c.entry_fee) AS net_winnings
FROM ContestEntry e JOIN Contest c ON c.contest_id = e.contest_id JOIN FixtureMatch m ON m.match_id = c.match_id
GROUP BY m.sport, e.user_id
)
SELECT a.sport, u.username, a.net_winnings,
1 + (SELECT COUNT(DISTINCT b.net_winnings) FROM net b WHERE b.sport = a.sport AND b.net_winnings > a.net_winnings) AS sport_rank
FROM net a JOIN AppUser u ON u.user_id = a.user_id
WHERE a.net_winnings > 0
AND (SELECT COUNT(DISTINCT b.net_winnings) FROM net b WHERE b.sport = a.sport AND b.net_winnings > a.net_winnings) < 3
ORDER BY a.sport, sport_rank, u.username← Dream11: Longest Run of Matches With the Same Captain · Dream11: Contest Winners From Player Points With C and VC Boosts →