Dream11: Contest Prizes When Tied Teams Share a Rank

Hard
641.

Dream11: Contest Prizes When Tied Teams Share a Rank

When a contest ends, Dream11 ranks its teams by points and pays out a prize table. Teams with equal points share a rank, and the prizes of all the positions they occupy are pooled and split equally among them. For example, if two teams tie for rank 2, they occupy positions 2 and 3 and each receives half of (prize for 2 + prize for 3); the next team is rank 4.

PrizeSlab gives the prize per position for ranges of positions; positions not covered by any slab pay nothing. For every entry that wins more than 0, return contest_id, entry_id, contest_rank and prize_won (rounded to 2 decimals). Order by contest_id, then contest_rank, then entry_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: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
pointsdecimal

Primary key: entry_id.

The final fantasy points of each team in a contest (steps of 0.5).

Table: PrizeSlab

ColumnType
contest_idint
rank_fromint
rank_toint
prize_per_rankint

Primary key (together): contest_id, rank_from.

Positions rank_from to rank_to (inclusive) each pay prize_per_rank rupees. A contest's slabs never overlap.

Example

The rows must come back in the order shown.

ContestEntry

entry_idcontest_iduser_idpoints
190071512.5
290072480
390073480
490074455
590075455
690076455
790077301
890171390
990178390
1090179200

PrizeSlab

contest_idrank_fromrank_toprize_per_rank
9001110000
900234000
900451000
90111500

Output

contest_identry_idcontest_rankprize_won
9001110000
900224000
900324000
90044666.67
90054666.67
90064666.67
90181250
90191250
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: Contest Prizes When Tied Teams Share a Rank

When a contest ends, Dream11 ranks its teams by points and pays out a prize table.

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #641

Problem statement

When a contest ends, Dream11 ranks its teams by points and pays out a prize table. Teams with equal points share a rank, and the prizes of all the positions they occupy are pooled and split equally among them. For example, if two teams tie for rank 2, they occupy positions 2 and 3 and each receives half of (prize for 2 + prize for 3); the next team is rank 4.

PrizeSlab gives the prize per position for ranges of positions; positions not covered by any slab pay nothing. For every entry that wins more than 0, return contest_id, entry_id, contest_rank and prize_won (rounded to 2 decimals). Order by contest_id, then contest_rank, then entry_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: ContestEntry

ColumnType
entry_idint
contest_idint
user_idint
pointsdecimal

Primary key: entry_id.

The final fantasy points of each team in a contest (steps of 0.5).

Table: PrizeSlab

ColumnType
contest_idint
rank_fromint
rank_toint
prize_per_rankint

Primary key: contest_id, rank_from.

Positions rank_from to rank_to (inclusive) each pay prize_per_rank rupees. A contest's slabs never overlap.

Examples

Example 1

ContestEntry

entry_idcontest_iduser_idpoints
190071512.5
290072480
390073480
490074455
590075455
690076455
790077301
890171390
990178390
1090179200

PrizeSlab

contest_idrank_fromrank_toprize_per_rank
9001110000
900234000
900451000
90111500

Output

contest_identry_idcontest_rankprize_won
9001110000
900224000
900324000
90044666.67
90054666.67
90064666.67
90181250
90191250

How to solve Dream11: Contest Prizes When Tied Teams Share a Rank

The payout rule has two ingredients: where each team sits, and which positions its tie occupies. RANK() OVER (PARTITION BY contest_id ORDER BY points DESC) gives the shared rank — two teams level on 480 points after the leader are both rank 2 and the next team is rank 4, which is precisely the position numbering the prize table uses. COUNT(*) OVER (PARTITION BY contest_id, points) gives the size k of the tie, so the group occupies positions rank to rank + k − 1.

The prize table is in ranges, so for each slab we need how many of its positions fall inside the tie's range. Two closed intervals overlap when each starts before the other ends — that is the join condition — and the length of the overlap is LEAST(rank_to, last) − GREATEST(rank_from, rank) + 1. Multiplying by the per-position prize and summing over the overlapping slabs gives the pooled money; dividing by k gives each team's share. Teams whose positions meet no slab have no joined rows and are omitted, which is the "more than 0" rule.

The alternative expands the slabs into one row per position with a recursive CTE, computes ranks and tie sizes with correlated counts, and simply sums the positions in range. Ties are capped at a handful of teams, so a share is never a rounding half. The window version sorts each contest once; the correlated one is quadratic.

Reference solution (MySQL)

WITH ranked AS (
  SELECT entry_id, contest_id,
         RANK() OVER (PARTITION BY contest_id ORDER BY points DESC) AS contest_rank,
         COUNT(*) OVER (PARTITION BY contest_id, points) AS tied
  FROM ContestEntry
)
SELECT r.contest_id, r.entry_id, r.contest_rank,
       ROUND(SUM(s.prize_per_rank * (LEAST(s.rank_to, r.contest_rank + r.tied - 1) - GREATEST(s.rank_from, r.contest_rank) + 1)) / r.tied, 2) AS prize_won
FROM ranked r
JOIN PrizeSlab s
  ON s.contest_id = r.contest_id
 AND s.rank_from <= r.contest_rank + r.tied - 1
 AND s.rank_to >= r.contest_rank
GROUP BY r.contest_id, r.entry_id, r.contest_rank, r.tied
ORDER BY r.contest_id, r.contest_rank, r.entry_id

Another way

WITH RECURSIVE pos AS (
  SELECT contest_id, rank_from AS n, rank_to, prize_per_rank FROM PrizeSlab
  UNION ALL
  SELECT contest_id, n + 1, rank_to, prize_per_rank FROM pos WHERE n < rank_to
),
ranked AS (
  SELECT e.entry_id, e.contest_id,
         1 + (SELECT COUNT(*) FROM ContestEntry b WHERE b.contest_id = e.contest_id AND b.points > e.points) AS contest_rank,
         (SELECT COUNT(*) FROM ContestEntry t WHERE t.contest_id = e.contest_id AND t.points = e.points) AS tied
  FROM ContestEntry e
)
SELECT r.contest_id, r.entry_id, r.contest_rank, ROUND(SUM(p.prize_per_rank) / r.tied, 2) AS prize_won
FROM ranked r JOIN pos p ON p.contest_id = r.contest_id AND p.n BETWEEN r.contest_rank AND r.contest_rank + r.tied - 1
GROUP BY r.contest_id, r.entry_id, r.contest_rank, r.tied
ORDER BY r.contest_id, r.contest_rank, r.entry_id

← Dream11: Multiple Accounts Behind One Gmail Address · Dream11: Longest Run of Matches With the Same Captain →