Sections

Spotify Exercise Solutions

The table used on this exercise provides information about the ranking of popular songs worldwide. The following questions are provided with the correct SQL query and graphs using the graphics function in Strata Scratch.

Use the datasets.spotify_worldwide_daily_song_ranking table to answer the questions below.

Question 1

Which songs have more than 3 million streams?

Solution:

Our query starts by selecting the columns trackname, artist and streams since they are useful parameters to answer the question. We add a conditional statement where streams > 3000000 and sort the data in ascending order to determine the top results.

Question 2

Which songs had been frequently placed in the top 1 position over the years?

Solution:

For each track (trackname) we find how many times it was in a top 1 position by counting the number of rows which pass the filter position = 1. The tracks are ordered in a descending order by that count (times_top1).

Question 3

Which artists have been on Spotify the most?

Solution:

We want to know which artists have been on Spotify the most, so the best approach is to query the artist name and count the associated rows. We group the results and sort them in descending order to determine the top artists on Spotify.

Question 4

Which artists had the most top 10 songs over the years?

Solution:

We can determine which artists have their songs frequently included on the top 10 by querying the artist name and adding a conditional statement where position is less than or equal to 10. We group the data having the same artist name and sort the results in descending order to view the top artists.

Question 5

Which songs have less than 2000 streams?

Solution:

Here, we first select the trackname and Streams from the table. We can display the results having less than 2000 streams by adding this requirement to the conditional statement WHERE. The top results in ascending order are then determined using the ORDER BY clause.

Question 6

What are the top 10 songs listened to?

Solution:

We can easily answer this question by querying the trackname from the table. We add a conditional statement where position < 10 to get the top 10 results only. Grouping and sorting the data will help us determine the ranking of the songs mostly listened to.