Dream11: Contest Entries by Format for Each Match

Medium
636.

Dream11: Contest Entries by Format for Each Match

Dream11's match-day dashboard shows where users put their teams: mega contests, head-to-heads or the smaller formats.

For every match — including matches with no contests or no entries — return match_id, mega_entries, h2h_entries (contest type head_to_head), other_entries (small_league and private together) and total_entries. Counts with nothing to count are 0. Order by total_entries descending, then by match_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: FixtureMatch

ColumnType
match_idint
fixturevarchar

Primary key: match_id.

One row per match listed in the app.

Table: Contest

ColumnTypeValues
contest_idint
match_idint
contest_typeenummega, small_league, head_to_head, private

Primary key: contest_id.

One row per contest on a match.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint

Primary key: entry_id.

One row per team entered into a contest.

Example

The rows must come back in the order shown.

FixtureMatch

match_idfixture
1MUM vs CHE
2Patna vs Jaipur
3Goa vs Kerala
4IND vs AUS

Contest

contest_idmatch_idcontest_type
101mega
111head_to_head
121private
132small_league
142mega
153mega

ContestEntry

entry_idcontest_iduser_id
1001071
1011072
1021171
1031173
1041274
1051371
1061375
1071476
1081477
1091478

Output

match_idmega_entriesh2h_entriesother_entriestotal_entries
12215
23025
30000
40000
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: Contest Entries by Format for Each Match

Dream11's match-day dashboard shows where users put their teams: mega contests, head-to-heads or the smaller formats.

  • Difficulty: Medium
  • Topics: Conditional Logic, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #636

Problem statement

Dream11's match-day dashboard shows where users put their teams: mega contests, head-to-heads or the smaller formats.

For every match — including matches with no contests or no entries — return match_id, mega_entries, h2h_entries (contest type head_to_head), other_entries (small_league and private together) and total_entries. Counts with nothing to count are 0. Order by total_entries descending, then by match_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: FixtureMatch

ColumnType
match_idint
fixturevarchar

Primary key: match_id.

One row per match listed in the app.

Table: Contest

ColumnType
contest_idint
match_idint
contest_typeenum(mega, small_league, head_to_head, private)

Primary key: contest_id.

One row per contest on a match.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint

Primary key: entry_id.

One row per team entered into a contest.

Examples

Example 1

FixtureMatch

match_idfixture
1MUM vs CHE
2Patna vs Jaipur
3Goa vs Kerala
4IND vs AUS

Contest

contest_idmatch_idcontest_type
101mega
111head_to_head
121private
132small_league
142mega
153mega

ContestEntry

entry_idcontest_iduser_id
1001071
1011072
1021171
1031173
1041274
1051371
1061375
1071476
1081477
1091478

Output

match_idmega_entriesh2h_entriesother_entriestotal_entries
12215
23025
30000
40000

How to solve Dream11: Contest Entries by Format for Each Match

The output has one row per match and one column per format, a classic conditional aggregation (a manual pivot). Starting from FixtureMatch and LEFT JOINing contests and then entries keeps every match, even one with nothing under it, and grouping by match_id folds its rows into one.

Each format column is SUM(CASE WHEN <format> AND e.entry_id IS NOT NULL THEN 1 ELSE 0 END). The second half of the condition is the subtle part: a contest that has no entries still survives the first left join and appears once with NULLs on the entry side — counting it would add a phantom entry. COUNT(e.entry_id) for the total skips those NULLs by itself. A match with no contests yields one all-NULL row, so every sum is 0, as required.

The alternative pivots only real entries (inner join of contests and entries) in a derived table and left-joins it to the matches, turning the missing groups into zeros with COALESCE. Both read each table once.

Reference solution (MySQL)

SELECT m.match_id,
       SUM(CASE WHEN c.contest_type = 'mega' AND e.entry_id IS NOT NULL THEN 1 ELSE 0 END) AS mega_entries,
       SUM(CASE WHEN c.contest_type = 'head_to_head' AND e.entry_id IS NOT NULL THEN 1 ELSE 0 END) AS h2h_entries,
       SUM(CASE WHEN c.contest_type IN ('small_league', 'private') AND e.entry_id IS NOT NULL THEN 1 ELSE 0 END) AS other_entries,
       COUNT(e.entry_id) AS total_entries
FROM FixtureMatch m
LEFT JOIN Contest c ON c.match_id = m.match_id
LEFT JOIN ContestEntry e ON e.contest_id = c.contest_id
GROUP BY m.match_id
ORDER BY total_entries DESC, m.match_id

Another way

SELECT m.match_id,
       COALESCE(s.mega, 0) AS mega_entries, COALESCE(s.h2h, 0) AS h2h_entries,
       COALESCE(s.other, 0) AS other_entries, COALESCE(s.total, 0) AS total_entries
FROM FixtureMatch m
LEFT JOIN (SELECT c.match_id,
                  SUM(IF(c.contest_type = 'mega', 1, 0)) AS mega,
                  SUM(IF(c.contest_type = 'head_to_head', 1, 0)) AS h2h,
                  SUM(IF(c.contest_type IN ('small_league', 'private'), 1, 0)) AS other,
                  COUNT(*) AS total
           FROM Contest c JOIN ContestEntry e ON e.contest_id = c.contest_id
           GROUP BY c.match_id) s ON s.match_id = m.match_id
ORDER BY total_entries DESC, m.match_id

← Dream11: The Moment Each Contest Filled Up · Dream11: Private Leagues Cancelled Below Their Minimum Size →