Dream11: The Moment Each Contest Filled Up

Medium
635.

Dream11: The Moment Each Contest Filled Up

MediumWindow Functions

MySQL

Dream11's contest-design team measures how fast contests fill to decide how many to create for the next match. A contest filled at the moment its total_spots-th entry joined. Entries take spots in order of joined_at, and when two entries share a timestamp the smaller entry_id came first. During traffic bursts a few extra entries can slip in after a contest is full (they are refunded later); they do not change when it filled.

For every contest that filled, return contest_id and filled_at. Contests that never reached total_spots entries are left out. Order by filled_at, then contest_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: Contest

ColumnType
contest_idint
total_spotsint

Primary key: contest_id.

How many teams the contest admits.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
joined_atdatetime

Primary key: entry_id.

One row per team that joined a contest.

Example

The rows must come back in the order shown.

Contest

contest_idtotal_spots
13
22
34
42

ContestEntry

entry_idcontest_iduser_idjoined_at
101712025-05-10 18:00:05
111722025-05-10 18:01:00
121732025-05-10 18:02:30
131742025-05-10 18:02:30
162752025-05-10 18:03:00
142762025-05-10 18:04:00
152772025-05-10 18:04:00
173712025-05-10 18:00:00
183782025-05-10 18:05:00

Output

contest_idfilled_at
12025-05-10 18:02:30
22025-05-10 18:04:00
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: The Moment Each Contest Filled Up — SQL Problem

Dream11's contest-design team measures how fast contests fill to decide how many to create for the next match.

  • Difficulty: Medium
  • Topics: Window Functions
  • Dialect: MySQL
  • Problem: #635

Problem statement

Dream11's contest-design team measures how fast contests fill to decide how many to create for the next match. A contest filled at the moment its total_spots-th entry joined. Entries take spots in order of joined_at, and when two entries share a timestamp the smaller entry_id came first. During traffic bursts a few extra entries can slip in after a contest is full (they are refunded later); they do not change when it filled.

For every contest that filled, return contest_id and filled_at. Contests that never reached total_spots entries are left out. Order by filled_at, then contest_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: Contest

ColumnType
contest_idint
total_spotsint

Primary key: contest_id.

How many teams the contest admits.

Table: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
joined_atdatetime

Primary key: entry_id.

One row per team that joined a contest.

Examples

Example 1

Contest

contest_idtotal_spots
13
22
34
42

ContestEntry

entry_idcontest_iduser_idjoined_at
101712025-05-10 18:00:05
111722025-05-10 18:01:00
121732025-05-10 18:02:30
131742025-05-10 18:02:30
162752025-05-10 18:03:00
142762025-05-10 18:04:00
152772025-05-10 18:04:00
173712025-05-10 18:00:00
183782025-05-10 18:05:00

Output

contest_idfilled_at
12025-05-10 18:02:30
22025-05-10 18:04:00

How to solve Dream11: The Moment Each Contest Filled Up

Give every entry its spot number within its contest — ROW_NUMBER() OVER (PARTITION BY contest_id ORDER BY joined_at, entry_id) — and the answer is the entry whose spot number equals the contest's total_spots. Joining Contest to the numbered entries on both contest_id and spot = total_spots picks out exactly that row, and its joined_at is when the contest filled.

The tie-break matters: two entries that joined in the same second compete for the last spot, and the statement says the smaller entry_id wins it. Because the window order includes entry_id, the numbering is total and deterministic. Over-filled contests are handled for free — the extra entries get numbers above total_spots and are never matched — and contests that did not fill have no row with that number, so the inner join drops them.

The alternative counts, for each entry, how many entries of its contest came at or before it (a correlated subquery that spells out the same order) and keeps the one whose count equals the capacity. It is quadratic per contest; the window version sorts each contest once.

Reference solution (MySQL)

WITH seat AS (
  SELECT contest_id, joined_at,
         ROW_NUMBER() OVER (PARTITION BY contest_id ORDER BY joined_at, entry_id) AS spot
  FROM ContestEntry
)
SELECT c.contest_id, s.joined_at AS filled_at
FROM Contest c
JOIN seat s ON s.contest_id = c.contest_id AND s.spot = c.total_spots
ORDER BY filled_at, c.contest_id

Another way

SELECT c.contest_id, e.joined_at AS filled_at
FROM Contest c JOIN ContestEntry e ON e.contest_id = c.contest_id
WHERE (SELECT COUNT(*) FROM ContestEntry b WHERE b.contest_id = e.contest_id
         AND (b.joined_at < e.joined_at OR (b.joined_at = e.joined_at AND b.entry_id <= e.entry_id))) = c.total_spots
ORDER BY filled_at, c.contest_id

← Dream11: Head-to-Head Fixtures Still to Play in Private Leagues · Dream11: Contest Entries by Format for Each Match →