Spotify Analytics

-- Spotify SQL Analytics Project by Dhruv

-- For this project, I downloaded a Spotify dataset from Kaggle.
-- I created a table to store the data and performed SQL-based analysis to uncover insights about songs and artists.

-- Step 1: Create a table to hold Spotify data
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
);

-- Step 2: Import the Spotify CSV data into the table (using SQL client or GUI).

-- Step 3: Perform data analysis on the Spotify dataset

-- 1. Calculate the average popularity, danceability, and energy for each artist and track
SELECT
    artist_name,
    track_name,
    AVG(popularity) AS avg_popularity,
    AVG(danceability) AS avg_danceability,
    AVG(energy) AS avg_energy
FROM BIT_DB.Spotifydata
GROUP BY artist_name, track_name;

-- 2. Find the top 10 most popular tracks
SELECT
    track_name,
    artist_name,
    popularity
FROM BIT_DB.Spotifydata
ORDER BY popularity DESC
LIMIT 10;

-- Summary:
-- This project, authored by Dhruv, uses SQL to analyze Spotify song data and reveal patterns in artist popularity and song characteristics.