Analyze a Music Streaming Service with SQL

Write advanced SQL queries to analyze a music streaming database.

Introduction

30 Second Summary

Every time you hit play on a song, a streaming service logs that moment. Behind every "top artist" recommendation and "year in review" summary is a database query turning raw listening data into insights.

In this project, you will build a music streaming analytics database and write progressively advanced SQL queries to answer real business questions about artist popularity, user engagement, and content performance.

What You'll Build

A SQLite database with 5 interrelated tables modelling a music streaming service, paired with 12+ analytical queries that demonstrate the SQL patterns most commonly tested in data analyst interviews.

By the end of this project, you'll have:

A fully populated multi-table relational database with artists, albums, tracks, users, and listening history.
A library of analytical queries using JOINs, aggregates, subqueries, and CTEs to answer business questions.
Hands-on experience with window functions like ROW_NUMBER, RANK, LAG, and running totals.
Secret Mission: Build a cohort retention analysis that tracks how user listening habits change over time.

Are there any prerequisites?

Basic SQL knowledge is helpful. You should be comfortable with SELECT, WHERE, and simple JOINs. This project will take you from that foundation into the intermediate and advanced patterns that data analyst roles require.

Set Up DB Browser for SQLite

To write and test SQL queries, you need a tool that lets you create databases, browse data, and execute statements.

In this step, you will install DB Browser for SQLite, a visual database tool that gives you a spreadsheet-like interface plus a full SQL editor. This lets you focus entirely on writing SQL without fighting infrastructure.

In this step, get ready to:

Install DB Browser for SQLite on your Mac.
Create a new database file for the project.

Download and install DB Browser for SQLite

DB Browser for SQLite is a free, open-source GUI for SQLite databases. It works on both Apple Silicon and Intel Macs.

Check if you already have DB Browser for SQLite installed by pressing Cmd+Space and typing DB Browser for SQLite.

✔️ I see version 3.13 or higher

If DB Browser for SQLite appeared in your Spotlight results and opened, check the version.

Click DB Browser for SQLite in the menu bar at the top of your screen.
Select About DB Browser for SQLite.
Confirm the version number shows 3.13 or higher.

You are all set. Skip ahead to the next substep where you create a new database.

ⓧ I see an older version

If you opened DB Browser for SQLite and checked the About dialog but see a version lower than 3.13, download the latest release to replace it.

Open your browser and go to https://sqlitebrowser.org/dl/.
Under the macOS section, click DB Browser for SQLite (Universal) to download the v3.13.1 release.
Open the downloaded .dmg file.
Drag the DB Browser for SQLite icon to your Applications folder, replacing the existing version when prompted.
Launch DB Browser for SQLite from Applications.

You should see the main window with tabs: Database Structure, Browse Data, Edit Pragmas, and Execute SQL.

ⓧ I don't have it installed

If nothing shows up in Spotlight, you need to install DB Browser for SQLite from scratch.

Open your browser and go to https://sqlitebrowser.org/dl/.
Under the macOS section, click DB Browser for SQLite (Universal) to download the v3.13.1 release.
Open the downloaded .dmg file.
Drag the DB Browser for SQLite icon to your Applications folder.
Press Cmd+Space and type DB Browser for SQLite, then press Enter to launch it.

macOS may ask for permission

If you see a dialog saying the app is from an unidentified developer, go to System Settings > Privacy & Security and click Open Anyway.

You should see the main window with tabs: Database Structure, Browse Data, Edit Pragmas, and Execute SQL.

Why DB Browser instead of the terminal?

Your Mac already has SQLite installed, so you could run queries in Terminal. DB Browser gives you a visual spreadsheet-like interface for browsing data, plus an SQL editor with syntax highlighting.

This lets you focus on learning SQL patterns instead of memorizing terminal commands.

Create a new database

With DB Browser open, you need to create the database file that will store all of your tables and data for this project.

Click New Database in the toolbar at the top of the window.
In the save dialog, navigate to your Desktop.
Name the file streaming_analytics.db and click Save.

What just happened?

You created an empty SQLite database file on your Desktop. All your tables, data, and queries will live inside this single .db file.

A Create Table dialog will appear automatically. You will create tables using SQL instead, so dismiss this dialog.

Click Cancel to close the Create Table dialog.
Click the Execute SQL tab near the top of the window.

You should see an empty SQL editor pane with a Run button (a play icon) above it. This is where you will write all your queries for the rest of the project.

Don't see the Execute SQL tab?

Make sure you saved the database file first. The tabs only appear after a database is open. If you accidentally closed the file, click Open Database in the toolbar and select streaming_analytics.db from your Desktop.

Help me find the Execute SQL tab in DB Browser for SQLite.

Your environment is ready. Next up, you will build the database schema and load it with sample data so you have something to query against.

Build the Database Schema and Load Data

Your database file is created and ready to hold data. The next step is to design the structure that organizes your music streaming information into related tables.

A relational database stores data across multiple connected tables instead of one giant spreadsheet. You will create 5 tables that model how a streaming service actually works: artists release albums, albums contain tracks, users listen to tracks.

In this step, get ready to:

Create 5 interrelated tables with primary keys and foreign keys.
Populate the tables with sample data (8 artists, 10 albums, 20 tracks, 6 users, 52 listening records).
Verify the relationships work by running a JOIN query.

Create the tables

Your database needs a schema before it can hold any data. The schema defines what columns each table has and how tables relate to each other through foreign keys.

In the Execute SQL tab (already open from the previous step), clear any existing text in the editor pane.
Paste the following SQL to create all 5 tables with their relationships:

-- Create the artists table
CREATE TABLE artists (
    artist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    genre TEXT NOT NULL,
    country TEXT NOT NULL
);

-- Create the albums table
CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist_id INTEGER NOT NULL,
    release_year INTEGER NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Create the tracks table
CREATE TABLE tracks (
    track_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    album_id INTEGER NOT NULL,
    duration_seconds INTEGER NOT NULL,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Create the users table
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    signup_date TEXT NOT NULL,
    plan_type TEXT NOT NULL
);

-- Create the listening_history table
CREATE TABLE listening_history (
    listen_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    track_id INTEGER NOT NULL,
    listened_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);

Click the Run button (the play icon above the editor), or press Cmd+Return to execute.

What does this SQL do?

Each CREATE TABLE statement defines a table with typed columns. INTEGER PRIMARY KEY gives each row a unique identifier.

The FOREIGN KEY lines create relationships between tables. For example, albums.artist_id references artists.artist_id, connecting every album to exactly one artist.

TEXT NOT NULL means the column stores text and cannot be left empty. This enforces data quality at the database level.

Switch to the Database Structure tab at the top of the window.
Confirm you see all 5 tables listed: artists, albums, tracks, users, and listening_history.

Don't see all 5 tables?

Check the bottom of the Execute SQL pane for error messages. A typo in a table or column name will prevent that table from being created.
Make sure you pasted the entire SQL block, including the final listening_history table. Partial pastes are the most common cause.
If you see an error like "table already exists", it means part of the SQL ran successfully before failing. Click Execute SQL tab, type DROP TABLE IF EXISTS for each table, run it, then re-paste and run the full block.

Help me fix my table creation error

Insert sample data

Empty tables are not useful for practicing queries. You need realistic sample data that exercises the relationships you just defined.

Switch back to the Execute SQL tab.
Clear the editor pane (select all with Cmd+A, then delete).
Paste the following INSERT statements to populate all 5 tables:

-- Insert artists
INSERT INTO artists (artist_id, name, genre, country) VALUES
(1, 'Luna Wave', 'Electronic', 'Sweden'),
(2, 'The Midnight Owls', 'Rock', 'United States'),
(3, 'Sora', 'Pop', 'Japan'),
(4, 'DJ Cosmos', 'Electronic', 'Germany'),
(5, 'River Stone', 'Folk', 'Canada'),
(6, 'Neon Hearts', 'Pop', 'South Korea'),
(7, 'Black Timber', 'Rock', 'Australia'),
(8, 'Aria Blue', 'Folk', 'Ireland');

-- Insert albums
INSERT INTO albums (album_id, title, artist_id, release_year) VALUES
(1, 'Digital Dreams', 1, 2023),
(2, 'Synthetic Sunrise', 1, 2025),
(3, 'Night Flight', 2, 2024),
(4, 'Starlight', 3, 2023),
(5, 'Tokyo Glow', 3, 2025),
(6, 'Orbit', 4, 2024),
(7, 'Wilderness', 5, 2023),
(8, 'Heartbeat City', 6, 2024),
(9, 'Ironwood', 7, 2025),
(10, 'Ocean Letters', 8, 2024);

-- Insert tracks
INSERT INTO tracks (track_id, title, album_id, duration_seconds) VALUES
(1, 'Pulse', 1, 245),
(2, 'Neon Rain', 1, 198),
(3, 'Dawn Protocol', 2, 312),
(4, 'Afterglow', 2, 267),
(5, 'Runway', 3, 224),
(6, 'Tailwind', 3, 195),
(7, 'Crystal', 4, 210),
(8, 'Bloom', 4, 188),
(9, 'Shibuya Lights', 5, 233),
(10, 'Orbit Zero', 6, 278),
(11, 'Deep Space', 6, 301),
(12, 'Campfire Stories', 7, 265),
(13, 'Mountain Pass', 7, 242),
(14, 'Signal', 8, 199),
(15, 'Voltage', 8, 215),
(16, 'Steel Rain', 9, 248),
(17, 'Granite', 9, 271),
(18, 'Tide Pool', 10, 223),
(19, 'Anchor', 10, 256),
(20, 'Salt Wind', 10, 189);

-- Insert users
INSERT INTO users (user_id, username, signup_date, plan_type) VALUES
(1, 'alex_listens', '2024-01-15', 'premium'),
(2, 'beat_explorer', '2024-02-20', 'free'),
(3, 'melody_fan', '2024-01-08', 'premium'),
(4, 'sound_wave', '2024-03-12', 'free'),
(5, 'tune_smith', '2024-02-01', 'premium'),
(6, 'bass_drop', '2024-03-28', 'free');

-- Insert listening history
INSERT INTO listening_history (listen_id, user_id, track_id, listened_at) VALUES
(1, 1, 1, '2024-01-20 08:30:00'),
(2, 1, 2, '2024-01-20 08:34:00'),
(3, 1, 5, '2024-01-21 19:00:00'),
(4, 1, 7, '2024-02-02 10:15:00'),
(5, 1, 9, '2024-02-14 21:00:00'),
(6, 1, 10, '2024-03-01 07:45:00'),
(7, 1, 3, '2024-03-15 20:00:00'),
(8, 1, 14, '2024-04-01 12:00:00'),
(9, 2, 3, '2024-02-22 15:30:00'),
(10, 2, 4, '2024-02-22 15:35:00'),
(11, 2, 10, '2024-03-01 09:00:00'),
(12, 2, 11, '2024-03-01 09:05:00'),
(13, 2, 16, '2024-03-20 18:00:00'),
(14, 2, 17, '2024-03-20 18:04:00'),
(15, 2, 1, '2024-04-10 11:00:00'),
(16, 3, 7, '2024-01-10 14:00:00'),
(17, 3, 8, '2024-01-10 14:04:00'),
(18, 3, 9, '2024-01-15 22:00:00'),
(19, 3, 14, '2024-02-05 16:30:00'),
(20, 3, 15, '2024-02-05 16:34:00'),
(21, 3, 12, '2024-02-20 10:00:00'),
(22, 3, 18, '2024-03-10 08:00:00'),
(23, 3, 5, '2024-03-25 19:00:00'),
(24, 3, 1, '2024-04-02 07:30:00'),
(25, 4, 10, '2024-03-15 20:00:00'),
(26, 4, 11, '2024-03-15 20:05:00'),
(27, 4, 16, '2024-03-20 12:00:00'),
(28, 4, 5, '2024-04-01 18:30:00'),
(29, 4, 6, '2024-04-01 18:34:00'),
(30, 4, 19, '2024-04-15 09:00:00'),
(31, 5, 1, '2024-02-05 08:00:00'),
(32, 5, 2, '2024-02-05 08:04:00'),
(33, 5, 3, '2024-02-10 21:00:00'),
(34, 5, 12, '2024-02-15 11:30:00'),
(35, 5, 13, '2024-02-15 11:34:00'),
(36, 5, 7, '2024-03-01 16:00:00'),
(37, 5, 18, '2024-03-10 07:00:00'),
(38, 5, 20, '2024-03-10 07:04:00'),
(39, 5, 9, '2024-04-05 22:00:00'),
(40, 5, 14, '2024-04-20 13:00:00'),
(41, 6, 16, '2024-04-01 10:00:00'),
(42, 6, 17, '2024-04-01 10:05:00'),
(43, 6, 5, '2024-04-10 19:30:00'),
(44, 6, 6, '2024-04-10 19:34:00'),
(45, 6, 10, '2024-04-15 08:00:00'),
(46, 6, 1, '2024-04-20 21:00:00'),
(47, 1, 12, '2024-04-15 10:00:00'),
(48, 2, 9, '2024-04-20 14:00:00'),
(49, 3, 3, '2024-04-10 18:00:00'),
(50, 4, 1, '2024-04-25 09:00:00'),
(51, 5, 5, '2024-04-28 20:00:00'),
(52, 6, 14, '2024-04-28 15:00:00');

Click the Run button (or press Cmd+Return) to execute all the INSERT statements.

What does this data represent?

You now have a miniature music streaming service with 8 artists across 4 genres (Electronic, Rock, Pop, Folk), 10 albums spanning 2023 to 2025, and 20 tracks with realistic durations.

The 6 users have different plan types (free and premium) and signup dates. The 52 listening history records capture who listened to what and when, giving you enough data to find meaningful patterns.

Switch to the Browse Data tab at the top of the window.
Select each table from the Table dropdown and confirm rows appear: 8 artists, 10 albums, 20 tracks, 6 users, and 52 listening history records.

Seeing 0 rows in a table?

Check the status bar at the bottom of the Execute SQL pane for error messages. A foreign key violation (inserting an album with a non-existent artist_id) would prevent that INSERT block from completing.
Make sure you ran the CREATE TABLE statements first. INSERT statements fail silently if the target table does not exist.
If you accidentally ran the INSERT statements twice, you will see a "UNIQUE constraint failed" error. That is fine. Your data from the first run is already in the tables.

Help me fix my INSERT statement error

Verify relationships with a JOIN query

The tables are connected through foreign keys, but you should confirm those connections actually work. A JOIN query pulls data from multiple tables in a single result.

Switch back to the Execute SQL tab.
Clear the editor and paste this query to verify your foreign key relationships:

-- Verify relationships: show tracks with their album and artist
SELECT
    t.title AS track_title,
    al.title AS album_title,
    a.name AS artist_name
FROM tracks t
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
LIMIT 5;

Click the Run button (or press Cmd+Return).

What does this query do?

This query uses JOIN to follow the foreign key chain: tracks connect to albums via album_id, and albums connect to artists via artist_id. The result shows each track alongside its album and artist name in a single row.

LIMIT 5 restricts the output to 5 rows so the results are easy to scan. You will remove this limit in later queries when you need the full picture.

Confirm you see 5 rows in the results pane below the editor, with track titles alongside their album and artist names.

Seeing an empty result or an error?

If you see "no such table", the CREATE TABLE step did not complete successfully. Switch to Database Structure to check which tables exist, then re-run the missing CREATE statements.
If you see 0 rows but no error, your INSERT statements for tracks, albums, or artists did not run. Switch to Browse Data and check each table has rows.

Help me debug my JOIN query

✔️ Awesome, I've got everything!

Great. Make sure you save your database by pressing Cmd+S (or clicking Write Changes in the toolbar). This persists all your tables and data to the streaming_analytics.db file.

ⓧ I'd like to double check the full code

Here is the complete table creation SQL:

-- Create the artists table
CREATE TABLE artists (
    artist_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    genre TEXT NOT NULL,
    country TEXT NOT NULL
);

-- Create the albums table
CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    artist_id INTEGER NOT NULL,
    release_year INTEGER NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

-- Create the tracks table
CREATE TABLE tracks (
    track_id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    album_id INTEGER NOT NULL,
    duration_seconds INTEGER NOT NULL,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

-- Create the users table
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    signup_date TEXT NOT NULL,
    plan_type TEXT NOT NULL
);

-- Create the listening_history table
CREATE TABLE listening_history (
    listen_id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    track_id INTEGER NOT NULL,
    listened_at TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (track_id) REFERENCES tracks(track_id)
);

And here is the complete data insertion SQL:

-- Insert artists
INSERT INTO artists (artist_id, name, genre, country) VALUES
(1, 'Luna Wave', 'Electronic', 'Sweden'),
(2, 'The Midnight Owls', 'Rock', 'United States'),
(3, 'Sora', 'Pop', 'Japan'),
(4, 'DJ Cosmos', 'Electronic', 'Germany'),
(5, 'River Stone', 'Folk', 'Canada'),
(6, 'Neon Hearts', 'Pop', 'South Korea'),
(7, 'Black Timber', 'Rock', 'Australia'),
(8, 'Aria Blue', 'Folk', 'Ireland');

-- Insert albums
INSERT INTO albums (album_id, title, artist_id, release_year) VALUES
(1, 'Digital Dreams', 1, 2023),
(2, 'Synthetic Sunrise', 1, 2025),
(3, 'Night Flight', 2, 2024),
(4, 'Starlight', 3, 2023),
(5, 'Tokyo Glow', 3, 2025),
(6, 'Orbit', 4, 2024),
(7, 'Wilderness', 5, 2023),
(8, 'Heartbeat City', 6, 2024),
(9, 'Ironwood', 7, 2025),
(10, 'Ocean Letters', 8, 2024);

-- Insert tracks
INSERT INTO tracks (track_id, title, album_id, duration_seconds) VALUES
(1, 'Pulse', 1, 245),
(2, 'Neon Rain', 1, 198),
(3, 'Dawn Protocol', 2, 312),
(4, 'Afterglow', 2, 267),
(5, 'Runway', 3, 224),
(6, 'Tailwind', 3, 195),
(7, 'Crystal', 4, 210),
(8, 'Bloom', 4, 188),
(9, 'Shibuya Lights', 5, 233),
(10, 'Orbit Zero', 6, 278),
(11, 'Deep Space', 6, 301),
(12, 'Campfire Stories', 7, 265),
(13, 'Mountain Pass', 7, 242),
(14, 'Signal', 8, 199),
(15, 'Voltage', 8, 215),
(16, 'Steel Rain', 9, 248),
(17, 'Granite', 9, 271),
(18, 'Tide Pool', 10, 223),
(19, 'Anchor', 10, 256),
(20, 'Salt Wind', 10, 189);

-- Insert users
INSERT INTO users (user_id, username, signup_date, plan_type) VALUES
(1, 'alex_listens', '2024-01-15', 'premium'),
(2, 'beat_explorer', '2024-02-20', 'free'),
(3, 'melody_fan', '2024-01-08', 'premium'),
(4, 'sound_wave', '2024-03-12', 'free'),
(5, 'tune_smith', '2024-02-01', 'premium'),
(6, 'bass_drop', '2024-03-28', 'free');

-- Insert listening history
INSERT INTO listening_history (listen_id, user_id, track_id, listened_at) VALUES
(1, 1, 1, '2024-01-20 08:30:00'),
(2, 1, 2, '2024-01-20 08:34:00'),
(3, 1, 5, '2024-01-21 19:00:00'),
(4, 1, 7, '2024-02-02 10:15:00'),
(5, 1, 9, '2024-02-14 21:00:00'),
(6, 1, 10, '2024-03-01 07:45:00'),
(7, 1, 3, '2024-03-15 20:00:00'),
(8, 1, 14, '2024-04-01 12:00:00'),
(9, 2, 3, '2024-02-22 15:30:00'),
(10, 2, 4, '2024-02-22 15:35:00'),
(11, 2, 10, '2024-03-01 09:00:00'),
(12, 2, 11, '2024-03-01 09:05:00'),
(13, 2, 16, '2024-03-20 18:00:00'),
(14, 2, 17, '2024-03-20 18:04:00'),
(15, 2, 1, '2024-04-10 11:00:00'),
(16, 3, 7, '2024-01-10 14:00:00'),
(17, 3, 8, '2024-01-10 14:04:00'),
(18, 3, 9, '2024-01-15 22:00:00'),
(19, 3, 14, '2024-02-05 16:30:00'),
(20, 3, 15, '2024-02-05 16:34:00'),
(21, 3, 12, '2024-02-20 10:00:00'),
(22, 3, 18, '2024-03-10 08:00:00'),
(23, 3, 5, '2024-03-25 19:00:00'),
(24, 3, 1, '2024-04-02 07:30:00'),
(25, 4, 10, '2024-03-15 20:00:00'),
(26, 4, 11, '2024-03-15 20:05:00'),
(27, 4, 16, '2024-03-20 12:00:00'),
(28, 4, 5, '2024-04-01 18:30:00'),
(29, 4, 6, '2024-04-01 18:34:00'),
(30, 4, 19, '2024-04-15 09:00:00'),
(31, 5, 1, '2024-02-05 08:00:00'),
(32, 5, 2, '2024-02-05 08:04:00'),
(33, 5, 3, '2024-02-10 21:00:00'),
(34, 5, 12, '2024-02-15 11:30:00'),
(35, 5, 13, '2024-02-15 11:34:00'),
(36, 5, 7, '2024-03-01 16:00:00'),
(37, 5, 18, '2024-03-10 07:00:00'),
(38, 5, 20, '2024-03-10 07:04:00'),
(39, 5, 9, '2024-04-05 22:00:00'),
(40, 5, 14, '2024-04-20 13:00:00'),
(41, 6, 16, '2024-04-01 10:00:00'),
(42, 6, 17, '2024-04-01 10:05:00'),
(43, 6, 5, '2024-04-10 19:30:00'),
(44, 6, 6, '2024-04-10 19:34:00'),
(45, 6, 10, '2024-04-15 08:00:00'),
(46, 6, 1, '2024-04-20 21:00:00'),
(47, 1, 12, '2024-04-15 10:00:00'),
(48, 2, 9, '2024-04-20 14:00:00'),
(49, 3, 3, '2024-04-10 18:00:00'),
(50, 4, 1, '2024-04-25 09:00:00'),
(51, 5, 5, '2024-04-28 20:00:00'),
(52, 6, 14, '2024-04-28 15:00:00');

Your database is fully loaded with interconnected data. Next up, you will write analytical queries using multi-table JOINs and aggregate functions to answer real business questions about your streaming service.

Analyze with JOINs and Aggregates

Your database is loaded with artists, albums, tracks, users, and listening history. Now it is time to ask the data real business questions.

In this step, you will write queries that chain multiple tables together using JOINs, then summarize results with aggregate functions like COUNT and SUM. These are the exact patterns data analyst interviewers test for.

In this step, get ready to:

Write multi-table JOIN queries to connect artists, albums, tracks, and listens.
Use GROUP BY with HAVING to filter aggregated results.
Build conditional aggregations with CASE WHEN to pivot data by genre.

Write multi-table JOIN queries

Your data lives across five tables. To answer a question like "how popular is each artist?", you need to connect artists to albums to tracks to listening_history. Multi-table JOINs let you traverse these relationships in a single query.

In DB Browser for SQLite, switch to the Execute SQL tab.
Clear any SQL from the previous step, then paste this query to count total listens per artist:

-- Total listens per artist (multi-table JOIN)
SELECT
    a.name AS artist_name,
    COUNT(lh.listen_id) AS total_listens
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY a.name
ORDER BY total_listens DESC;

What does this query do?

FROM artists a starts at the artists table and gives it the alias a for shorter references.
Each JOIN connects one table to the next using their shared foreign key. The chain goes: artists to albums to tracks to listening_history.
COUNT(lh.listen_id) counts how many listening records exist for each artist after the tables are joined.
GROUP BY a.name collapses all rows for the same artist into one summary row.

Click the Run button (play icon) or press Cmd+Return to execute the query.
In the results pane below, you should see 8 rows listing each artist's name and their total listen count, sorted from most to least popular.

Seeing no results or an error?

If you see "no such table", your data from Step 2 may not have been committed. Switch to the Browse Data tab and check that the artists table has 8 rows.
If you see a syntax error, check that you have no leftover SQL from a previous query above this one in the editor.
Help me debug my JOIN query.

Now you know overall artist popularity. The next question is more specific: which individual tracks get the most plays?

Clear the SQL editor and paste this query to find the top 5 most-played tracks:

-- Top 5 most-played tracks with artist and album
SELECT
    t.title AS track_title,
    al.title AS album_title,
    a.name AS artist_name,
    COUNT(lh.listen_id) AS play_count
FROM tracks t
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY t.title, al.title, a.name
ORDER BY play_count DESC
LIMIT 5;

What's different about this query?

This query starts from tracks instead of artists because you want track-level detail. The JOINs traverse the same foreign keys but in a different direction.

LIMIT 5 caps the output at 5 rows after sorting, giving you a "top N" result.

Click Run.
You should see 5 rows showing each track title, its album, its artist, and the play count, sorted from highest to lowest.

Getting fewer than 5 rows?

If you only see 1-2 rows, check that the listening_history table has all 52 rows. Switch to Browse Data and select listening_history from the table dropdown to verify.
Why is my top tracks query returning unexpected results?

Filter with GROUP BY and HAVING

GROUP BY collapses rows into summary groups. But what if you only want groups that meet a threshold? That is where HAVING comes in. It filters groups after aggregation, the same way WHERE filters individual rows before aggregation.

Clear the SQL editor and paste this query to find artists with more than 5 total listens:

-- Artists with more than 5 listens (GROUP BY + HAVING)
SELECT
    a.name AS artist_name,
    COUNT(lh.listen_id) AS total_listens
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY a.name
HAVING COUNT(lh.listen_id) > 5
ORDER BY total_listens DESC;

What's the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens. HAVING filters entire groups after the aggregation runs.

You cannot use WHERE COUNT(...) > 5 because COUNT does not exist yet at the WHERE stage. The count only exists once the rows are grouped.

Click Run.
You should see fewer artists than before. Only those with more than 5 listens appear in the results.

Seeing all 8 artists instead of a filtered list?

Make sure the HAVING clause comes after GROUP BY and before ORDER BY. SQL enforces a strict clause order.
Check that you wrote > 5 (greater than 5), not >= 5 (greater than or equal to 5).
Help me understand why HAVING isn't filtering my results.

Next, you will calculate how much total time each user has spent listening. This uses SUM instead of COUNT to add up actual values rather than just counting rows.

Clear the SQL editor and paste this query to calculate each user's total listening time in minutes:

-- Each user's total listening time in minutes
SELECT
    u.username,
    ROUND(SUM(t.duration_seconds) / 60.0, 1) AS total_minutes
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
GROUP BY u.username
ORDER BY total_minutes DESC;

What does this query do?

SUM(t.duration_seconds) adds up the duration of every track the user listened to.
Dividing by 60.0 (not 60) forces decimal division so you get precise minutes instead of truncated integers.
ROUND(..., 1) rounds the result to one decimal place for readability.

Click Run.
You should see 6 rows, one per user, with decimal values in the total_minutes column (e.g. 38.2, 35.7).

Seeing whole numbers instead of decimals?

Make sure you divide by 60.0 (with the decimal point), not 60. Integer division truncates the remainder.
My SUM query isn't giving decimal results. Help me fix it.

Split metrics with CASE WHEN

Sometimes you want to break a single metric into categories within the same row. Instead of running separate queries per genre, you can use CASE WHEN inside an aggregate to create a column for each category in one pass.

Clear the SQL editor and paste this query to count each user's listens split by genre:

-- Listens per user split by genre (conditional aggregation with CASE)
SELECT
    u.username,
    SUM(CASE WHEN a.genre = 'Electronic' THEN 1 ELSE 0 END) AS electronic_listens,
    SUM(CASE WHEN a.genre = 'Rock' THEN 1 ELSE 0 END) AS rock_listens,
    SUM(CASE WHEN a.genre = 'Pop' THEN 1 ELSE 0 END) AS pop_listens,
    SUM(CASE WHEN a.genre = 'Folk' THEN 1 ELSE 0 END) AS folk_listens
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
GROUP BY u.username
ORDER BY u.username;

What does CASE WHEN do inside SUM?

Each CASE WHEN a.genre = 'Electronic' THEN 1 ELSE 0 END produces a 1 for Electronic tracks and a 0 for everything else. Wrapping it in SUM() counts only the rows matching that genre.

This pattern is called conditional aggregation. It is the SQL equivalent of a pivot table, turning row values (genres) into separate columns.

Click Run.
You should see 6 rows (one per user) with columns electronic_listens, rock_listens, pop_listens, and folk_listens showing integer counts.

Seeing all zeros in your genre columns?

Check that the genre strings in your CASE statements match the data exactly. Genre values are case-sensitive: 'Electronic' is not the same as 'electronic'.
Make sure you are joining all the way through to the artists table. The genre column lives on artists, not on tracks or albums.
My CASE WHEN conditional aggregation is returning all zeros. Help me debug it.

✔️ Awesome, I've got everything!

Great. You have written five analytical queries covering multi-table JOINs, GROUP BY with HAVING, SUM with decimal division, and conditional aggregation with CASE WHEN.

ⓧ I'd like to double check the full code

Here is the complete set of queries from this step for reference:

-- Total listens per artist (multi-table JOIN)
SELECT
    a.name AS artist_name,
    COUNT(lh.listen_id) AS total_listens
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY a.name
ORDER BY total_listens DESC;

-- Top 5 most-played tracks with artist and album
SELECT
    t.title AS track_title,
    al.title AS album_title,
    a.name AS artist_name,
    COUNT(lh.listen_id) AS play_count
FROM tracks t
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY t.title, al.title, a.name
ORDER BY play_count DESC
LIMIT 5;

-- Artists with more than 5 listens (GROUP BY + HAVING)
SELECT
    a.name AS artist_name,
    COUNT(lh.listen_id) AS total_listens
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
JOIN tracks t ON al.album_id = t.album_id
JOIN listening_history lh ON t.track_id = lh.track_id
GROUP BY a.name
HAVING COUNT(lh.listen_id) > 5
ORDER BY total_listens DESC;

-- Each user's total listening time in minutes
SELECT
    u.username,
    ROUND(SUM(t.duration_seconds) / 60.0, 1) AS total_minutes
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
GROUP BY u.username
ORDER BY total_minutes DESC;

-- Listens per user split by genre (conditional aggregation with CASE)
SELECT
    u.username,
    SUM(CASE WHEN a.genre = 'Electronic' THEN 1 ELSE 0 END) AS electronic_listens,
    SUM(CASE WHEN a.genre = 'Rock' THEN 1 ELSE 0 END) AS rock_listens,
    SUM(CASE WHEN a.genre = 'Pop' THEN 1 ELSE 0 END) AS pop_listens,
    SUM(CASE WHEN a.genre = 'Folk' THEN 1 ELSE 0 END) AS folk_listens
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
JOIN albums al ON t.album_id = al.album_id
JOIN artists a ON al.artist_id = a.artist_id
GROUP BY u.username
ORDER BY u.username;

You can now chain tables together and summarize data with aggregate functions. Next up, you will nest queries inside each other using subqueries and CTEs to solve even more layered analytical problems.

Write Subqueries and CTEs

Your JOIN and aggregate queries answer direct questions like "who has the most listens?" But real analysis often requires answering questions that depend on the answer to another question first.

Subqueries let you nest one question inside another. Common Table Expressions (CTEs) do the same thing but lay out each step in a readable, top-to-bottom flow. Both patterns are staples of data analyst interviews.

In this step, get ready to:

Write subqueries to answer nested analytical questions.
Rewrite subqueries as CTEs for improved readability.
Combine CTEs with JOINs to merge summary metrics with profile data.

Write subqueries for nested logic

A subquery is a SELECT statement nested inside another query. It lets you compute an intermediate result and use it as a filter or comparison value in the outer query.

Clear the SQL editor in the Execute SQL tab.
Paste the following query to find users who have listened to more tracks than the average user:

-- Subquery: users who listened to more tracks than average
SELECT
    u.username,
    COUNT(lh.listen_id) AS listen_count
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
GROUP BY u.username
HAVING COUNT(lh.listen_id) > (
    SELECT AVG(user_listens)
    FROM (
        SELECT COUNT(listen_id) AS user_listens
        FROM listening_history
        GROUP BY user_id
    )
);

What does this query do?

The innermost subquery counts listens per user. The middle subquery takes the average of those counts. The outer query then filters to only show users whose personal count exceeds that average.

This is a two-level nesting pattern. The database resolves from the inside out, like solving parentheses in math.

Click the Run button (play icon) or press Cmd+Return to execute.
You should see a results table showing usernames and their listen counts. Only users above the average appear.

Getting a syntax error?

Check that you have matching parentheses. The HAVING clause contains two opening parentheses and two closing ones. A missing parenthesis after GROUP BY user_id is the most common mistake.

Help me debug my subquery syntax.

Now you'll write a correlated subquery. Unlike a regular subquery that runs once, a correlated subquery runs once for each row in the outer query.

Clear the editor and paste the following query to find each artist's most recent album:

-- Correlated subquery: each artist's most recent album
SELECT
    a.name AS artist_name,
    al.title AS album_title,
    al.release_year
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
WHERE al.release_year = (
    SELECT MAX(al2.release_year)
    FROM albums al2
    WHERE al2.artist_id = a.artist_id
);

What makes this correlated?

The inner subquery references a.artist_id from the outer query. For each artist row, the subquery finds that specific artist's maximum release year. The WHERE clause then keeps only the album matching that max year.

Click Run.
You should see one row per artist showing their most recent album title and release year.

Seeing too many rows?

Make sure the inner SELECT uses al2.artist_id = a.artist_id to correlate with the outer query. Without this WHERE clause, the subquery returns the global max year instead of the per-artist max.

Help me understand why my correlated subquery returns wrong results.

Rewrite with CTEs for readability

Subqueries work, but deeply nested ones become hard to read. CTEs let you name each intermediate step with a WITH clause, so the query reads top-to-bottom like a recipe.

Clear the editor and paste this CTE version of the "users above average" query:

-- CTE version: users who listened to more tracks than average
WITH user_listen_counts AS (
    SELECT
        user_id,
        COUNT(listen_id) AS listen_count
    FROM listening_history
    GROUP BY user_id
),
average_listens AS (
    SELECT AVG(listen_count) AS avg_count
    FROM user_listen_counts
)
SELECT
    u.username,
    ulc.listen_count
FROM user_listen_counts ulc
JOIN users u ON ulc.user_id = u.user_id
CROSS JOIN average_listens al
WHERE ulc.listen_count > al.avg_count
ORDER BY ulc.listen_count DESC;

What does this query do?

This produces the same results as the nested subquery, but each step is named and visible.

user_listen_counts calculates each user's total listens.
average_listens computes the average from the first CTE's results.
The final SELECT joins both CTEs and filters users above that average.

Why CTEs over subqueries?

CTEs are easier to debug because you can run each named block independently. They also let you reference the same intermediate result multiple times without recomputing it. In interviews, CTEs signal that you write production-quality SQL.

Click Run.
You should see the same usernames and counts as the subquery version, now sorted in descending order.

Getting an error about the WITH keyword?

Make sure you don't have leftover SQL above the WITH clause. CTEs must start at the beginning of the statement. Clear everything in the editor before pasting.

Help me fix my CTE query.

Next, you'll chain multiple CTEs together to answer a more complex question: which artists perform above average?

Clear the editor and paste this multi-CTE query:

-- Multi-CTE: artists performing above average
WITH artist_listens AS (
    SELECT
        a.artist_id,
        a.name AS artist_name,
        COUNT(lh.listen_id) AS total_listens
    FROM artists a
    JOIN albums al ON a.artist_id = al.artist_id
    JOIN tracks t ON al.album_id = t.album_id
    JOIN listening_history lh ON t.track_id = lh.track_id
    GROUP BY a.artist_id, a.name
),
avg_artist_listens AS (
    SELECT AVG(total_listens) AS avg_listens
    FROM artist_listens
)
SELECT
    al.artist_name,
    al.total_listens,
    ROUND(aal.avg_listens, 1) AS average_across_artists
FROM artist_listens al
CROSS JOIN avg_artist_listens aal
WHERE al.total_listens > aal.avg_listens
ORDER BY al.total_listens DESC;

What does this query do?

artist_listens aggregates total listens per artist using the same multi-table JOIN pattern from Step 3.
avg_artist_listens calculates the average across all artists from the first CTE.
The final SELECT uses CROSS JOIN to bring the single-row average alongside every artist, then filters to only those above it.

Click Run.
You should see a short list of artists whose listen count exceeds the average, with the average value displayed for comparison.

Seeing zero rows?

Double-check that the WHERE clause compares al.total_listens > aal.avg_listens. If you accidentally used < instead of >, you'll get artists below average instead.

Help me debug my multi-CTE query.

Combine CTEs with JOINs

CTEs become especially powerful when you use them to compute a summary, then JOIN that summary back to a detail table. This pattern lets you enrich aggregated metrics with profile information in a single query.

Clear the editor and paste this CTE + JOIN query that combines user listening stats with their profile data:

-- CTE + JOIN: user listening stats combined with profile info
WITH user_stats AS (
    SELECT
        lh.user_id,
        COUNT(lh.listen_id) AS total_listens,
        COUNT(DISTINCT a.artist_id) AS unique_artists,
        COUNT(DISTINCT a.genre) AS unique_genres
    FROM listening_history lh
    JOIN tracks t ON lh.track_id = t.track_id
    JOIN albums al ON t.album_id = al.album_id
    JOIN artists a ON al.artist_id = a.artist_id
    GROUP BY lh.user_id
)
SELECT
    u.username,
    u.plan_type,
    u.signup_date,
    us.total_listens,
    us.unique_artists,
    us.unique_genres
FROM users u
JOIN user_stats us ON u.user_id = us.user_id
ORDER BY us.total_listens DESC;

What does this query do?

The user_stats CTE calculates three metrics per user: total listens, how many distinct artists they explored, and how many genres they sampled.

The final SELECT joins this summary back to the users table to pull in plan_type and signup_date. This is a common analytics pattern for building user dashboards or segmentation reports.

Click Run.
You should see all 6 users with their plan type, signup date, total listens, unique artists count, and unique genres count.

Missing some users in the results?

Make sure you're using JOIN (not LEFT JOIN) in both the CTE and the final SELECT. Since all 6 users have listening history, a regular JOIN returns all of them. If you see fewer than 6 rows, check that lh.user_id in the GROUP BY matches the join condition.

Help me debug my CTE + JOIN query.

✔️ Awesome, I've got everything!

Great. Make sure all five queries are saved together in your notes or a file for reference.

ⓧ I'd like to double check the full code

Here is the complete 04_subqueries_and_ctes.sql file with all five queries:

-- Subquery: users who listened to more tracks than average
SELECT
    u.username,
    COUNT(lh.listen_id) AS listen_count
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
GROUP BY u.username
HAVING COUNT(lh.listen_id) > (
    SELECT AVG(user_listens)
    FROM (
        SELECT COUNT(listen_id) AS user_listens
        FROM listening_history
        GROUP BY user_id
    )
);

-- Correlated subquery: each artist's most recent album
SELECT
    a.name AS artist_name,
    al.title AS album_title,
    al.release_year
FROM artists a
JOIN albums al ON a.artist_id = al.artist_id
WHERE al.release_year = (
    SELECT MAX(al2.release_year)
    FROM albums al2
    WHERE al2.artist_id = a.artist_id
);

-- CTE version: users who listened to more tracks than average
WITH user_listen_counts AS (
    SELECT
        user_id,
        COUNT(listen_id) AS listen_count
    FROM listening_history
    GROUP BY user_id
),
average_listens AS (
    SELECT AVG(listen_count) AS avg_count
    FROM user_listen_counts
)
SELECT
    u.username,
    ulc.listen_count
FROM user_listen_counts ulc
JOIN users u ON ulc.user_id = u.user_id
CROSS JOIN average_listens al
WHERE ulc.listen_count > al.avg_count
ORDER BY ulc.listen_count DESC;

-- Multi-CTE: artists performing above average
WITH artist_listens AS (
    SELECT
        a.artist_id,
        a.name AS artist_name,
        COUNT(lh.listen_id) AS total_listens
    FROM artists a
    JOIN albums al ON a.artist_id = al.artist_id
    JOIN tracks t ON al.album_id = t.album_id
    JOIN listening_history lh ON t.track_id = lh.track_id
    GROUP BY a.artist_id, a.name
),
avg_artist_listens AS (
    SELECT AVG(total_listens) AS avg_listens
    FROM artist_listens
)
SELECT
    al.artist_name,
    al.total_listens,
    ROUND(aal.avg_listens, 1) AS average_across_artists
FROM artist_listens al
CROSS JOIN avg_artist_listens aal
WHERE al.total_listens > aal.avg_listens
ORDER BY al.total_listens DESC;

-- CTE + JOIN: user listening stats combined with profile info
WITH user_stats AS (
    SELECT
        lh.user_id,
        COUNT(lh.listen_id) AS total_listens,
        COUNT(DISTINCT a.artist_id) AS unique_artists,
        COUNT(DISTINCT a.genre) AS unique_genres
    FROM listening_history lh
    JOIN tracks t ON lh.track_id = t.track_id
    JOIN albums al ON t.album_id = al.album_id
    JOIN artists a ON al.artist_id = a.artist_id
    GROUP BY lh.user_id
)
SELECT
    u.username,
    u.plan_type,
    u.signup_date,
    us.total_listens,
    us.unique_artists,
    us.unique_genres
FROM users u
JOIN user_stats us ON u.user_id = us.user_id
ORDER BY us.total_listens DESC;

You've now mastered two essential patterns for structuring complex analytical logic. Next up, you'll tackle window functions to rank, compare, and accumulate data without collapsing rows.

Master Window Functions

Your CTEs and subqueries can answer complex analytical questions with clean, readable SQL. Now it's time to tackle the skill that separates junior analysts from senior ones: window functions.

Window functions let you perform calculations across rows related to the current row without collapsing the result set. Think of ranking artists within each genre, comparing this month's listens to last month's, or showing a cumulative total that grows row by row. These are the exact patterns data analyst interviews test most frequently.

In this step, get ready to:

Rank artists within each genre using ROW_NUMBER, RANK, and DENSE_RANK.
Compare month-over-month listening activity using LAG.
Calculate running totals that accumulate per user over time.

Rank artists within each genre

A common business question is "who's the top artist in each genre?" Regular GROUP BY gives you totals, but it can't assign a rank within each category. Window functions solve this by computing a value across a set of rows (the "window") without merging those rows into one.

Clear the Execute SQL editor in DB Browser for SQLite.
Paste and run this query to rank artists within each genre by popularity:

-- ROW_NUMBER: rank artists within each genre by popularity
WITH artist_listens AS (
    SELECT
        a.artist_id,
        a.name AS artist_name,
        a.genre,
        COUNT(lh.listen_id) AS listen_count
    FROM artists a
    JOIN albums al ON a.artist_id = al.artist_id
    JOIN tracks t ON al.album_id = t.album_id
    JOIN listening_history lh ON t.track_id = lh.track_id
    GROUP BY a.artist_id, a.name, a.genre
)
SELECT
    genre,
    artist_name,
    listen_count,
    ROW_NUMBER() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS row_num,
    RANK() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS dense_rank_val
FROM artist_listens
ORDER BY genre, row_num;

What does this query do?

The CTE artist_listens first calculates each artist's total play count using a multi-table JOIN and GROUP BY.
PARTITION BY genre tells the window function to restart numbering for each genre. It's like running a separate ranking within each genre.
ORDER BY listen_count DESC determines the ranking order within each partition.
ROW_NUMBER() always assigns unique sequential numbers (1, 2, 3). RANK() assigns the same number to ties but skips the next value (1, 1, 3). DENSE_RANK() assigns the same number to ties without skipping (1, 1, 2).

You should see results grouped by genre, with each artist ranked by their listen count. Look at how the three ranking columns differ when artists have the same number of listens.

Seeing an error about window functions?

Make sure you included the full CTE (the WITH clause) before the SELECT. The window functions reference listen_count which only exists inside the CTE's output.

help me debug my ranking window function query

Compare month-over-month with LAG

Ranking tells you who's on top right now. But stakeholders also want to know if engagement is growing or shrinking. The LAG function lets you peek at the previous row's value so you can calculate the difference between two time periods in a single query.

Clear the SQL editor.
Paste and run this query to calculate month-over-month listening changes per user:

-- LAG: month-over-month listening comparison per user
WITH monthly_listens AS (
    SELECT
        u.username,
        strftime('%Y-%m', lh.listened_at) AS listen_month,
        COUNT(lh.listen_id) AS monthly_count
    FROM users u
    JOIN listening_history lh ON u.user_id = lh.user_id
    GROUP BY u.username, strftime('%Y-%m', lh.listened_at)
)
SELECT
    username,
    listen_month,
    monthly_count,
    LAG(monthly_count) OVER (PARTITION BY username ORDER BY listen_month) AS prev_month_count,
    monthly_count - LAG(monthly_count) OVER (PARTITION BY username ORDER BY listen_month) AS month_change
FROM monthly_listens
ORDER BY username, listen_month;

What does this query do?

The CTE uses strftime('%Y-%m', lh.listened_at) to extract the year-month from each listen timestamp. This is SQLite's equivalent of date truncation.
LAG(monthly_count) OVER (PARTITION BY username ORDER BY listen_month) looks at the previous row's monthly_count for the same user. The first month for each user returns NULL because there is no previous row.
The month_change column subtracts the previous month from the current month. A positive number means growth. A negative number means a drop in listens.

Each row now shows the current month's listens, the previous month's listens, and the difference. The first month for each user shows NULL in the previous and change columns because there is nothing to compare against.

All prev_month_count values are NULL?

This happens if the PARTITION BY or ORDER BY is incorrect. Make sure PARTITION BY username groups rows per user and ORDER BY listen_month sorts them chronologically. If every user only has one row, check that the CTE's GROUP BY includes the strftime expression.

help me fix my LAG window function

Calculate running totals per user

Running totals show how a metric accumulates over time. For a streaming service, this answers questions like "how quickly is this user's engagement growing?" The pattern uses SUM with a window frame that expands row by row.

Clear the SQL editor.
Paste and run this query to calculate a cumulative listen count per user:

-- Running total: cumulative listens over time per user
SELECT
    u.username,
    lh.listened_at,
    t.title AS track_title,
    ROW_NUMBER() OVER (PARTITION BY u.username ORDER BY lh.listened_at) AS listen_number,
    SUM(1) OVER (PARTITION BY u.username ORDER BY lh.listened_at) AS running_total
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
ORDER BY u.username, lh.listened_at;

What does this query do?

SUM(1) OVER (PARTITION BY u.username ORDER BY lh.listened_at) adds 1 for each row in the user's partition, ordered by time. This creates a running count that grows with each listen.
ROW_NUMBER() appears alongside to give each listen a sequential number. Both columns should show identical values since each row represents exactly one listen event.
The running total resets for each user because of PARTITION BY u.username. Without the partition, the total would accumulate across all users.

Scroll through the results. You should see numbers increasing monotonically (1, 2, 3, 4...) within each user's rows, then resetting back to 1 when the next user begins.

Running total doesn't reset between users?

If the numbers keep climbing without resetting, check that your PARTITION BY u.username clause is present in both the SUM and ROW_NUMBER window definitions. Without PARTITION BY, the window spans all rows in the result set.

help me fix my running total query

Why are window functions so important for interviews?

Window functions are the number one skill that self-taught SQL learners skip. They're also a standard interview question for data analyst roles. The three patterns you just wrote (ranking, period comparison, running totals) cover the most common interview scenarios.

The key mental model: GROUP BY collapses rows into one summary row per group. Window functions keep every row intact and add a calculated column beside it. That's why your running total query returns one row per listen event instead of one row per user.

✔️ Awesome, I've got everything!

Great. You now have three window function queries covering ranking, period comparison, and running totals.

ⓧ I'd like to double check the full code

Here is the complete 05_window_functions.sql with all three queries together:

-- ROW_NUMBER: rank artists within each genre by popularity
WITH artist_listens AS (
    SELECT
        a.artist_id,
        a.name AS artist_name,
        a.genre,
        COUNT(lh.listen_id) AS listen_count
    FROM artists a
    JOIN albums al ON a.artist_id = al.artist_id
    JOIN tracks t ON al.album_id = t.album_id
    JOIN listening_history lh ON t.track_id = lh.track_id
    GROUP BY a.artist_id, a.name, a.genre
)
SELECT
    genre,
    artist_name,
    listen_count,
    ROW_NUMBER() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS row_num,
    RANK() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY genre ORDER BY listen_count DESC) AS dense_rank_val
FROM artist_listens
ORDER BY genre, row_num;

-- LAG: month-over-month listening comparison per user
WITH monthly_listens AS (
    SELECT
        u.username,
        strftime('%Y-%m', lh.listened_at) AS listen_month,
        COUNT(lh.listen_id) AS monthly_count
    FROM users u
    JOIN listening_history lh ON u.user_id = lh.user_id
    GROUP BY u.username, strftime('%Y-%m', lh.listened_at)
)
SELECT
    username,
    listen_month,
    monthly_count,
    LAG(monthly_count) OVER (PARTITION BY username ORDER BY listen_month) AS prev_month_count,
    monthly_count - LAG(monthly_count) OVER (PARTITION BY username ORDER BY listen_month) AS month_change
FROM monthly_listens
ORDER BY username, listen_month;

-- Running total: cumulative listens over time per user
SELECT
    u.username,
    lh.listened_at,
    t.title AS track_title,
    ROW_NUMBER() OVER (PARTITION BY u.username ORDER BY lh.listened_at) AS listen_number,
    SUM(1) OVER (PARTITION BY u.username ORDER BY lh.listened_at) AS running_total
FROM users u
JOIN listening_history lh ON u.user_id = lh.user_id
JOIN tracks t ON lh.track_id = t.track_id
ORDER BY u.username, lh.listened_at;

Secret mission

Cohort Retention Analysis

Ready for a real interview-grade challenge? Build a cohort retention analysis that groups users by their signup month, then tracks what percentage of each cohort is still actively listening in subsequent months. This combines CTEs, window-style logic, and date manipulation into one multi-layered query.

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your resources on your machine or delete them entirely.

Resources you used:

streaming_analytics.db file on your local machine.
DB Browser for SQLite application.

Keep everything running

No action needed. Choose this if you plan to keep practising SQL or extending your database with new queries.

The streaming_analytics.db file stays on your machine indefinitely at no cost.
You can reopen it anytime in DB Browser for SQLite to run new queries or add more data.

Pause - I'll come back to this later

Close DB Browser for SQLite to free up memory. Your database file remains on disk for whenever you want to return.

In DB Browser for SQLite, click File then Close Database.
Quit DB Browser for SQLite.
Your streaming_analytics.db file stays on your machine at no cost until you reopen it.

Delete - I don't want to use this again

Remove all project resources and the application from your machine.

Close DB Browser for SQLite if it is still open.
Delete the streaming_analytics.db file from your Desktop (or wherever you saved it). You can drag it to the Trash in Finder, or run this in Terminal:

rm ~/Desktop/streaming_analytics.db

Uninstall DB Browser for SQLite by opening Finder, navigating to Applications, and dragging DB Browser for SQLite to the Trash.
Empty the Trash to reclaim disk space.

Nice Work!

Nice work! You've just built a complete music streaming analytics database and written a library of advanced SQL queries that cover the exact patterns data analyst interviewers look for.

You've learned how to:

Build a 5-table relational database with proper foreign key relationships modelling a music streaming service.
Write multi-table JOINs with GROUP BY, HAVING, and CASE to answer business questions about artist popularity and user engagement.
Use window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, running totals) and CTEs for ranking, comparison, and cumulative analysis.
Secret Mission: Built a cohort retention analysis combining CTEs, window functions, and date logic into a single production-grade analytical query.

Ready to quiz yourself?