Music Store Data Analysis using SQL

Have you ever wondered how a music store keeps track of all its songs and artists? By exploring the store’s dataset, we can uncover useful information, such as the total number of tracks, artists, and genres. In this article, we’ll analyze a dataset from the Spotify music platform, available from Kaggle, and perform data analysis to uncover insights.

We’ll also identify the most popular albums and explore the relationship between danceability and energy in the music collection then create a Power BI dashboard for quick decision-making.

Music Store Data

We have taken Spotify from Kaggle. Let's take a quick understanding of the dataset as defined below:

• track_id: A unique identifier assigned to each track in the dataset, ensuring that each track can be individually referenced.
• artists: The artist(s) associated with the track, which may contain one or multiple artists, depending on collaborations. This field helps identify the creators of the music.
• album_name: The name of the album to which the track belongs. Albums group multiple tracks that share a common theme or release date.
• track_name: The name of the track itself, identifying the specific song within the dataset.
• popularity: A score (from 0 to 100) representing how popular the track is, with higher scores indicating more popularity, influenced by factors like plays, user interactions, and shares.
• duration_ms: The length of the track in milliseconds, indicating how long the track plays from start to finish.
• explicit: A boolean value (True/False) indicating whether the track contains explicit content such as profane language or adult themes, allowing users to filter out explicit content if desired.
• danceability: A value between 0 and 1 indicates how suitable the track is for dancing. Higher values suggest the track has a rhythm and tempo that makes it easier to dance to.
• energy: A measure (between 0 and 1) representing the energy level of the track. Higher values indicate energetic tracks with fast tempos, driving beats, and strong rhythms.
• key: The musical key in which the track is composed, providing a sense of the tonal foundation of the track (e.g., C major, A minor).
• loudness: The track’s loudness in decibels (dB), quantifying how loud the track sounds relative to others.
• mode: The mode of the track, which is either "Major" or "Minor" determining whether the track has a brighter (Major) or more somber (Minor) tone.
• speechiness: A value between 0 and 1 representing the amount of spoken word in the track. A higher value suggests more speech-like qualities, such as rap or spoken word elements.
• acousticness: A value between 0 and 1 that indicates how acoustic or "natural" the track sounds. Higher values mean the track has more acoustic qualities, such as the use of real instruments and fewer electronic elements.
• instrumentalness: A value between 0 and 1 indicating whether the track is instrumental. A value closer to 1 suggests the track has little to no vocals and is mostly instrumental.
• liveness: A value between 0 and 1 that indicates the presence of audience noise in the track, with higher values suggesting live performances or recordings with audience sounds.
• valence: A value between 0 and 1 that measures the positivity of the track. Higher values indicate a happier or more positive mood, while lower values are associated with more somber or melancholic tracks.
• tempo: The tempo (beats per minute) of the track, providing an indication of how fast or slow the track is, helping to categorize it into genres or settings (e.g., fast-paced for workout playlists).
• time_signature: The time signature of the track, specifying the rhythmic structure, such as 4/4 (common time) or 3/4 (waltz), which defines how the beats are grouped in the music.
• track_genre: The genre of the track (e.g., Pop, Rock, Jazz), which helps categorize the music and is useful for genre-based analysis or filtering.

Data Cleaning Using SQL

Before diving into the analysis, the data needs to be cleaned to ensure accuracy. SQL is a powerful tool for this, and here are the key steps we need to remember for cleaning. Although this dataset is already cleaned.

• Remove Null Values: Ensuring there are no missing values in critical columns like track_id, popularity, and track_genre.
• Standardize Data: Standardizing text data in columns like explicit, artists, and track_genre to avoid any discrepancies.
• Handle Duplicates: Remove any duplicate tracks or entries to ensure data integrity.

Exploratory Data Analysis (EDA) Using SQL

Let's perform some SQL queries to get quick insight and take decision on behalf of the result as defined below:

1. Total Number of Tracks

SELECT COUNT(track_id) AS Total_Tracks
FROM spotify_data;A

Output:

Explanation: This query counts the total number of unique track IDs in the dataset, providing an overview of the dataset's size.

2. Total Number of Artists:

SELECT COUNT(DISTINCT artists) AS Total_Artists
FROM spotify_data;

Output:

Explanation: This query counts the number of distinct artists in the dataset, helping to understand the diversity of music contributors.

3. Total Number of Genres:

SELECT COUNT(DISTINCT track_genre) AS Total_Genres
FROM spotify_data;

Output:

Explanation: This query counts how many different music genres are present in the dataset, offering insights into the genre diversity of the catalog.

4. Top 10 Albums by Popularity:

SELECT album_name, AVG(popularity) AS avg_popularity
FROM spotify_data
GROUP BY album_name
ORDER BY avg_popularity DESC
LIMIT 10;

Output:

Explanation: This query calculates the average popularity for each album, then orders them in descending order to find the top 10 most popular albums.

5. Energy vs. Danceability:

SELECT danceability, energy
FROM spotify_data;

Output:

Explanation: This query retrieves the danceability and energy values for all tracks in the dataset, allowing us to analyze how these two attributes correlate in different songs.

Advanced-Data Analysis with Dashboard Creation

A well-designed dashboard enables efficient data analysis and decision-making. This dashboard includes Key Performance Indicators (KPIs), stacked column charts, a donut chart, and slicers, providing valuable insights into business or healthcare operations.

1. Key Performance Indicators (KPIs)

KPIs help measure overall performance and efficiency.

• Total Sales Amount: Represents the total revenue generated by summing all billing amounts. Monitoring revenue helps track financial growth and make informed strategic decisions.
• Total Patients: Displays the total number of unique patients in the dataset. Analyzing patient volume helps in resource allocation, staffing, and expansion planning.

2. Billing Amount by Facility (Stacked Column Chart)

This visualization shows the total billing amount for different facilities.

• X-axis: Represents different facilities.
• Y-axis: Displays total revenue from each facility.
• Insights: Identifies high-revenue facilities, compares financial performance and helps in strategic resource allocation.

3. Patients by Diagnosis (Stacked Column Chart)

This chart represents the number of patients diagnosed with various medical conditions.

• X-axis: Lists different diagnoses.
• Y-axis: Shows the number of patients diagnosed with each condition.
• Insights: Identifies common medical conditions, helps allocate resources for specialized treatments, and assists in disease trend analysis.

For example, if Hypertension and Diabetes have the highest patient count, healthcare providers can implement preventive programs to manage these conditions effectively.

4. Patients by Facility (Stacked Column Chart)

This chart illustrates the distribution of patients across different facilities.

• X-axis: Represents different facilities.
• Y-axis: Displays the number of patients treated.
• Insights: Helps analyze hospital capacity, identify high-demand facilities, and optimize resource distribution.

If one facility consistently has a higher patient count, it may need additional staff, beds, and medical equipment to improve efficiency.

5. Gender Distribution (Donut Chart)

• A donut chart is used to visualize gender distribution in the dataset.
• The chart is divided into segments for Male, Female, and Other.
• Insights: Helps in understanding gender-based trends, identifying conditions more common in specific genders, and evaluating gender inclusivity in healthcare services.

For example, if more females are diagnosed with certain illnesses, hospitals can focus on targeted awareness programs to improve patient outcomes.

6. Slicers (Filters for Dynamic Analysis)

Slicers enable interactive data filtering, providing more specific insights.

• Facility Name Slicer: Filters data by a specific facility for a detailed performance review.
• Admission Date Slicer: Filters data by time range to track seasonal trends and disease patterns.

Conclusion

By examining the dataset, we get a better understanding of the music store’s collection—from the total number of tracks to the energy levels of different songs. These insights can help us spot trends and make smarter recommendations for listeners based on their preferences.