Prize ranks with ties
mediumPrize ranks with ties
DraftKings SQL Interview Question
DraftKings pays prize money by finishing position, and two lineups on identical points have to finish level. Standard contest scoring applies: tied lineups share the better position, and the next position down is skipped by as many lineups as tied.
Return the contest_id, the user_id, their points, and their finishing position as prize_rank. Sort by contest_id and then by prize_rank.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
draftkings_contest_entriesTable28 rows
| Column Name | Type |
|---|---|
| entry_id | BIGINT |
| contest_id | VARCHAR |
| user_id | VARCHAR |
| joined_at | TIMESTAMP |
| points | DOUBLE |
draftkings_contest_entriesExample Input
| entry_id | contest_id | user_id | joined_at | points |
|---|---|---|---|---|
| 3101 | C1 | U100 | 2024-03-20 18:05:00 | 742.5 |
| 3102 | C1 | U101 | 2024-03-20 18:11:00 | 801 |
| 3103 | C1 | U102 | 2024-03-20 18:14:00 | 801 |
| 3104 | C1 | U103 | 2024-03-20 18:22:00 | 689.5 |
| 3105 | C1 | U104 | 2024-03-20 18:30:00 | 742.5 |
| 3106 | C1 | U105 | 2024-03-20 18:41:00 | 655 |
| 3107 | C1 | U106 | 2024-03-20 18:52:00 | 812.5 |
| 3120 | C4 | U100 | 2024-03-23 17:30:00 | 455.5 |
| 3121 | C4 | U107 | 2024-03-23 17:38:00 | 455.5 |
| 3122 | C4 | U108 | 2024-03-23 17:49:00 | 603 |
| 3123 | C4 | U110 | 2024-03-23 17:55:00 | 588.5 |
| 3124 | C4 | U111 | 2024-03-23 18:04:00 | 721.5 |
Example Output
| contest_id | user_id | points | prize_rank |
|---|---|---|---|
| C1 | U106 | 812.5 | 1 |
| C1 | U101 | 801 | 2 |
| C1 | U102 | 801 | 2 |
| C1 | U100 | 742.5 | 4 |
| C1 | U104 | 742.5 | 4 |
| C1 | U103 | 689.5 | 6 |
| C1 | U105 | 655 | 7 |
| C4 | U111 | 721.5 | 1 |
| C4 | U108 | 603 | 2 |
| C4 | U110 | 588.5 | 3 |
| C4 | U100 | 455.5 | 4 |
| C4 | U107 | 455.5 | 4 |
Explanation
In C1 two lineups tie on 801 points and both finish second. The next one down finishes fourth rather than third, because two places have already been used. It happens again at 742.5, where two lineups share fourth and the next is sixth.
The example above is a small slice of the data. Your query runs against the full tables.
Company
DraftKings
Difficulty
medium
Topic
ranking
Language
SQL
Your query
Starting the SQL engine...