Listening time so far

running totals

Listening time so far

Spotify SQL Interview Question

Spotify counts a listener as engaged once they pass a threshold of minutes in a day, and the product team needs the moment each listener crosses it rather than the daily total. That means the minutes have to add up play by play through the day.

Return the listener_id, the played_at time of each play, the minutes that play lasted as minutes, and the listener's minutes so far including that play as running_minutes. Round both to 2 decimal places. Sort by listener_id and then by played_at.

Asked of

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

spotify_playsTable30 rows

Column NameType
play_idBIGINT
listener_idVARCHAR
track_idVARCHAR
played_atTIMESTAMP
ms_playedBIGINT

spotify_playsExample Input

play_idlistener_idtrack_idplayed_atms_played
5001L01T1002024-03-04 08:02:00210000
5002L01T1042024-03-04 08:06:00185000
5003L01T1002024-03-04 08:10:00198000
5004L01T1122024-03-04 09:15:00240000
5005L01T1042024-03-04 09:21:00172000
5006L01T1002024-03-04 18:40:00205000
5007L01T1182024-03-04 18:44:00160000
5008L01T1122024-03-04 18:49:00233000
5026L04T1002024-03-04 11:00:00204000
5027L04T1002024-03-04 11:05:00201000
5028L04T1042024-03-04 15:45:00178000
5029L04T1182024-03-04 15:52:00162000
5030L04T1212024-03-04 15:58:00249000

Example Output

listener_idplayed_atminutesrunning_minutes
L012024-03-04 08:02:003.53.5
L012024-03-04 08:06:003.086.58
L012024-03-04 08:10:003.39.88
L012024-03-04 09:15:00413.88
L012024-03-04 09:21:002.8716.75
L012024-03-04 18:40:003.4220.17
L012024-03-04 18:44:002.6722.83
L012024-03-04 18:49:003.8826.72
L042024-03-04 11:00:003.43.4
L042024-03-04 11:05:003.356.75
L042024-03-04 15:45:002.979.72
L042024-03-04 15:52:002.712.42
L042024-03-04 15:58:004.1516.57

Explanation

L01's first play lasts 3.5 minutes, so the running figure starts there. The next play of 3.08 minutes takes it to 6.58, and the one after takes it to 9.88. L04 starts again from its own first play rather than carrying on from L01.

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