logo

Back to questions

Spotify Listening History

Medium

You are given a table that keeps track of users' listening history on Spotify. The table tracks how many times users listened to each song for all days between August 1 and August 7, 2022.

Write a query to show the user ID, song ID, and the number of times the user has played each song as of August 4, 2022. We'll refer to the number of song plays as . The rows with the most song plays should be at the top of the output.

Assumption:

  • The table holds data that ends on July 31, 2022. Output should include the historical data in this table.
  • There may be a new user in the weekly table who is not present in the history table.
  • A user may listen to a song for the first time, in which case no existing (user_id, song_id) user-song pair exists in the history table.
  • A user may listen to a specific song multiple times in the same day.

Table:

Column NameType
history_idinteger
user_idinteger
song_idinteger
song_playsinteger

Example Input:

history_iduser_idsong_idsong_plays
10011777123811
1245269545201

Table:

Column NameType
user_idinteger
song_idinteger
listen_timedatetime

Example Input:

user_idsong_idlisten_time
777123808/01/2022 12:00:00
695452008/04/2022 08:00:00
125963008/04/2022 16:00:00
695985208/07/2022 12:00:00

Example Output:

user_idsong_idsong_plays
777123812
69545202
12596301

Up to August 4, 2022, 777 has listened to the 1238 12 times. 695 with 9852 is excluded from the output because their listening time on August 8, 2022 is not within the the question's requirement (which is up to August 4, 2022 only).

PostgreSQL 14

Refer friends to get bonus content & cool prizes.