Dream11: Top Three Users by Net Winnings in Each Sport

Hard
643.

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

ColumnType
user_idint
usernamevarchar

Primary key: user_id.

Usernames are unique.

Table: FixtureMatch

ColumnTypeValues
match_idint
sportenumcricket, football, kabaddi

Primary key: match_id.

One row per match.

Table: Contest

ColumnType
contest_idint
match_idint
entry_feeint

Primary key: contest_id.

One row per contest on a match.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
winningsint

Primary key: entry_id.

One row per team entered; winnings is 0 for a team that won nothing.

Example

The rows must come back in the order shown.

AppUser

user_idusername
1aarav_21
2diya_07
3kabir_88
4meera_14
5rohan_33

FixtureMatch

match_idsport
1cricket
2cricket
3kabaddi

Contest

contest_idmatch_identry_fee
10150
112100
12325

ContestEntry

entry_idcontest_iduser_idwinnings
100101500
1011020
102112400
103113250
104104100
105114200
106105350
1071210
108123125
1091250

Output

sportusernamenet_winningssport_rank
cricketaarav_214501
cricketrohan_333002
cricketdiya_072503
kabaddikabir_881001
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
usernamevarchar

Primary key: user_id.

Usernames are unique.

Table: FixtureMatch

ColumnType
match_idint
sportenum(cricket, football, kabaddi)

Primary key: match_id.

One row per match.

Table: Contest

ColumnType
contest_idint
match_idint
entry_feeint

Primary key: contest_id.

One row per contest on a match.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
winningsint

Primary key: entry_id.

One row per team entered; winnings is 0 for a team that won nothing.

Examples

Example 1

AppUser

user_idusername
1aarav_21
2diya_07
3kabir_88
4meera_14
5rohan_33

FixtureMatch

match_idsport
1cricket
2cricket
3kabaddi

Contest

contest_idmatch_identry_fee
10150
112100
12325

ContestEntry

entry_idcontest_iduser_idwinnings
100101500
1011020
102112400
103113250
104104100
105114200
106105350
1071210
108123125
1091250

Output

sportusernamenet_winningssport_rank
cricketaarav_214501
cricketrohan_333002
cricketdiya_072503
kabaddikabir_881001

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

Another 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 →