Spotify Analysis

#For this project, I downloaded and used Spotify data 
#I then created a table
#After I created the table, I performed analysis on the Spotify data 

#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. 

--Finding the top 10 artists based on popularity 
SELECT artist_name, AVG(popularity) AS avg_popularity
FROM BIT_DB.Spotifydata
GROUP BY artist_name
ORDER BY AVG(popularity) DESC
LIMIT 10;

--Finding the top 10 songs based on popularity
SELECT artist_name, track_name, popularity
FROM BIT_DB.Spotifydata
ORDER BY popularity DESC
LIMIT 10;

--Artist with the longest song
SELECT artist_name, track_name, duration_ms
FROM BIT_DB.Spotifydata
ORDER BY duration_ms DESC
LIMIT 1;

--The average danceability of tracks with popularity above 80
SELECT AVG(danceability) AS avg_danceability
FROM BIT_DB.Spotifydata
WHERE popularity > 80;