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. 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. Output the track name, artist, and the corresponding streams. Sort records based on streams in descending order.
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 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 * FROM spotify_worldwide_daily_song_ranking
WHERE position BETWEEN 8 AND 10;
SELECT SUM(streams) AS top100_total_streams
FROM (
SELECT streams
FROM spotify_worldwide_daily_song_ranking
ORDER BY position ASC
LIMIT 100) AS t;
SELECT AVG(stream)
FROM spotify_worldwide_daily_song_ranking;
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;
SELECT trackname, streams
FROM spotify_worldwide_daily_song_ranking
WHERE streams < 2000
ORDER BY streams DESC;
SELECT artist, COUNT(id) num_appearances
FROM spotify_worldwide_daily_song_ranking
GROUP BY artist
ORDER BY COUNT(id) DESC;
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;
SELECT
trackname,
COUNT(position) AS num_top
FROM spotify_worldwide_daily_song_ranking
WHERE position = 1
GROUP BY 1
ORDER BY COUNT(*) DESC
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;
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
