🎵 Spotify Data Analysis Using SQL

---

📌 Project Overview

This project focuses on analyzing Spotify music data using SQL. It covers everything from basic data exploration to advanced SQL concepts such as CTEs, Window Functions, Aggregate Functions, Conditional Aggregation, and Subqueries.

The objective is to extract meaningful insights from Spotify datasets using SQL queries.

---

🎯 Objectives

• 🎵 Explore Spotify music dataset
• 📊 Perform Exploratory Data Analysis (EDA)
• 🔍 Answer real-world business questions
• 📈 Practice SQL from Beginner to Advanced
• 💡 Learn Window Functions, CTEs and Aggregate Functions

---

🛠️ Tech Stack

• PostgreSQL
• SQL
• Git
• GitHub

---

📊 Dataset Information

The dataset contains information about Spotify tracks including:

• 🎤 Artist
• 🎵 Track
• 💿 Album
• 📀 Album Type
• 💃 Danceability
• ⚡ Energy
• 🔊 Loudness
• 🗣 Speechiness
• 🎼 Acousticness
• 🎻 Instrumentalness
• ❤️ Valence
• 🎧 Liveness
• ⏱ Tempo
• ⌛ Duration
• 👀 Views
• 👍 Likes
• 💬 Comments
• 📈 Streams
• ▶ Platform (Spotify / YouTube)

---

🔍 Exploratory Data Analysis (EDA)

📌 Display complete dataset

SELECT *
FROM spotify;

---

📌 Count total artists

SELECT COUNT(DISTINCT artist)
FROM spotify;

---

📌 Find album types

SELECT DISTINCT album_type
FROM spotify;

---

📌 Maximum track duration

SELECT MAX(duration_min)
FROM spotify;

---

📌 Minimum track duration

SELECT MIN(duration_min)
FROM spotify;

---

📌 Find tracks having 0 duration

SELECT *
FROM spotify
WHERE duration_min = 0;

---

📌 Remove tracks having 0 duration

DELETE FROM spotify
WHERE duration_min = 0;

---

📌 List all available channels

SELECT DISTINCT channel
FROM spotify;

---

🟢 Easy Level SQL Questions

1️⃣ Retrieve the names of all tracks that have more than 1 billion streams.

SELECT track
FROM spotify
WHERE stream > 1000000000;

---

2️⃣ List all albums along with their respective artists.

SELECT DISTINCT album, artist
FROM spotify
ORDER BY album;

---

3️⃣ Get the total number of comments for tracks where licensed = TRUE.

SELECT SUM(comments)
FROM spotify
WHERE licensed = 'true';

---

4️⃣ Find all tracks that belong to the album type "single".

SELECT track
FROM spotify
WHERE album_type='single';

---

5️⃣ Count the total number of tracks by each artist.

SELECT artist,
COUNT(track)
FROM spotify
GROUP BY artist;

---

🟡 Medium Level SQL Questions

1️⃣ Calculate the average danceability of tracks in each album.

SELECT album,
AVG(danceability) AS Avg_danceability
FROM spotify
GROUP BY album;

---

2️⃣ Find the Top 5 tracks with the highest energy values.

SELECT track,
energy
FROM spotify
GROUP BY track,energy
ORDER BY energy DESC
LIMIT 5;

---

3️⃣ List all tracks along with total views and total likes where official_video = TRUE.

SELECT track,
SUM(views) AS total_views,
SUM(likes) AS total_likes
FROM spotify
WHERE official_video='true'
GROUP BY track
ORDER BY total_views DESC
LIMIT 5;

---

4️⃣ Calculate total views of all tracks in each album.

SELECT album,
track,
SUM(views) AS total_album_views
FROM spotify
GROUP BY album,track
ORDER BY total_album_views DESC;

---

5️⃣ Retrieve track names streamed more on Spotify than YouTube.

SELECT *
FROM
(
SELECT track,
COALESCE(SUM(CASE WHEN most_played_on='Youtube' THEN stream END),0) AS streamed_on_youtube,
COALESCE(SUM(CASE WHEN most_played_on='Spotify' THEN stream END),0) AS streamed_on_spotify
FROM spotify
GROUP BY 1
) AS t1
WHERE streamed_on_spotify > streamed_on_youtube
AND streamed_on_youtube <> 0;

---

🔴 Hard Level SQL Questions

1️⃣ Find the Top 3 most-viewed tracks for each artist using Window Functions.

WITH ranking_artist AS
(
SELECT artist,
track,
SUM(views) AS total_views,
DENSE_RANK() OVER
(
PARTITION BY artist
ORDER BY SUM(views) DESC
) AS rank
FROM spotify
GROUP BY artist,track
ORDER BY artist,total_views DESC
)

SELECT *
FROM ranking_artist
WHERE rank<=3;

---

2️⃣ Find tracks where the liveness score is above the average.

SELECT track,
artist,
liveness
FROM spotify
WHERE liveness >
(
SELECT AVG(liveness)
FROM spotify
);

---

3️⃣ Calculate the difference between the highest and lowest energy values for each album using CTE.

WITH cte AS
(
SELECT album,
MAX(energy) AS highest_energy,
MIN(energy) AS lowest_energy
FROM spotify
GROUP BY album
)

SELECT album,
highest_energy-lowest_energy AS energy_diff
FROM cte
ORDER BY energy_diff DESC;

---

📚 SQL Concepts Covered

• ✅ SELECT
• ✅ WHERE
• ✅ ORDER BY
• ✅ GROUP BY
• ✅ Aggregate Functions
• ✅ DISTINCT
• ✅ LIMIT
• ✅ CASE WHEN
• ✅ COALESCE
• ✅ Subqueries
• ✅ Common Table Expressions (CTE)
• ✅ Window Functions
• ✅ DENSE_RANK()
• ✅ DELETE
• ✅ Data Cleaning

---

🚀 Key Learnings

• ✔ SQL Query Writing
• ✔ Data Cleaning
• ✔ Business Problem Solving
• ✔ Data Aggregation
• ✔ Window Functions
• ✔ CTEs
• ✔ Analytical Thinking

---

⭐ If you found this project useful

Please consider giving it a ⭐ on GitHub!

---