Dream11: Private Leagues Cancelled Below Their Minimum Size

Medium
637.

Dream11: Private Leagues Cancelled Below Their Minimum Size

MediumJoinsAggregation

MySQL

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

ColumnType
user_idint
usernamevarchar

Primary key: user_id.

One row per account.

Table: PrivateLeague

ColumnType
league_idint
league_namevarchar
creator_idint
entry_feeint
min_teamsint

Primary key: league_id.

League names are unique. creator_id is a user.

Table: LeagueEntry

ColumnType
entry_idint
league_idint
user_idint

Primary key: entry_id.

One row per team entered; the creator's own team is an entry like any other.

Example

The rows must come back in the order shown.

AppUser

user_idusername
1aarav_21
2diya_07
3kabir_88
4meera_14

PrivateLeague

league_idleague_namecreator_identry_feemin_teams
10Hostel B Warriors1503
11Office Super Over21004
12College Gang3252
13Sunday Squad42002

LeagueEntry

entry_idleague_iduser_id
100101
101102
102112
103113
104123
105124
106121

Output

league_namecreatorteams_joinedrefund_due
Office Super Overdiya_072200
Hostel B Warriorsaarav_212100
Sunday Squadmeera_1400
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: 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

ColumnType
user_idint
usernamevarchar

Primary key: user_id.

One row per account.

Table: PrivateLeague

ColumnType
league_idint
league_namevarchar
creator_idint
entry_feeint
min_teamsint

Primary key: league_id.

League names are unique. creator_id is a user.

Table: LeagueEntry

ColumnType
entry_idint
league_idint
user_idint

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_idusername
1aarav_21
2diya_07
3kabir_88
4meera_14

PrivateLeague

league_idleague_namecreator_identry_feemin_teams
10Hostel B Warriors1503
11Office Super Over21004
12College Gang3252
13Sunday Squad42002

LeagueEntry

entry_idleague_iduser_id
100101
101102
102112
103113
104123
105124
106121

Output

league_namecreatorteams_joinedrefund_due
Office Super Overdiya_072200
Hostel B Warriorsaarav_212100
Sunday Squadmeera_1400

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_name

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