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.

StrataScratch - Find songs that are ranked between 8-10platform.stratascratch.com

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.

StrataScratch - Top 100 Ranked Songsplatform.stratascratch.com

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.

StrataScratch - Find the average number of streams across all songsplatform.stratascratch.com

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.

StrataScratch - Top 10 Ranked Songsplatform.stratascratch.com

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

StrataScratch - Find songs with less than 2000 streamsplatform.stratascratch.com

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.

StrataScratch - Artist Appearance Countplatform.stratascratch.com

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.

StrataScratch - Find songs that have more than 3 million streamsplatform.stratascratch.com

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.

StrataScratch - Top Ranked Songsplatform.stratascratch.com

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.

StrataScratch - Find artists with the highest number of top 10 ranked songs over the yearsplatform.stratascratch.com

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.

StrataScratch - Days At Number Oneplatform.stratascratch.com

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