Dream11: Paid Public Contests Still Under Half Full

Easy
623.

Dream11: Paid Public Contests Still Under Half Full

EasyBasics

MySQL

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

ColumnTypeValues
contest_idint
match_idint
contest_typeenummega, small_league, head_to_head, private
entry_feeint
total_spotsint
spots_filledint

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Contest

contest_idmatch_idcontest_typeentry_feetotal_spotsspots_filled
1101mega49100003120
2101head_to_head9921
3101private25102
4102small_league0204
5102small_league25209
6102mega991000500
7103mega49910098

Output

contest_identry_feespots_left
1496880
52511
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: 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

ColumnType
contest_idint
match_idint
contest_typeenum(mega, small_league, head_to_head, private)
entry_feeint
total_spotsint
spots_filledint

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_idmatch_idcontest_typeentry_feetotal_spotsspots_filled
1101mega49100003120
2101head_to_head9921
3101private25102
4102small_league0204
5102small_league25209
6102mega991000500
7103mega49910098

Output

contest_identry_feespots_left
1496880
52511

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_spots

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