Dream11: Contests and Prize Pools per Sport in May 2025
For a monthly business review, Dream11's strategy team wants to see how much prize money was put up in each sport.
- Difficulty: Easy
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #629
Problem statement
For a monthly business review, Dream11's strategy team wants to see how much prize money was put up in each sport.
Consider the contests on matches whose start_time falls in May 2025 (from 2025-05-01 00:00:00 up to, but not including, 2025-06-01 00:00:00). For each sport with at least one such contest, return sport, contests (how many) and total_prize_pool. Order by total_prize_pool descending, then by sport.
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 |
| sport | enum(cricket, football, kabaddi) |
| fixture | varchar |
| start_time | datetime |
Primary key: match_id.
One row per real-world match the app lists.
Table: Contest
| Column | Type |
|---|---|
| contest_id | int |
| match_id | int |
| prize_pool | int |
Primary key: contest_id.
One row per contest on a match; prize_pool is the advertised total prize in rupees.
Examples
Example 1
FixtureMatch
| match_id | sport | fixture | start_time |
|---|---|---|---|
| 1 | cricket | MUM vs CHE | 2025-04-30 19:30:00 |
| 2 | cricket | BLR vs KOL | 2025-05-03 19:30:00 |
| 3 | football | Goa vs Kerala | 2025-05-31 23:30:00 |
| 4 | kabaddi | Patna vs Jaipur | 2025-06-01 00:00:00 |
| 5 | kabaddi | Patna vs Jaipur | 2025-05-12 20:00:00 |
Contest
| contest_id | match_id | prize_pool |
|---|---|---|
| 10 | 1 | 500000 |
| 11 | 2 | 1000000 |
| 12 | 2 | 25000 |
| 13 | 3 | 200000 |
| 14 | 4 | 50000 |
| 15 | 5 | 150000 |
| 16 | 5 | 50000 |
Output
| sport | contests | total_prize_pool |
|---|---|---|
| cricket | 2 | 1025000 |
| football | 1 | 200000 |
| kabaddi | 2 | 200000 |
How to solve Dream11: Contests and Prize Pools per Sport in May 2025
The date that decides the month is on the match, while the money is on the contest, so the query joins Contest to FixtureMatch, keeps the rows whose match starts in May 2025, and groups by sport.
The month filter is written as a half-open range: start_time >= '2025-05-01 00:00:00' AND start_time < '2025-06-01 00:00:00'. That keeps a match at 23:30 on 31 May and drops one at exactly midnight on 1 June, without having to know how many days May has or what the last second of the day is. Because the timestamps are stored in a fixed format, comparing them as text is the same as comparing them as times, and the range lets an index on start_time do the work.
The alternatives test YEAR() and MONTH(), or format the timestamp to '%Y-%m' — equally correct, but a function on the column cannot use an index. A sport with no May contests has no rows after the join, so it does not appear. Ties in the total are ordered by sport name.
Reference solution (MySQL)
SELECT m.sport, COUNT(*) AS contests, SUM(c.prize_pool) AS total_prize_pool
FROM Contest c
JOIN FixtureMatch m ON m.match_id = c.match_id
WHERE m.start_time >= '2025-05-01 00:00:00' AND m.start_time < '2025-06-01 00:00:00'
GROUP BY m.sport
ORDER BY total_prize_pool DESC, m.sportAnother way
SELECT m.sport, COUNT(c.contest_id) AS contests, SUM(c.prize_pool) AS total_prize_pool
FROM FixtureMatch m JOIN Contest c ON c.match_id = m.match_id
WHERE YEAR(m.start_time) = 2025 AND MONTH(m.start_time) = 5
GROUP BY m.sport ORDER BY total_prize_pool DESC, m.sportAnother way
SELECT sport, COUNT(*) AS contests, SUM(prize_pool) AS total_prize_pool
FROM (SELECT m.sport, c.prize_pool FROM Contest c JOIN FixtureMatch m ON m.match_id = c.match_id
WHERE DATE_FORMAT(m.start_time, '%Y-%m') = '2025-05') x
GROUP BY sport ORDER BY total_prize_pool DESC, sport← Dream11: Withdrawals That Missed the 24-Hour Payout Promise · Dream11: Deposits Above a User's Own Monthly Limit →