Case Study #2: Spotify Music Analysis 🎵

Real-world SQL case study — Spotify ka Data Analyst banke complete music industry analysis karo. Top artists, streaming patterns, genre performance, revenue insights, listener behavior — 10 detailed questions with production-ready SQL queries aur business recommendations. Data Insights par.

📑 10 Questions Covered:

• Q1: Top 10 Most Streamed Songs — Ranking Analysis
• Q2: Artist Performance — Revenue & Stream Analysis
• Q3: Genre-wise Market Share — Percentage of Total
• Q4: Monthly Streaming Trends — MoM Growth
• Q5: Top Artist per Genre — Window Functions
• Q6: Listener Engagement — Avg Streams per User
• Q7: Song Popularity vs Duration Analysis
• Q8: Year-over-Year Growth by Genre
• Q9: Premium vs Free Tier Revenue Comparison
• Q10: Inactive Listeners — Churn Identification

🎵 The Situation:

Tum Spotify ke Data Analyst ho. VP of Content Strategy tumhare paas aati hain aur bolti hain:

🎯 Your Mission: Spotify database ka detailed analysis — top performers identify karo, streaming trends analyze karo, revenue patterns samjho, aur listener behavior ka deep-dive karo. Har insight ke saath actionable business recommendation dena hai.

Spotify database mein 5 tables hain — rich dataset with multiple dimensions of analysis:

🎤 Table 1: artists — Artist Master Data

Contains all artist information — name, country, debut year, genre, verified status.

🎵 Table 2: songs — Song Catalog

Contains song details — title, artist, album, release date, duration, explicit flag.

📊 Table 3: streams — Streaming History (Fact Table)

Transaction table — every stream recorded. This is the LARGEST table.

🎧 Table 4: listeners — User Information

User profiles with subscription tier, country, age, signup date.

💰 Table 5: revenue — Monthly Revenue Records

Monthly revenue per listener — subscription fees + ad revenue.

❓ Question: Find the top 10 most-streamed songs. Show song title, artist name, genre, total streams, unique listeners, average stream duration, and completion rate (avg stream duration / song duration).

💡 Step-by-step Approach:
1. Tables needed: songs + artists + streams — 3-way JOIN
2. Metrics: COUNT(streams) = total plays, COUNT(DISTINCT listener_id) = unique listeners, AVG(stream_duration_sec) = avg listen time
3. Completion Rate: (AVG stream duration / song duration) × 100 — tells us ki listeners poora gaana sunte hain ya skip karte hain
4. Sort: ORDER BY total_streams DESC, LIMIT 10

SELECT s.song_title, a.artist_name, a.genre, COUNT(st.stream_id) AS total_streams,
COUNT(DISTINCT st.listener_id) AS unique_listeners, ROUND(AVG(st.stream_duration_sec), 0) AS avg_listen_sec,
ROUND( AVG(st.stream_duration_sec) * 100.0 / s.duration_sec, 1 ) AS completion_rate_pct,
ROUND( COUNT(st.stream_id) * 1.0 / COUNT(DISTINCT st.listener_id), 1 ) AS avg_plays_per_listener
FROM songs s INNER
JOIN artists a
ON s.artist_id = a.artist_id INNER
JOIN streams st
ON s.song_id = st.song_id
GROUP BY s.song_id, s.song_title, a.artist_name, a.genre,
s.duration_sec
ORDER BY total_streams DESC
LIMIT 10;

📈 Expected Output:

158 94.3% 2.3

❓ Question: Rank artists by total streams. Show artist name, country, genre, total songs, total streams, unique listeners, avg completion rate, and estimated revenue (streams × ₹0.004 per stream). Also show each artist's % of total platform streams.

💡 Step-by-step Approach:
1. 3-way JOIN: artists + songs + streams
2. Aggregations: COUNT songs, COUNT streams, COUNT DISTINCT listeners
3. Revenue estimate: Spotify pays ~₹0.003-0.005 per stream. We use ₹0.004
4. % of total: SUM(streams) OVER() window function — each artist's share of total platform streams
5. Completion rate: AVG(stream_duration / song_duration) per artist — overall quality indicator

WITH artist_metrics AS ( SELECT a.artist_id, a.artist_name, a.country, a.genre, COUNT(DISTINCT s.song_id) AS total_songs, COUNT(st.stream_id) AS total_streams, COUNT(DISTINCT st.listener_id) AS unique_listeners, ROUND( AVG(st.stream_duration_sec * 100.0 / s.duration_sec), 1 ) AS avg_completion_pct, ROUND( COUNT(st.stream_id) * 0.004, 0 ) AS est_revenue_inr FROM artists a INNER JOIN songs s ON a.artist_id = s.artist_id INNER JOIN streams st ON s.song_id = st.song_id GROUP BY a.artist_id, a.artist_name, a.country, a.genre ) SELECT artist_name,
country, genre, total_songs, total_streams, unique_listeners,
avg_completion_pct, est_revenue_inr, ROUND( total_streams * 100.0 / SUM(total_streams) OVER (), 2 ) AS pct_of_total_streams,
DENSE_RANK() OVER ( ORDER BY total_streams DESC ) AS artist_rank
FROM artist_metrics
ORDER BY total_streams DESC
LIMIT 10;

📈 Expected Output:

5.1

❓ Question: Calculate genre-wise market share — total songs, total streams, unique listeners, avg completion rate, and percentage of total streams. Also identify genre growth potential by comparing unique listeners per stream ratio.

💡 Approach:
1. JOIN: artists (genre info) + songs + streams
2. GROUP BY genre: Aggregate all metrics per genre
3. % of total: Each genre's streams / total platform streams × 100
4. Listener density: Unique listeners / total streams — high ratio = broader reach, low ratio = niche but passionate fanbase
5. Running total: Cumulative % to see how many genres cover 80% streams (Pareto principle)

WITH genre_stats AS ( SELECT a.genre, COUNT(DISTINCT s.song_id) AS total_songs, COUNT(DISTINCT a.artist_id) AS total_artists, COUNT(st.stream_id) AS total_streams, COUNT(DISTINCT st.listener_id) AS unique_listeners, ROUND( AVG(st.stream_duration_sec * 100.0 / s.duration_sec), 1 ) AS avg_completion_pct, ROUND( COUNT(st.stream_id) * 1.0 / COUNT(DISTINCT st.listener_id), 1 ) AS streams_per_listener FROM artists a INNER JOIN songs s ON a.artist_id = s.artist_id INNER JOIN streams st ON s.song_id = st.song_id GROUP BY a.genre ) SELECT genre,
total_artists, total_songs, total_streams, unique_listeners,
avg_completion_pct, streams_per_listener, ROUND( total_streams * 100.0 / SUM(total_streams) OVER (), 2 ) AS market_share_pct,
ROUND( SUM(total_streams) OVER ( ORDER BY total_streams DESC ) * 100.0 / SUM(total_streams) OVER (), 1 ) AS cumulative_pct
FROM genre_stats
ORDER BY total_streams DESC;

📈 Expected Output:

22 1,40,000 62,000 89.5% 2.3 1.6% 100.0

❓ Question: Calculate monthly streaming metrics — total streams, unique listeners, unique songs played, avg streams per listener. Show MoM growth percentage for streams and listeners. Identify best and worst performing months.

💡 Approach:
1. CTE 1: Monthly aggregation — GROUP BY month
2. LAG(): Previous month values for comparison
3. MoM Growth: (current - previous) / previous × 100
4. Avg per listener: streams / unique_listeners — engagement indicator
5. Trend indicator: CASE WHEN se growth/decline classify karo

WITH monthly_metrics AS ( SELECT DATE_FORMAT(stream_date, '%Y-%m') AS month, COUNT(stream_id) AS total_streams, COUNT(DISTINCT listener_id) AS active_listeners, COUNT(DISTINCT song_id) AS songs_played, ROUND( COUNT(stream_id) * 1.0 / COUNT(DISTINCT listener_id), 1 ) AS avg_streams_per_user FROM streams GROUP BY DATE_FORMAT(stream_date, '%Y-%m') ) SELECT month,
total_streams, active_listeners, songs_played, avg_streams_per_user,
LAG(total_streams) OVER ( ORDER BY month ) AS prev_month_streams,
ROUND( (total_streams - LAG(total_streams) OVER (ORDER BY month)) * 100.0 / LAG(total_streams) OVER (ORDER BY month), 2 ) AS stream_growth_pct,
ROUND( (active_listeners - LAG(active_listeners) OVER (ORDER BY month)) * 100.0 / LAG(active_listeners) OVER (ORDER BY month), 2 ) AS listener_growth_pct,
CASE
WHEN total_streams > LAG(total_streams) OVER (ORDER BY month)
THEN '📈 Growth'
WHEN total_streams < LAG(total_streams) OVER (ORDER BY month)
THEN '📉 Decline'
ELSE '➡️ Stable'
END AS trend
FROM monthly_metrics
ORDER BY month DESC
LIMIT 8;

📈 Expected Output:

NULL ➡️ Stable

❓ Question: Find the #1 artist in each genre by total streams. Show their rank within genre, streams, unique listeners, and their percentage contribution to that genre's total streams.

💡 Approach:
1. CTE 1: Calculate per-artist metrics with genre
2. Window Functions: ROW_NUMBER() PARTITION BY genre ORDER BY streams DESC — rank within genre
3. Genre % contribution: artist_streams / SUM(streams) OVER(PARTITION BY genre) × 100
4. Filter: WHERE rank = 1 for top artist per genre. Also show top 3 with rank <= 3 variant

WITH artist_genre_stats AS ( SELECT a.genre, a.artist_name, a.country, COUNT(st.stream_id) AS total_streams, COUNT(DISTINCT st.listener_id) AS unique_listeners, COUNT(DISTINCT s.song_id) AS songs_count, ROW_NUMBER() OVER ( PARTITION BY a.genre ORDER BY COUNT(st.stream_id) DESC ) AS genre_rank, ROUND( COUNT(st.stream_id) * 100.0 / SUM(COUNT(st.stream_id)) OVER ( PARTITION BY a.genre ), 1 ) AS genre_share_pct FROM artists a INNER JOIN songs s ON a.artist_id = s.artist_id INNER JOIN streams st ON s.song_id = st.song_id GROUP BY a.genre, a.artist_name, a.country ) SELECT genre,
genre_rank, artist_name, country, songs_count, total_streams,
unique_listeners, genre_share_pct
FROM artist_genre_stats
WHERE genre_rank <= 3
ORDER BY genre, genre_rank;

📈 Expected Output:

28,000 5.9%

📌 Part A Complete — Questions 1-5 Done

Part B mein Q6-Q10 aayenge: Listener Engagement, Song Duration vs Popularity, YoY Growth by Genre, Premium vs Free Revenue, aur Churn Analysis. Plus Final Business Recommendations aur Key Learnings.