Worldwide Daily Song Ranking Table
Queries are in PostgreSQL
Table: spotify_worldwide_daily_song_ranking
Schema:
id | int |
position | int |
trackname | varchar |
artist | varchar |
streams | int |
url | varchar |
date | datetime |
region | varchar |
Preview:
id | position | trackname | artist | streams | url | date | region |
---|---|---|---|---|---|---|---|
303651 | 52 | Heart Won't Forget | Matoma | 28047 | https://open.spotify.com/track/2of2DM5LqTh7ohmmVXUKsH | 2017-02-04 | no |
85559 | 160 | Someone In The Crowd - From "La La Land" Soundtrack | Emma Stone | 17134 | https://open.spotify.com/track/7xE4vKvjqUTtHyJ9zi0k1q | 2017-02-26 | fr |
1046089 | 175 | The Greatest | Sia | 10060 | https://open.spotify.com/track/7xHWNBFm6ObGEQPaUxHuKO | 2017-03-06 | cl |
350824 | 25 | Unforgettable | French Montana | 46603 | https://open.spotify.com/track/3B54sVLJ402zGa6Xm4YGNe | 2017-10-01 | no |
776822 | 1 | Bad and Boujee (feat. Lil Uzi Vert) | Migos | 1823391 | https://open.spotify.com/track/4Km5HrUvYTaSUfiSGPJeQR | 2017-01-27 | us |
EASY
Find songs that are ranked between 8-10
Find songs that are ranked between 8-10. Output the track name along with the corresponding position, ordered ascendingly.
Top 100 Ranked Songs
Find the total number of streams for the top 100 ranked songs.
Find the average number of streams across all songs
Find the average number of streams across all songs.
Top 10 Ranked Songs
Find the top 10 ranked songs by position. Output the track name along with the corresponding position and sort records by the position in descending order and track name alphabetically, as there are many tracks that are tied for the same position.
Find songs with less than 2000 streams
Find songs with less than 2000 streams. Output the track name along with the corresponding streams. Sort records by streams in descending order. There is no need to group rows with same track name
Find how many times each artist appeared on the Spotify ranking list
Find how many times each artist appeared on the Spotify ranking list Output the artist name along with the corresponding number of occurrences. Order records by the number of occurrences in descending order.
Find songs that have more than 3 million streams
Find songs that have more than 3 million streams. Output the track name, artist, and the corresponding streams. Sort records based on streams in descending order.
MEDIUM
Top Ranked Songs
Find songs that have ranked in the top position. Output the track name and the number of times it ranked at the top. Sort your records by the number of times the song was in the top position in descending order.
Find artists with the highest number of top 10 ranked songs over the years
Find artists with the highest number of top 10 ranked songs over the years. Output the artist along with the corresponding number of top 10 rankings.
HARD
Days At Number One
Find the number of days a US track has stayed in the 1st position for both the US and worldwide rankings. Output the track name and the number of days in the 1st position. Order your output alphabetically by track name.
If the region 'US' appears in dataset, it should be included in the worldwide ranking.
Last updated