Dream11: Saved Teams Over the 100-Credit Cap

Easy
626.

Dream11: Saved Teams Over the 100-Credit Cap

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

ColumnType
player_idint
player_namevarchar
creditsdecimal

Primary key: player_id.

The credit price of each player for the match, in steps of 0.5.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key (together): team_id, player_id.

One row per player in a saved team; every team has 11 players.

Example

The rows must come back in the order shown.

Player

player_idplayer_namecredits
1Arjun Rao9.5
2Kabir Khan9
3Rohan Iyer10.5
4Dev Nair8.5
5Harsh Patel9
6Ishaan Das8
7Karan Mehta10
8Nikhil Joshi9.5
9Vikram Bose8.5
10Rahul Verma9
4 more rows

TeamPick

team_idplayer_id
11
12
13
14
15
16
17
18
19
110
34 more rows

Output

team_idcredits_used
2102
4100.5
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: 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

ColumnType
player_idint
player_namevarchar
creditsdecimal

Primary key: player_id.

The credit price of each player for the match, in steps of 0.5.

Table: TeamPick

ColumnType
team_idint
player_idint

Primary key: team_id, player_id.

One row per player in a saved team; every team has 11 players.

Examples

Example 1

Player

player_idplayer_namecredits
1Arjun Rao9.5
2Kabir Khan9
3Rohan Iyer10.5
4Dev Nair8.5
5Harsh Patel9
6Ishaan Das8
7Karan Mehta10
8Nikhil Joshi9.5
9Vikram Bose8.5
10Rahul Verma9
11Farhan Singh8
12Vivaan Reddy10.5
13Aarav Gupta9
14Liam Menon8.5

TeamPick

team_idplayer_id
11
12
13
14
15
16
17
18
19
110
111
21
22
23
24
25
26
27
28
29
210
212
31
32
33
34
35
36
37
38
39
310
314
42
43
44
45
46
47
48
49
410
411
412

Output

team_idcredits_used
2102
4100.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_id

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