Back to back repeats

window functions

Back to back repeats

Spotify SQL Interview Question

A track played twice in a row is the strongest signal a listener can give Spotify without pressing a button, and it feeds the recommender more than a like does. The same track played twice in a day with other music in between is far weaker, so the two have to be told apart.

For each listener who did it at least once, return the listener_id and the number of plays that repeated the track played immediately before as repeat_plays.

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_idrepeat_plays
L041

Explanation

L04 played T100 and then played it again straight away, which is one immediate repeat. L01 played T100 three times during the day but always with another track in between, so none of those count and L01 is not in the output.

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