Menu
This project involved performing an in-depth analysis of streaming data for a music distribution company to extract actionable insights on track, album, and artist performance across digital platforms. Using a large dataset containing metrics such as streams, views, likes, comments, energy, danceability, liveness, and acousticness, I designed and executed complex SQL queries incorporating aggregate functions (SUM, AVG, COUNT), conditional expressions (CASE, COALESCE), window functions (DENSE_RANK, SUM OVER, PARTITION BY), and common table expressions (CTEs) to derive meaningful performance metrics.
Key tasks included identifying tracks with the highest streams, calculating average danceability per album, ranking top tracks per artist by views, and computing energy-to-liveness ratios to assess track dynamics. I also performed cross-platform analysis to compare streaming performance between Spotify and YouTube, enabling insights into platform-specific audience engagement.
The outcome of the project provided a comprehensive view of content performance, highlighting top-performing artists, albums, and tracks, and delivering data-driven recommendations to optimize music release strategies, marketing campaigns, and audience targeting. This analysis demonstrates the ability to transform raw streaming data into actionable business intelligence using SQL and analytical reasoning.
Objective:
To perform a comprehensive analysis of tracks in the music catalog, focusing on identifying highly popular tracks, mapping albums to artists, assessing engagement on licensed tracks, and isolating single releases.
Description:
This task leverages the spotify_data table to extract key insights about track performance and catalog structure:
High-Streaming Tracks:
The query filters tracks with more than 1 billion streams to highlight exceptionally popular songs. This helps identify tracks with global appeal and massive audience engagement.
Album-Artist Mapping:
By retrieving both the Album and Artist columns, the analysis establishes a clear relationship between albums and their respective artists, providing a complete view of the catalog.
Licensed Track Engagement:
Filtering tracks where licensed = TRUE, the query aggregates the total number of comments to evaluate audience interaction with officially licensed content. This insight is critical for assessing the popularity and reach of licensed music.
Single Releases:
The query also isolates tracks labeled as 'single' in the Album_type column, allowing the company to analyze release strategies and performance metrics for singles compared to full albums.
Purpose & Outcome:
Identifies the most popular tracks, supporting marketing and promotional decisions.
Provides a detailed mapping of albums to artists, useful for catalog management and reporting.
Assesses audience engagement with licensed tracks to inform licensing and royalty strategies.
Highlights single releases for targeted promotion and playlist curation.
SQL Queries:
Objective:
To analyze artist performance, album characteristics, track energy levels, video engagement, and overall album viewership. This provides a comprehensive understanding of catalog performance from multiple perspectives.
Description:
These queries extract critical metrics from the spotify_data table:
Track Count per Artist
By grouping tracks by Artist and counting them, the query identifies which artists have the most content in the catalog. This is useful for analyzing artist productivity and catalog depth.
Average Danceability per Album
Calculating the average Danceability for tracks within each album helps the company assess the overall musical style or mood of an album. This insight is valuable for playlist curation, recommendation systems, and genre analysis.
Top 5 Tracks by Energy
Using a window function (dense_rank) over the Energy column, the query ranks tracks and identifies the top 5 with the highest energy levels. This highlights tracks suitable for high-intensity playlists or promotional campaigns.
Official Video Engagement
By filtering for tracks where official_video = TRUE and retrieving Views and Likes, the query assesses audience engagement with visual content. This information informs marketing strategy for video releases.
Total Album Views
Summing Views across all tracks in each album provides an aggregate measure of album popularity. This helps the company understand which albums are performing best in terms of audience reach.
Purpose & Outcome:
Identifies prolific artists and their contribution to the catalog.
Measures album-level musical characteristics for analytics and recommendation purposes.
Highlights energetic tracks for targeted marketing and playlist inclusion.
Evaluates video engagement to inform promotional strategies.
Aggregates album-level views to guide decision-making on future releases and artist support.
SQL Queries:
Objective:
To analyze track performance across streaming platforms, identify high-performing tracks per artist, evaluate audience engagement characteristics, and measure the variability of track energy within albums. These analyses enable a music distribution company to optimize content strategy, playlist curation, and marketing decisions.
Description:
Tracks with Higher Spotify Streams than YouTube:
The dataset is aggregated by track to calculate total streams on Spotify and YouTube. Using COALESCE ensures null values are handled. Tracks with more streams on Spotify than YouTube, excluding negligible YouTube activity, are identified to guide platform-specific promotion and licensing decisions.
Top Tracks per Artist:
By summing the total views for each track and applying a window function DENSE_RANK() partitioned by artist, the query identifies the top three most-viewed tracks per artist. This highlights the most popular content for targeted marketing campaigns, playlist placement, and artist performance evaluation.
Tracks with Above-Average Liveness:
The average Liveness across all tracks is calculated, and tracks exceeding this benchmark are retrieved. This identifies songs with a higher audience presence or live-performance feel, which is valuable for curating energetic playlists and live-event promotions.
Energy Variation Within Albums:
Using a common table expression, the highest and lowest energy values are calculated for each album, and the difference is computed. This analysis provides insight into the dynamic range of albums, supporting strategies around musical diversity, listener engagement, and recommendation systems.
Purpose & Outcome:
Provides insights into platform-specific streaming strengths for strategic promotion.
Highlights top-performing tracks for each artist to support marketing and playlist curation.
Evaluates track liveness to enhance recommendations, playlisting, and live-event planning.
Measures energy variation to understand album composition and guide listener engagement strategies.
SQL Queries:
Objective:
To examine the relationship between track energy and audience engagement while tracking the accumulation of likes across tracks. This enables the music distribution company to understand high-intensity tracks and measure cumulative listener interaction trends for strategic promotion.
Description:
Tracks with High Energy-to-Liveness Ratio:
The ratio of Energy to Liveness is calculated for each track, and tracks exceeding a threshold of 1.2 are identified. This metric highlights songs that are energetically intense relative to their live-performance presence. Such tracks can be prioritized for energetic playlists, radio promotion, and curated recommendations where high-intensity tracks are desirable.
Cumulative Likes Ordered by Views:
The cumulative sum of Likes is computed for all tracks, ordered by the number of Views in descending order, using a window function. This provides a running total of listener engagement across the most-viewed tracks. It offers insights into how audience attention accumulates across popular content and supports decision-making for highlighting trending tracks or allocating marketing resources.
Purpose & Outcome:
Identifies high-energy tracks that can drive listener engagement and playlist curation.
Reveals patterns of cumulative engagement, enabling the company to track and predict audience response trends.
Supports targeted promotional strategies for high-impact tracks and popular content.
SQL Queries:
This project demonstrates how advanced SQL techniques and data analysis can provide actionable insights for a music distribution company. By analyzing streaming, views, likes, and track characteristics, we were able to uncover patterns in listener behavior, track popularity, and artist performance.
Key measurable results include:
High-Performing Tracks: Identification of tracks with over 1 billion streams and high energy-to-liveness ratios highlighted songs that dominate listener engagement and have the potential for playlist prioritization.
Artist and Album Insights: Aggregations of total tracks per artist, average danceability, and total album views revealed the most productive and popular artists, helping the company focus marketing and licensing efforts on high-impact content.
Platform Performance: Comparison of Spotify versus YouTube streams identified tracks performing better on specific platforms, supporting data-driven decisions for platform-specific promotion.
Engagement Trends: Calculating cumulative likes ordered by views demonstrated how listener engagement accumulates across tracks, providing measurable trends to inform promotional strategies and track release planning.
Top Tracks Identification: Using ranking and window functions, we efficiently determined the top tracks per artist based on energy and viewership, enabling precision targeting for campaigns.
Overall Outcome:
The analysis delivers a data-driven foundation to optimize content promotion, playlist curation, and audience engagement strategies. By quantifying track performance, engagement, and platform trends, the company can make measurable, informed decisions that maximize revenue, enhance listener satisfaction, and strengthen artist promotion.
Emmanuel Olorunda
Copyright © 2025 | All Rights Reserved
Every project starts with a chat, Emmanuel is keen to conversation and open to discussing your project. he will pull in the right expertise when needed