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.

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