Dream11: Contests and Prize Pools per Sport in May 2025

Easy
629.

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.

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

ColumnTypeValues
match_idint
sportenumcricket, football, kabaddi
fixturevarchar
start_timedatetime

Primary key: match_id.

One row per real-world match the app lists.

Table: Contest

ColumnType
contest_idint
match_idint
prize_poolint

Primary key: contest_id.

One row per contest on a match; prize_pool is the advertised total prize in rupees.

Example

The rows must come back in the order shown.

FixtureMatch

match_idsportfixturestart_time
1cricketMUM vs CHE2025-04-30 19:30:00
2cricketBLR vs KOL2025-05-03 19:30:00
3footballGoa vs Kerala2025-05-31 23:30:00
4kabaddiPatna vs Jaipur2025-06-01 00:00:00
5kabaddiPatna vs Jaipur2025-05-12 20:00:00

Contest

contest_idmatch_idprize_pool
101500000
1121000000
12225000
133200000
14450000
155150000
16550000

Output

sportconteststotal_prize_pool
cricket21025000
football1200000
kabaddi2200000
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: 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

ColumnType
match_idint
sportenum(cricket, football, kabaddi)
fixturevarchar
start_timedatetime

Primary key: match_id.

One row per real-world match the app lists.

Table: Contest

ColumnType
contest_idint
match_idint
prize_poolint

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_idsportfixturestart_time
1cricketMUM vs CHE2025-04-30 19:30:00
2cricketBLR vs KOL2025-05-03 19:30:00
3footballGoa vs Kerala2025-05-31 23:30:00
4kabaddiPatna vs Jaipur2025-06-01 00:00:00
5kabaddiPatna vs Jaipur2025-05-12 20:00:00

Contest

contest_idmatch_idprize_pool
101500000
1121000000
12225000
133200000
14450000
155150000
16550000

Output

sportconteststotal_prize_pool
cricket21025000
football1200000
kabaddi2200000

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.sport

Another 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.sport

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