Skip to content
Tutorials Center
My learning

Topics

Lesson 118 of 125 ยท Projects

Project: Music Streaming Analytics

Analyze a music streaming service with SQL. Build weekly charts with rank movement, measure skip rate, group plays into listening sessions, summarize playlists and chart retention.

A streaming service logs one row every time someone starts a song. From that single table you can build almost everything a music business reports: the weekly chart, how often listeners skip, how long a listening session lasts, which playlists are popular, and how many new users are still listening a month later.

In this project you'll use the streaming sample database: 12 artists, 61 tracks, 40 users (free and premium), 18 playlists and a play log of 3,876 plays from 2 September to 26 October 2025. You will build a weekly top chart with rank movement, measure skip rate, turn the raw log into listening sessions with window functions, profile playlists, and draw a retention curve.

You need joins, CTEs, ranking and LAG and LEAD. The session stage builds on the ideas in Sessionization and Funnels, which has a general treatment; here it is applied to music.

The tables

Table One row is...
artists an artist and their main genre
tracks a song: artist, title, genre and duration_sec
users a listener with a plan (free or premium), country and join date
plays the start of one play: who, which track, played_at and seconds_listened
playlists, playlist_tracks a user's playlist and its tracks in order

The column that drives most of this project is seconds_listened. It's how long the user actually listened before the song ended or they moved on. Two business rules use it, and every number below depends on them:

  • A stream is a play with at least 30 seconds listened. Only streams count for charts and royalties.
  • A skip is a play with fewer than 30 seconds, the exact opposite.
SQL Size of the data and stream versus skip ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

Of 3,876 plays, 908 are skips: a skip rate of 23.4% overall. The next stages break that number down.

Stage 1: the weekly chart, with movement

A chart ranks tracks by streams in a week and shows how each one moved since the week before. Two CTEs do the work: one ranks the current week (13 to 19 October), the other ranks the previous week, and a LEFT JOIN puts the two ranks side by side. A track that wasn't charted last week would get NULL, which a chart calls "new".

SQL Top tracks of the week with last week's rank ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

Midnight Gold by Blue Cartridge is number 1 with 16 streams from 8 listeners, up two places from third. Echo Ember by Mira Solano jumped from 21st to 7th, the biggest climber in the top eight, while Harbor Orchard by Lantern Choir fell four places from third to seventh. Three tracks tie for third place with 12 streams, and RANK gives all three position 3 and leaves a gap so the next position is 6. If you'd rather have 3, 4, 5, switch to ROW_NUMBER and add a tie-breaker, or use DENSE_RANK for 3, 3, 3, 4. The WHERE filter on dates uses >= and < so that the week boundaries don't overlap.

Why count listeners too? A track with 16 streams from one fan is very different from 16 streams from 16 people. Real charts often rank by unique listeners for exactly this reason.

Stage 2: skip rate

Skips are the music business's early warning signal. A song with a high skip rate is losing listeners in its first seconds, and an algorithm may stop recommending it. Skip rate is simply SUM(seconds_listened < 30) / COUNT(*), because a comparison is 1 or 0 in SQLite. Split it by plan first:

SQL Skip rate by plan, and by track ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

Free users skip 30.0% of plays (683 of 2,279) against 14.1% for premium users (225 of 1,597). That is expected: free plans often limit skipping or shuffle songs at random, while premium users choose what they hear. The second query ranks tracks, but only those with at least 60 plays: a track with 5 plays and 3 skips would show 60% and mean nothing. Glass Velvet by Juno Park (30.4%) and Echo Ember by Mira Solano (30.0%) lose the most listeners early. Note that Echo Ember also climbed the chart, so a song can be both popular and skipped a lot.

When a rate is compared across groups of very different sizes, set a minimum denominator with HAVING COUNT(*) >= N. It's the simplest guard against noisy percentages, and the same trick works for conversion rates, error rates and ratings.

Stage 3: listening sessions

The play log has no "session" column, but a session is easy to define: a run of plays with no longer than 30 minutes of silence between them. After a longer gap the next play starts a new session. Building that from raw rows is the classic use of window functions:

  1. Use LAG to get the previous play's start time and length for the same user.
  2. Compute the idle time: the gap between the end of the previous song (its start plus seconds_listened) and the start of this one.
  3. Flag a play as a session start when there is no previous play or the idle time exceeds 1,800 seconds.
  4. A running SUM of those flags gives each play a session number.

Look at steps 1 to 3 on a single user first:

SQL Idle time and session starts for one user ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

User 3's first play starts a session (no previous play, so idle_sec is NULL). The next five follow within seconds of each other. The seventh play comes 6,675 seconds after the previous one ended (about 111 minutes), so it starts session number two. Multiplying the difference of two julianday values by 86,400 converts days to seconds. Now run steps 1 to 4 for everybody and summarize:

SQL Sessions for all users ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

The log turns into 532 sessions (about 13.3 per user). Free users have more sessions (304 against 228) and slightly more plays per session (7.5 against 7.0), but premium sessions last longer: 24.2 minutes on average against 21.6. Both facts come from skipping: a free user plays more songs but hears less of each one. The PARTITION BY user_id keeps one user's plays from leaking into the next user's sessions. Once you have session_no, you can answer many more questions (sessions by hour, skip rate by position in the session) with a plain GROUP BY.

Stage 4: playlists

Playlists link users to tracks through playlist_tracks. A playlist report joins four tables and answers how big and how varied playlists are, and which artists the users collect:

SQL Playlist profile and most-collected artists ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

The longest playlist is a free user's "Workout" at 48.9 minutes over 11 tracks and 7 different artists. The average playlist holds about 8 tracks. The two most-collected artists are tied: The Paper Foxes and The Slow Rivers, each with 19 appearances in playlists. Compare that with the chart in Stage 1: playlists show what people keep, charts show what they played this week. Both are useful, and they don't always agree.

Stage 5: retention

Retention answers "of the people who joined, how many are still listening N weeks later?" Each user has a joined_on date, so give every play a week since joining: the number of days between the join date and the play, divided by 7 and cut to a whole number. Count the distinct users with at least one play in each week, and divide by everyone who joined.

SQL Weekly retention curve ยท database: streaming

Press Run (or Ctrl + Enter) to see the result here.

In their first week 37 of the 40 users played something (92.5%). The curve then falls: 72.5% in week 1, 55.0% in week 2, 37.5% in week 3, 20% in week 5 and just 7.5% by week 7. Week 4 (40.0%) is slightly higher than week 3, a reminder that this measures "active in that week", not "still a user": people skip a week and return. The shape matters more than any single point. A steep fall in week 1 means onboarding is the problem. A fall that levels off means you have a loyal core.

Here the later weeks are also partly censored: users who joined in October have not had seven weeks yet, so they cannot be active in week 7. For a cleaner curve, restrict each week to the users old enough to have reached it. The next step in a real analysis would also split the curve by plan to see whether premium users stay longer.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between a play, a stream and a skip in this project?

    Every row of plays is a play. The 30-second threshold splits plays into streams (count for charts and royalties) and skips.
  2. 2. Why does the chart query use RANK() instead of ROW_NUMBER()?

    RANK gives tied tracks the same position. ROW_NUMBER would break ties arbitrarily, which would be unfair to equal tracks.
  3. 3. In the session query, what does the running SUM(new_session) OVER (PARTITION BY user_id ORDER BY played_at, id) produce?

    new_session is 1 on the first play of a session and 0 elsewhere. A running total of 0s and 1s stays constant inside a session and goes up by one at each start, so it works as a session id.
  4. 4. Why does the track skip-rate query use HAVING COUNT(*) >= 60?

    A rate from a handful of plays swings wildly: 3 skips out of 5 is 60%. A minimum denominator keeps the ranking meaningful.

Summary

  • Define business terms first (a stream is 30 seconds or more, a skip is under 30) and write every query from those definitions.
  • Build a chart with RANK() in two CTEs (this week, last week) and a LEFT JOIN; NULL in last week's rank means a new entry.
  • Rates need minimum denominators (HAVING COUNT(*) >= N) before you rank them.
  • Sessionization is LAG for the previous play, an idle-time test, a 0/1 start flag and a running SUM that turns flags into session numbers.
  • Compute retention as distinct active users per week since joining divided by all joiners, and remember that recent joiners have not had time to reach later weeks.

Next: Project: Ride Booking Analysis, where requests, cancellations and peak hours take center stage.