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
| Column | Type |
|---|---|
| match_id | int |
| fixture | varchar |
Primary key: match_id.
One row per match listed in the app.
Table: Contest
| Column | Type |
|---|---|
| contest_id | int |
| match_id | int |
| contest_type | enum(mega, small_league, head_to_head, private) |
Primary key: contest_id.
One row per contest on a match.
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| contest_id | int |
| user_id | int |
Primary key: entry_id.
One row per team entered into a contest.
Examples
Example 1
FixtureMatch
| match_id | fixture |
|---|---|
| 1 | MUM vs CHE |
| 2 | Patna vs Jaipur |
| 3 | Goa vs Kerala |
| 4 | IND vs AUS |
Contest
| contest_id | match_id | contest_type |
|---|---|---|
| 10 | 1 | mega |
| 11 | 1 | head_to_head |
| 12 | 1 | private |
| 13 | 2 | small_league |
| 14 | 2 | mega |
| 15 | 3 | mega |
ContestEntry
| entry_id | contest_id | user_id |
|---|---|---|
| 100 | 10 | 71 |
| 101 | 10 | 72 |
| 102 | 11 | 71 |
| 103 | 11 | 73 |
| 104 | 12 | 74 |
| 105 | 13 | 71 |
| 106 | 13 | 75 |
| 107 | 14 | 76 |
| 108 | 14 | 77 |
| 109 | 14 | 78 |
Output
| match_id | mega_entries | h2h_entries | other_entries | total_entries |
|---|---|---|---|---|
| 1 | 2 | 2 | 1 | 5 |
| 2 | 3 | 0 | 2 | 5 |
| 3 | 0 | 0 | 0 | 0 |
| 4 | 0 | 0 | 0 | 0 |
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_idAnother 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 →