Spotify Streaming History
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.
: Refers to the historical count of streaming or song plays by the user.
As of 4 August 2022,
The dataset you are querying against may have different input & output - this is just an example!
Goal: Output the user id, song id and cumulative count of the song plays as of 4 August 2022.
We’re using song plays and streaming interchangeably, but it should mean the same.
Step 1: Find the count of song plays by the user and song
According to the assumptions, the weekly table holds streaming data from 1 August 2022 to 7 August 2022. Since the question asks for data up to 4 August 2022 (inclusive), we have to filter to the specified date.
You also want to be mindful of how you arrange the fields in the . You'll know why in the following step! ;)
Step 2: Combine the output with the historical streaming data
The trick for a to work is:
Step 3: Obtain the user id, song id and cumulative count of the song plays
Now that we have a table containing the historical streaming data up to 4 August 2022, let's work on the final step: Find the cumulative count of song plays and order them in descending order.
Solution #1: Using CTE
Solution #2: Using Subquery