Everyone in the top three

ranking

Everyone in the top three

DraftKings SQL Interview Question

DraftKings pays the top three places in a contest, and any lineup tied into those places gets paid too. Cutting the list off at exactly three rows would drop a player who scored the same as somebody who was paid, which is the fastest way to lose a user for good.

Return the contest_id, the user_id, and the points of every entry finishing in the top three places, counting ties as sharing a place.

Asked of

  • Data Analyst
  • Business Analyst
  • BI Analyst
  • Product Analyst
  • Analytics Engineer
  • Data Engineer
  • Data Scientist

draftkings_contest_entriesTable28 rows

Column NameType
entry_idBIGINT
contest_idVARCHAR
user_idVARCHAR
joined_atTIMESTAMP
pointsDOUBLE

draftkings_contest_entriesExample Input

entry_idcontest_iduser_idjoined_atpoints
3101C1U1002024-03-20 18:05:00742.5
3102C1U1012024-03-20 18:11:00801
3103C1U1022024-03-20 18:14:00801
3104C1U1032024-03-20 18:22:00689.5
3105C1U1042024-03-20 18:30:00742.5
3106C1U1052024-03-20 18:41:00655
3107C1U1062024-03-20 18:52:00812.5
3120C4U1002024-03-23 17:30:00455.5
3121C4U1072024-03-23 17:38:00455.5
3122C4U1082024-03-23 17:49:00603
3123C4U1102024-03-23 17:55:00588.5
3124C4U1112024-03-23 18:04:00721.5

Example Output

contest_iduser_idpoints
C1U101801
C1U102801
C1U106812.5
C4U108603
C4U110588.5
C4U111721.5

Explanation

C1 has one lineup in first and two tied in second, and those three fill all three paid places. The two on 742.5 are fourth, not third, because the tie above them used up a place, so they are not paid.

The example above is a small slice of the data. Your query runs against the full tables.