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:
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.
SELECT * FROM spotify_worldwide_daily_song_ranking
WHERE position BETWEEN 8 AND 10;
Top 100 Ranked Songs
Find the total number of streams for the top 100 ranked songs.
SELECT SUM(streams) AS top100_total_streams
FROM (
SELECT streams
FROM spotify_worldwide_daily_song_ranking
ORDER BY position ASC
LIMIT 100) AS t;
Find the average number of streams across all songs
Find the average number of streams across all songs.
SELECT AVG(stream)
FROM spotify_worldwide_daily_song_ranking;
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.
SELECT trackname, position
FROM
(SELECT trackname, position
FROM spotify_worldwide_daily_song_ranking
ORDER BY position
LIMIT 10) AS t
ORDER BY position DESC, trackname;
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
SELECT trackname, streams
FROM spotify_worldwide_daily_song_ranking
WHERE streams < 2000
ORDER BY streams DESC;
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.
SELECT artist, COUNT(id) num_appearances
FROM spotify_worldwide_daily_song_ranking
GROUP BY artist
ORDER BY COUNT(id) DESC;
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.
SELECT trackname, artist, SUM(streams) AS total_streams
FROM spotify_worldwide_daily_song_ranking
GROUP BY 1, 2
HAVING SUM(streams) > 3000000
ORDER BY SUM(streams) DESC;
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.
SELECT
trackname,
COUNT(position) AS num_top
FROM spotify_worldwide_daily_song_ranking
WHERE position = 1
GROUP BY 1
ORDER BY COUNT(*) DESC
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.
SELECT artist, COUNT(position) as num_in_top10
FROM spotify_worldwide_daily_song_ranking
WHERE position <= 10
GROUP BY artist
ORDER BY COUNT(position) DESC;
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.
SELECT us.trackname, COUNT(ww.id)
FROM spotify_daily_rankings_2017_us us
JOIN spotify_worldwide_daily_song_ranking ww
ON us.trackname = ww.trackname AND us.artist = ww.artist
WHERE us.position = 1 AND ww.position = 1
GROUP BY us.trackname
ORDER BY us.trackname
Last updated
Was this helpful?