Dream11: Saved Teams Over the 100-Credit Cap — SQL Problem
Every fantasy team on Dream11 must fit inside 100 credits. After a bad app release, the integrity team suspects some saved teams slipped past the check and…
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #626
Problem statement
Every fantasy team on Dream11 must fit inside 100 credits. After a bad app release, the integrity team suspects some saved teams slipped past the check and wants the list.
Return every team whose players' credits add up to more than 100 (a team at exactly 100 is legal). Return team_id and credits_used, ordered by credits_used descending, then by team_id.
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: Player
| Column | Type |
|---|---|
| player_id | int |
| player_name | varchar |
| credits | decimal |
Primary key: player_id.
The credit price of each player for the match, in steps of 0.5.
Table: TeamPick
| Column | Type |
|---|---|
| team_id | int |
| player_id | int |
Primary key: team_id, player_id.
One row per player in a saved team; every team has 11 players.
Examples
Example 1
Player
| player_id | player_name | credits |
|---|---|---|
| 1 | Arjun Rao | 9.5 |
| 2 | Kabir Khan | 9 |
| 3 | Rohan Iyer | 10.5 |
| 4 | Dev Nair | 8.5 |
| 5 | Harsh Patel | 9 |
| 6 | Ishaan Das | 8 |
| 7 | Karan Mehta | 10 |
| 8 | Nikhil Joshi | 9.5 |
| 9 | Vikram Bose | 8.5 |
| 10 | Rahul Verma | 9 |
| 11 | Farhan Singh | 8 |
| 12 | Vivaan Reddy | 10.5 |
| 13 | Aarav Gupta | 9 |
| 14 | Liam Menon | 8.5 |
TeamPick
| team_id | player_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 1 | 6 |
| 1 | 7 |
| 1 | 8 |
| 1 | 9 |
| 1 | 10 |
| 1 | 11 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
| 2 | 9 |
| 2 | 10 |
| 2 | 12 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 3 | 6 |
| 3 | 7 |
| 3 | 8 |
| 3 | 9 |
| 3 | 10 |
| 3 | 14 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 4 | 5 |
| 4 | 6 |
| 4 | 7 |
| 4 | 8 |
| 4 | 9 |
| 4 | 10 |
| 4 | 11 |
| 4 | 12 |
Output
| team_id | credits_used |
|---|---|
| 2 | 102 |
| 4 | 100.5 |
How to solve Dream11: Saved Teams Over the 100-Credit Cap
Each pick row only names a player, and the price lives in Player, so the first step is a join on player_id. After the join every row is one player in one team with that player's credits, and grouping by team_id turns the eleven rows of a team into one total.
The cap is on that total, which does not exist until the groups are formed, so the filter belongs in HAVING SUM(p.credits) > 100. A team at exactly 100 credits is within the rules and the strict comparison keeps it out of the list. Credits move in steps of 0.5, which are exact in both MySQL decimals and binary floating point, so the sum has no rounding error to worry about.
The alternative computes every team's total in a derived table and filters it with an ordinary WHERE outside — the same plan written in two steps. The ordering puts the worst offenders first and breaks equal totals by team_id so the list is stable.
Reference solution (MySQL)
SELECT t.team_id, SUM(p.credits) AS credits_used
FROM TeamPick t
JOIN Player p ON p.player_id = t.player_id
GROUP BY t.team_id
HAVING SUM(p.credits) > 100
ORDER BY credits_used DESC, t.team_idAnother way
SELECT team_id, credits_used
FROM (SELECT t.team_id, SUM(p.credits) AS credits_used FROM TeamPick t JOIN Player p ON p.player_id = t.player_id GROUP BY t.team_id) x
WHERE credits_used > 100
ORDER BY credits_used DESC, team_id← Dream11: Gross Winnings, Tax Withheld and Net Payout per User · Dream11: Promo Code Redemptions per Campaign →