Dream11: Paid Public Contests Still Under Half Full
An hour before the round locks, the contest-operations team at Dream11 looks for paid public contests that are filling slowly, so it can feature them in a…
- Difficulty: Easy
- Topics: Basics
- Dialect: MySQL
- Problem: #623
Problem statement
An hour before the round locks, the contest-operations team at Dream11 looks for paid public contests that are filling slowly, so it can feature them in a push notification.
Return every contest that is not private, has an entry_fee above 0, and has less than half of its spots filled (spots_filled strictly below half of total_spots — a contest exactly half full is not slow). Return contest_id, entry_fee and spots_left (total_spots - spots_filled), in any order.
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: Contest
| Column | Type |
|---|---|
| contest_id | int |
| match_id | int |
| contest_type | enum(mega, small_league, head_to_head, private) |
| entry_fee | int |
| total_spots | int |
| spots_filled | int |
Primary key: contest_id.
One row per contest on a match. entry_fee is in rupees; 0 means a free contest. spots_filled never exceeds total_spots.
Examples
Example 1
Contest
| contest_id | match_id | contest_type | entry_fee | total_spots | spots_filled |
|---|---|---|---|---|---|
| 1 | 101 | mega | 49 | 10000 | 3120 |
| 2 | 101 | head_to_head | 99 | 2 | 1 |
| 3 | 101 | private | 25 | 10 | 2 |
| 4 | 102 | small_league | 0 | 20 | 4 |
| 5 | 102 | small_league | 25 | 20 | 9 |
| 6 | 102 | mega | 99 | 1000 | 500 |
| 7 | 103 | mega | 499 | 100 | 98 |
Output
| contest_id | entry_fee | spots_left |
|---|---|---|
| 1 | 49 | 6880 |
| 5 | 25 | 11 |
How to solve Dream11: Paid Public Contests Still Under Half Full
This is a single-table filter. A contest qualifies when three independent facts are all true, so the WHERE clause joins them with AND: the contest is public (contest_type <> 'private' — private leagues are only visible to invited friends, so a push notification cannot sell them), it is paid (entry_fee > 0), and it is filling slowly.
The slow-fill test is the one to get exactly right. "Less than half" is strict, so a contest at exactly 50% is out. Writing it as spots_filled * 2 < total_spots keeps the arithmetic in integers and makes the boundary obvious; spots_filled < total_spots / 2 is the same test with decimal division, which is how the alternative writes it. The extra column is just the difference of two columns of the same row.
The query reads the table once; an index on (contest_type, entry_fee) would help only if most contests were free or private.
Reference solution (MySQL)
SELECT contest_id, entry_fee, total_spots - spots_filled AS spots_left
FROM Contest
WHERE contest_type <> 'private'
AND entry_fee > 0
AND spots_filled * 2 < total_spotsAnother way
SELECT contest_id, entry_fee, total_spots - spots_filled AS spots_left FROM Contest WHERE contest_type IN ('mega', 'small_league', 'head_to_head') AND entry_fee >= 1 AND spots_filled < total_spots / 2← Apple: Apple Pay Rolling Seven-Day Fraud Rate · Dream11: Depositors Who Never Joined a Contest →