logo

Back to questions

Spotify Streaming History

Medium

You're given two tables on Spotify users' streaming data. table contains the historical streaming data and table contains the current week's streaming data.

Write a query to output the user id, song id, and cumulative count of song plays as of 4 August 2022 sorted in descending order.

Definitions:

  • table currently holds data from 1 August 2022 to 7 August 2022.
  • table currently holds data up to to 31 July 2022. The output should include the historical data in this table.

Assumption:

  • There may be a new user or song in the table not present in the table.

Table:

Column NameType
history_idinteger
user_idinteger
song_idinteger
song_playsinteger

Example Input:

history_iduser_idsong_idsong_plays
10011777123811
1245269545201

: Refers to the historical count of streaming or song plays by the user.

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

As of 4 August 2022,

  • User 777 has listened to the 1238 for 12 times which is 11 times historically and 1 time within the week.
  • User 695's streaming of the 9852 is excluded from the output because the streaming date on 8 August 2022 is out of the question requirement.

The dataset you are querying against may have different input & output - this is just an example!

PostgreSQL 14