Back to questions
Spotify Streaming History Spotify SQL Interview Question
Spotify Streaming History
Spotify SQL Interview Question
You're given two tables containing data on Spotify users' streaming activity: which has historical streaming data, and which has data from the current week.
Write a query that outputs the user ID, song ID, and cumulative count of song plays up to August 4th, 2022, sorted in descending order.
Assume that there may be new users or songs in the table that are not present in the table.
Definitions:
- table only contains data for the week of August 1st to August 7th, 2022.
- table contains data up to July 31st, 2022. The query should include historical data from this table.
Table:
| Column Name | Type |
|---|---|
| history_id | integer |
| user_id | integer |
| song_id | integer |
| song_plays | integer |
Example Input:
| history_id | user_id | song_id | song_plays |
|---|---|---|---|
| 10011 | 777 | 1238 | 11 |
| 12452 | 695 | 4520 | 1 |
field contains the historical data of the number of times a user has played a particular song.
Table:
| Column Name | Type |
|---|---|
| user_id | integer |
| song_id | integer |
| listen_time | datetime |
Example Input:
| user_id | song_id | listen_time |
|---|---|---|
| 777 | 1238 | 08/01/2022 12:00:00 |
| 695 | 4520 | 08/04/2022 08:00:00 |
| 125 | 9630 | 08/04/2022 16:00:00 |
| 695 | 9852 | 08/07/2022 12:00:00 |
Example Output:
| user_id | song_id | song_plays |
|---|---|---|
| 777 | 1238 | 12 |
| 695 | 4520 | 2 |
| 125 | 9630 | 1 |
On 4 August 2022, the data shows that User 777 listened to the song with song ID 1238 for a total of 12 times, with 11 of those times occurring before the current week and 1 time occurring within the current week.
However, the streaming data for User 695 with the song ID 9852 are not included in the output because the streaming date for that record falls outside the date range specified in the question.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Goal: To output the user ID, song ID and cumulative count of the song plays as of August 4th, 2022.
Step 1: Extract the count of song plays from the weekly table
The weekly table contains streaming data from August 1st to August 7th, 2022. To get the data up to August 4th, 2022, we filter to the specified date.
Note: In the following step, the SELECT fields should be arranged in the order , , and .
Step 2: Combine the weekly and historical streaming data
We combine the output with the table using a clause.
For the to work, the number and order of the fields in the SELECT statement for both queries should be the same, and the data types should be compatible.
Step 3: Find the cumulative count of song plays
Now that we have a table containing the historical streaming data up to August 4th, 2022, we need to find the cumulative count of song plays and order them in descending order.
Solution #2: Using Subquery