Dream11: Private Leagues Cancelled Below Their Minimum Size
A private league on Dream11 is created by a user for friends, with a minimum number of teams.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #637
Problem statement
A private league on Dream11 is created by a user for friends, with a minimum number of teams. If fewer than min_teams teams have joined when the match locks, the league is cancelled and every entry fee is refunded. Finance needs the refund list.
For every private league with fewer than min_teams entries (including leagues nobody joined), return league_name, creator (the creator's username), teams_joined and refund_due (teams_joined × entry_fee). Order by refund_due descending, then by league_name.
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.
One row per account.
Table: PrivateLeague
| Column | Type |
|---|---|
| league_id | int |
| league_name | varchar |
| creator_id | int |
| entry_fee | int |
| min_teams | int |
Primary key: league_id.
League names are unique. creator_id is a user.
Table: LeagueEntry
| Column | Type |
|---|---|
| entry_id | int |
| league_id | int |
| user_id | int |
Primary key: entry_id.
One row per team entered; the creator's own team is an entry like any other.
Examples
Example 1
AppUser
| user_id | username |
|---|---|
| 1 | aarav_21 |
| 2 | diya_07 |
| 3 | kabir_88 |
| 4 | meera_14 |
PrivateLeague
| league_id | league_name | creator_id | entry_fee | min_teams |
|---|---|---|---|---|
| 10 | Hostel B Warriors | 1 | 50 | 3 |
| 11 | Office Super Over | 2 | 100 | 4 |
| 12 | College Gang | 3 | 25 | 2 |
| 13 | Sunday Squad | 4 | 200 | 2 |
LeagueEntry
| entry_id | league_id | user_id |
|---|---|---|
| 100 | 10 | 1 |
| 101 | 10 | 2 |
| 102 | 11 | 2 |
| 103 | 11 | 3 |
| 104 | 12 | 3 |
| 105 | 12 | 4 |
| 106 | 12 | 1 |
Output
| league_name | creator | teams_joined | refund_due |
|---|---|---|---|
| Office Super Over | diya_07 | 2 | 200 |
| Hostel B Warriors | aarav_21 | 2 | 100 |
| Sunday Squad | meera_14 | 0 | 0 |
How to solve Dream11: Private Leagues Cancelled Below Their Minimum Size
Every league needs its entry count, including leagues with none, so PrivateLeague is LEFT JOINed to LeagueEntry and grouped per league. The creator's name comes from an inner join to AppUser — every league has a creator, so it loses nothing.
Counting needs care. A league nobody joined contributes one row whose entry columns are NULL; COUNT(*) would call that 1 team, while COUNT(e.entry_id) correctly says 0. The cancellation test compares the count with that league's own minimum, which is why min_teams is in the GROUP BY and the test sits in HAVING. The refund is the count times the fee — 0 for an empty league, which still appears because it was cancelled.
The alternative computes the count with a correlated subquery per league (which naturally returns 0 for an empty league) and filters outside. Both read each league's entries once with an index on LeagueEntry.league_id. Ties on the refund are broken by the league's unique name.
Reference solution (MySQL)
SELECT l.league_name, u.username AS creator, COUNT(e.entry_id) AS teams_joined,
COUNT(e.entry_id) * l.entry_fee AS refund_due
FROM PrivateLeague l
JOIN AppUser u ON u.user_id = l.creator_id
LEFT JOIN LeagueEntry e ON e.league_id = l.league_id
GROUP BY l.league_id, l.league_name, u.username, l.entry_fee, l.min_teams
HAVING COUNT(e.entry_id) < l.min_teams
ORDER BY refund_due DESC, l.league_nameAnother way
SELECT league_name, creator, teams_joined, teams_joined * entry_fee AS refund_due FROM (
SELECT l.league_name, u.username AS creator, l.entry_fee, l.min_teams,
(SELECT COUNT(*) FROM LeagueEntry e WHERE e.league_id = l.league_id) AS teams_joined
FROM PrivateLeague l JOIN AppUser u ON u.user_id = l.creator_id) t
WHERE teams_joined < min_teams
ORDER BY refund_due DESC, league_name← Dream11: Contest Entries by Format for Each Match · Dream11: Withdrawal Requests Made Before KYC Approval →