Spotify Analytics

# For this project, I downloaded Spotify data from Kaggle.
# Then I created a table to insert Spotify data into.
# Finally, I performed analytics on the data using SQL. 

#Creating the table: 
CREATE TABLE BIT_DB.Spotifydata (
id integer PRIMARY KEY,
artist_name varchar NOT NULL,
track_name varchar NOT NULL,
track_id varchar NOT NULL,
popularity integer NOT NULL,
danceability decimal(4,3) NOT NULL,
energy decimal(4,3) NOT NULL,
key integer NOT NULL,
loudness decimal(5,3) NOT NULL,
mode integer NOT NULL,
speechiness decimal(5,4) NOT NULL,
acousticness decimal(6,5) NOT NULL,
instrumentalness text NOT NULL,
liveness decimal(5,4) NOT NULL,
valence decimal(4,3) NOT NULL,
tempo decimal(6,3) NOT NULL,
duration_ms integer NOT NULL,
time_signature integer NOT NULL 
)

#Then I inserted the Spotify Data .csv into the table.

#Next, I explored the data using the following SQL. 

-- How many tracks does each artist have?
SELECT distinct artist_name, COUNT(track_name)
FROM BIT_DB.Spotifydata
GROUP BY artist_name
ORDER BY COUNT(track_name);

-- What are the top 5 energetic tracks and who is the artist for each track?
SELECT track_name, artist_name
FROM BIT_DB.Spotifydata
ORDER BY energy desc
LIMIT 5;

-- What is the least popular song, who is the artist, and what is its danceability rating?
SELECT track_name, artist_name, danceability
FROM BIT_DB.Spotifydata
ORDER BY popularity
LIMIT 1;

-- What is the average danceability for the top 10 popular songs?
SELECT AVG(danceability)
FROM BIT_DB.Spotifydata
WHERE track_name IN 
    (SELECT track_name
     FROM BIT_DB.Spotifydata
     ORDER BY popularity desc
     LIMIT 10)
;

-- Which artist recorded the longest song and which song is it?
SELECT artist_name, track_name, duration_ms
FROM BIT_DB.Spotifydata
ORDER BY duration_ms desc
LIMIT 1;