Points above the contest average

window functions

Points above the contest average

DraftKings SQL Interview Question

A DraftKings score means nothing on its own, because a low scoring slate drags every lineup down. What tells a player whether they drafted well is the distance from the average of the contest they were actually in, and that is what the results screen shows them.

Return the contest_id, the user_id, their points, and how far above or below their contest's average they finished as points_vs_average, rounded to 2 decimal places.

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_idpointspoints_vs_average
C1U100742.5-6.64
C1U10180151.86
C1U10280151.86
C1U103689.5-59.64
C1U104742.5-6.64
C1U105655-94.14
C1U106812.563.36
C4U100455.5-109.3
C4U107455.5-109.3
C4U10860338.2
C4U110588.523.7
C4U111721.5156.7

Explanation

C1 averaged 749.14 points, so U106 on 812.5 finished 63.36 above it and U105 on 655 finished 94.14 below. C4 is a lower scoring contest, so 603 there is 38.2 above its own average rather than below.

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