Music Streaming Analytics with MySQL

Design a 5-table relational database and write analytical SQL queries.

Introduction

30 Second Summary

Music streaming services track billions of plays every day. Behind every "Top Artists" list and personalized recommendation is a database that connects listeners, tracks, and artists through carefully designed relationships.

In this project, you will design and build a relational database from scratch using MySQL, populate it with sample music streaming data, and write increasingly powerful queries to extract insights like top artists, power users, and listening patterns.

What You'll Build

You will have a working database where you can type a single query and instantly see which artists are dominating your streaming service, which users are the most active listeners, and which artists share fans in common.

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

  • A multi-table JOIN query that pulls together usernames, track names, and artist names from five separate tables into one readable result.
  • An aggregate leaderboard that ranks artists by total listen time and filters out anyone below a threshold using GROUP BY and HAVING.
  • Reusable views that package your most complex analytics into simple one-line SELECT statements anyone on your team could run.
  • Secret Mission: Build a "similar artists" recommendation that finds artists who share listeners using a self-join pattern.

Are there any prerequisites?

This project assumes basic familiarity with SELECT statements. If you have written a simple query like SELECT * FROM table_name before, you have everything you need to get started.

Before We Start

Before we dive into SQL, let's lock in what you're about to build and why it matters.

Install MySQL and Create Your Database

Your music streaming analytics database needs a database engine to store tables and run queries against them. MySQL is one of the most widely used relational databases in the world, and it runs locally on your Mac with zero cloud costs.

In this step, you will install MySQL using Homebrew, start the server, and create the database that will hold all of your streaming data.

In this step, get ready to:
  • Install MySQL and confirm the server is running.
  • Create the music_streaming database.
  • Verify the database appears in your server's database list.
Install MySQL via Homebrew and start the server

Before installing anything, check whether MySQL is already on your machine.

  • Check if MySQL is already installed by running this command:
mysql --version

✔️ I see version 9.7 or higher

MySQL is already installed and ready to go. Skip ahead to starting the server below.

ⓧ I see an older version

You need MySQL 9.7 for this project. Upgrade your Homebrew MySQL installation.

  • Upgrade MySQL by running:
brew upgrade mysql
  • Once the upgrade finishes, continue to the next section to start the server.

ⓧ Command not found

MySQL is not installed yet. Install it with Homebrew.

  • Install MySQL by running:
brew install mysql

What does this command do?

Homebrew downloads and installs MySQL 9.7 (the current stable version) along with all its dependencies. The install takes a minute or two depending on your internet connection.

Seeing a conflict error?

Homebrew's mysql formula conflicts with mariadb and percona-server. If you have either installed, uninstall it first, then retry brew install mysql.

Need more help? Help me resolve a Homebrew conflict when installing MySQL

  • Start the MySQL server by running this command:
brew services start mysql

What does this command do?

This tells Homebrew to start MySQL as a background service. The server will keep running until you stop it or restart your Mac.

Homebrew also configures MySQL to accept connections from root without a password on localhost. This is fine for a local learning project.

  • Verify MySQL is running and check its version by running:
mysql -u root -e "SELECT VERSION();"

You should see 9.7.1 (or a similar 9.7.x number) printed in your terminal.

Seeing 'Can't connect to local MySQL server'?

The server may not have finished starting yet. Wait 5 seconds and try the command again.

If it still fails, check the service status by running brew services list and confirm mysql shows as started.

Still stuck? Help me troubleshoot my MySQL connection

Create the streaming database

Now that MySQL is running, you need a dedicated database to hold your streaming tables. You will write this as a SQL file so you can re-run it anytime.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir music-streaming
cd music-streaming
  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code (or Cursor) and press Enter to open it.
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the music-streaming folder, then click Open.
  • Create a new file by clicking File then New File.
  • Name the file streaming_setup.sql and press Enter to save it.
  • Paste the following SQL into streaming_setup.sql:
CREATE DATABASE IF NOT EXISTS music_streaming;
USE music_streaming;

What does this code do?

  • CREATE DATABASE IF NOT EXISTS music_streaming; tells MySQL to create a new database called music_streaming. The IF NOT EXISTS part prevents an error if the database already exists.
  • USE music_streaming; switches the active database so that any tables you create next go into this database.
  • Save the file with Cmd+S.
  • Switch back to your terminal and run the file against MySQL:
mysql -u root < streaming_setup.sql

If the command succeeds, it returns to the prompt with no output. That silence means MySQL processed both statements without errors.

Before you verify, what do you expect to see when you list all databases on the server?

  • Confirm your database was created by running:
mysql -u root -e "SHOW DATABASES;"

You should see music_streaming in the list alongside MySQL's built-in databases like information_schema and sys.

Don't see music_streaming in the list?

Make sure your terminal is inside the music-streaming folder on your Desktop. The < streaming_setup.sql part of the command reads the file from whatever directory you are in.

Run ls to confirm you see streaming_setup.sql listed. If not, cd into the correct folder and try again.

Still stuck? Help me figure out why my database wasn't created

✔️ Awesome, I've got everything!

Great. Double check you have saved streaming_setup.sql and confirmed music_streaming appears in your database list.

ⓧ I'd like to double check the full code

Here is the complete streaming_setup.sql file. Make sure yours matches exactly:

CREATE DATABASE IF NOT EXISTS music_streaming;
USE music_streaming;

Your MySQL server is running and your database is ready. Next up, you will design the schema and build five interconnected tables inside it.

Design and Build the Schema

Your MySQL server is running and the music_streaming database is ready. Now it's time to give that empty database some structure.

A well-designed relational schema is the foundation of every database project. You need tables with proper relationships before you can store or query any data.

In this step, get ready to:
  • Create entity tables for artists, albums, and tracks with foreign key relationships.
  • Add user and listening history tables to model who listened to what.
  • Verify your schema's structure and constraints using information_schema.
Create the core entity tables

A music streaming service has a natural hierarchy: artists release albums, and albums contain tracks. You'll model each of these as its own table, with foreign keys linking them together.

  • Switch back to VS Code.
  • Create a new file called schema.sql in the same folder as streaming_setup.sql.
  • Add the USE statement and the first table definition by pasting this code:
USE music_streaming;

CREATE TABLE IF NOT EXISTS artists (
    artist_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    genre VARCHAR(50) NOT NULL,
    PRIMARY KEY (artist_id)
) ENGINE=INNODB;

What does this code do?

  • USE music_streaming; tells MySQL which database to work in.
  • CREATE TABLE IF NOT EXISTS creates the table only if it doesn't already exist. This means you can safely re-run the file without errors.
  • AUTO_INCREMENT makes MySQL generate a unique ID for each new row automatically.
  • PRIMARY KEY (artist_id) marks the column that uniquely identifies each artist.
  • ENGINE=INNODB enables foreign key support, which you'll use in the next tables.
  • Save schema.sql.
  • Run the file against your database with this command in Terminal:
mysql -u root music_streaming < schema.sql
  • Verify the table was created by running:
mysql -u root music_streaming -e "SHOW TABLES;"

You should see artists listed in the output.

Seeing an error?

  • If you see Unknown database, make sure you ran streaming_setup.sql in Step 1 to create the music_streaming database.
  • If you get a file-not-found error, make sure your Terminal is in the same folder where schema.sql is saved.

Need more help?

Now you'll add the albums table. This is where foreign keys come in. Each album belongs to one artist, so the table needs a column that points back to the artists table.

  • In schema.sql, add the following code below the artists table definition:
CREATE TABLE IF NOT EXISTS albums (
    album_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    artist_id INT NOT NULL,
    release_year INT NOT NULL,
    PRIMARY KEY (album_id),
    FOREIGN KEY (artist_id)
        REFERENCES artists(artist_id)
        ON DELETE CASCADE
) ENGINE=INNODB;

What is a foreign key?

A foreign key is a column that references the primary key of another table. Here, artist_id in albums must match an existing artist_id in artists. MySQL enforces this. You cannot insert an album that references a non-existent artist.

ON DELETE CASCADE means if you delete an artist, all their albums are automatically deleted too. This keeps your data consistent without manual cleanup.

  • Below the albums table, add the tracks table:
CREATE TABLE IF NOT EXISTS tracks (
    track_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(150) NOT NULL,
    album_id INT NOT NULL,
    duration_seconds INT NOT NULL,
    PRIMARY KEY (track_id),
    FOREIGN KEY (album_id)
        REFERENCES albums(album_id)
        ON DELETE CASCADE
) ENGINE=INNODB;

What does this code do?

  • The tracks table follows the same foreign key pattern as albums. Each track belongs to one album via album_id.
  • duration_seconds stores each track's length as an integer. You'll use this later to calculate total listen time.
  • This creates a chain: artists own albums, and albums own tracks. Deleting an artist cascades through both levels.
  • Save schema.sql.
  • Run the file again in Terminal:
mysql -u root music_streaming < schema.sql
  • Verify all three tables exist by running:
mysql -u root music_streaming -e "SHOW TABLES;"

You should see albums, artists, and tracks listed.

Seeing a foreign key error?

If MySQL reports a foreign key constraint error, check that your tables are defined in the correct order. The artists table must come before albums, and albums must come before tracks. A foreign key can only reference a table that already exists.

Help me debug this.

Create the user and relationship tables

The three entity tables model your music catalog. Now you need to model the people using the service and what they listen to.

  • In schema.sql, add the users table below the tracks definition:
CREATE TABLE IF NOT EXISTS users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    joined_date DATE NOT NULL,
    PRIMARY KEY (user_id)
) ENGINE=INNODB;

What does UNIQUE do here?

The UNIQUE constraint on username ensures no two users can have the same name. MySQL will reject any INSERT that tries to duplicate an existing username.

  • Below users, add the listening_history table:
CREATE TABLE IF NOT EXISTS listening_history (
    listen_id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    track_id INT NOT NULL,
    listened_at DATETIME NOT NULL,
    PRIMARY KEY (listen_id),
    FOREIGN KEY (user_id)
        REFERENCES users(user_id)
        ON DELETE CASCADE,
    FOREIGN KEY (track_id)
        REFERENCES tracks(track_id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Why is this table special?

The listening_history table is a junction table. It connects users to tracks through two foreign keys. One user can listen to many tracks, and one track can be listened to by many users. This is called a many-to-many relationship.

The listened_at timestamp records when each play happened, turning this from a simple link into rich event data.

  • Save schema.sql.
  • Run the file one more time:
mysql -u root music_streaming < schema.sql
  • Verify all five tables exist:
mysql -u root music_streaming -e "SHOW TABLES;"

You should now see albums, artists, listening_history, tracks, and users listed (MySQL shows them alphabetically).

Missing tables in the output?

  • Make sure you saved schema.sql before running the command. Unsaved changes won't be picked up.
  • Check that all five CREATE TABLE statements are in schema.sql in the correct order: artists, albums, tracks, users, listening_history.

Help me figure out why my tables aren't showing up.

Inspect your schema

Your tables exist, but let's confirm the column types and constraints are correct. MySQL provides commands to inspect the internal structure of any table.

  • Inspect the tracks table structure by running:
mysql -u root music_streaming -e "DESCRIBE tracks;"

You should see each column listed with its type, whether it allows NULL, its key status, and default value.

  • Inspect the listening_history table:
mysql -u root music_streaming -e "DESCRIBE listening_history;"

You should see user_id and track_id both marked as MUL in the Key column. This confirms they are foreign keys.

Before you run the next command, do you think it will show foreign keys for all five tables, or only some of them? Think about which tables actually reference other tables.

  • Verify all foreign key relationships at once by querying the information_schema:
mysql -u root music_streaming -e "SELECT TABLE_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'music_streaming' AND REFERENCED_TABLE_NAME IS NOT NULL;"

You should see four rows: albums referencing artists, tracks referencing albums, and listening_history referencing both users and tracks. Only tables with FOREIGN KEY declarations appear here.

What is information_schema?

The information_schema is a built-in MySQL database that stores metadata about all your databases, tables, columns, and constraints. Querying it is the programmatic way to inspect your schema without clicking through a GUI.

✔️ Awesome, I've got everything!

Great. Double check you've saved schema.sql and move on to the next step.

ⓧ I'd like to double check the full code

Your schema.sql should contain these blocks in order. Scroll up and confirm each one is present:

  • USE music_streaming; at the very top.
  • CREATE TABLE IF NOT EXISTS artists with artist_id, name, and genre columns.
  • CREATE TABLE IF NOT EXISTS albums with a FOREIGN KEY (artist_id) REFERENCES artists(artist_id).
  • CREATE TABLE IF NOT EXISTS tracks with a FOREIGN KEY (album_id) REFERENCES albums(album_id).
  • CREATE TABLE IF NOT EXISTS users with a UNIQUE constraint on username.
  • CREATE TABLE IF NOT EXISTS listening_history with two foreign keys: one to users and one to tracks.

Your schema is locked in. Five tables, properly connected with foreign keys that enforce data integrity. Next up, you'll fill these tables with realistic sample data so your queries have something to work with.

Populate with Sample Data

Your schema is built, but the tables are empty. Queries against empty tables return nothing useful.

Realistic sample data lets you see patterns, test edge cases, and verify that your queries return correct results. In this step, you will populate all five tables with data that represents a small streaming service.

In this step, get ready to:
  • Insert artists, albums, and tracks with realistic names and durations.
  • Insert users and listening history records spread across multiple days.
Insert artists, albums, and tracks

Your three core entity tables form a hierarchy: artists own albums, albums contain tracks. You need to insert them in this order because each table references the one above it via foreign keys.

  • In your editor, create a new file called seed_data.sql in the same folder as your other SQL files.
  • Add the database selector, artist records, and album records by pasting this code:
USE music_streaming;

-- Artists
INSERT INTO artists (name, genre) VALUES
('Aurora Wave', 'Electronic'),
('The Rust Collective', 'Rock'),
('Jade Thompson', 'R&B'),
('Pine & Maple', 'Folk'),
('Neon Drift', 'Electronic');

-- Albums
INSERT INTO albums (title, artist_id, release_year) VALUES
('Synthetic Sunrise', 1, 2022),
('Pulse Echoes', 1, 2024),
('Iron Threads', 2, 2021),
('Concrete Garden', 2, 2023),
('Velvet Hours', 3, 2023),
('Midnight Letters', 3, 2024),
('Timber Lines', 4, 2022),
('Quiet Roads', 4, 2024),
('Circuit Bloom', 5, 2023),
('Afterglow', 5, 2025);

What does this code do?

USE music_streaming; tells MySQL which database to run the INSERTs against.

The artists INSERT creates 5 artists across 3 genres (Electronic, Rock, R&B, Folk). The albums INSERT references those artists by their auto-generated IDs (1 through 5), giving each artist exactly 2 albums.

  • Below the albums INSERT, add the tracks for all 10 albums by pasting this code:
-- Tracks
INSERT INTO tracks (title, album_id, duration_seconds) VALUES
('Morning Code', 1, 245),
('Wavelength', 1, 198),
('Digital Rain', 1, 272),
('Echo Chamber', 2, 215),
('Neon Pulse', 2, 189),
('Rust and Bone', 3, 264),
('Heavy Wire', 3, 231),
('Grinding Gears', 3, 298),
('Bloom in Grey', 4, 212),
('Shattered Glass', 4, 247),
('Velvet Touch', 5, 236),
('Slow Burn', 5, 284),
('After Midnight', 6, 195),
('Golden Thread', 6, 261),
('Cedar Song', 7, 223),
('Mountain Pass', 7, 198),
('Still Water', 8, 245),
('Homeward', 8, 267),
('Spark', 9, 201),
('Overdrive', 9, 278),
('Fading Light', 10, 242),
('Last Signal', 10, 219);

What does this code do?

This inserts 22 tracks spread across the 10 albums. Each track has a duration_seconds value between 189 and 298, which represents realistic song lengths (about 3 to 5 minutes).

The album_id values (1 through 10) match the albums you just inserted. MySQL enforces this through the foreign key constraint. If you tried to reference a non-existent album, the INSERT would fail.

  • Save seed_data.sql.
  • Run the file to insert artists, albums, and tracks into your database:
mysql -u root music_streaming < seed_data.sql
  • Verify the tracks made it in by running:
mysql -u root music_streaming -e "SELECT COUNT(*) FROM tracks;"

You should see 22 in the output.

Seeing an error about a foreign key constraint?

This means a track references an album_id that does not exist. Make sure the artists INSERT comes before albums, and albums before tracks. MySQL inserts them in order, so the parent rows must exist first.

If you need to start fresh, run mysql -u root -e "DROP DATABASE music_streaming;" then re-run streaming_setup.sql and schema.sql before running seed_data.sql again.

Help me debug my foreign key constraint error in seed_data.sql

Insert users and listening history

The listening_history table is the heart of your analytics. It records every time a user plays a track, creating the many-to-many relationship between users and tracks that powers all your future queries.

  • Return to seed_data.sql in your editor.
  • Below the tracks INSERT, add the users by pasting this code:
-- Users
INSERT INTO users (username, joined_date) VALUES
('alex_listens', '2023-01-15'),
('beat_seeker', '2023-03-22'),
('chloe_m', '2023-06-01'),
('dave_waves', '2024-01-10'),
('emma_tunes', '2024-04-05');

What does this code do?

This creates 5 users with different joined_date values spanning 2023 and 2024. The staggered join dates will let you write queries later that filter by when users signed up.

  • Below the users INSERT, add the listening history records by pasting this code:
-- Listening History
INSERT INTO listening_history (user_id, track_id, listened_at) VALUES
(1, 1, '2025-06-01 08:30:00'),
(1, 2, '2025-06-01 08:34:00'),
(1, 6, '2025-06-01 09:00:00'),
(1, 11, '2025-06-02 20:15:00'),
(1, 15, '2025-06-03 07:45:00'),
(1, 19, '2025-06-03 08:10:00'),
(2, 1, '2025-06-01 10:00:00'),
(2, 4, '2025-06-01 10:05:00'),
(2, 5, '2025-06-02 14:30:00'),
(2, 19, '2025-06-02 14:35:00'),
(2, 20, '2025-06-03 16:00:00'),
(2, 21, '2025-06-03 16:05:00'),
(3, 11, '2025-06-01 22:00:00'),
(3, 12, '2025-06-01 22:04:00'),
(3, 13, '2025-06-02 21:30:00'),
(3, 14, '2025-06-02 21:34:00'),
(3, 6, '2025-06-03 19:00:00'),
(4, 15, '2025-06-01 06:00:00'),
(4, 16, '2025-06-01 06:04:00'),
(4, 17, '2025-06-02 07:00:00'),
(4, 18, '2025-06-02 07:05:00'),
(4, 1, '2025-06-03 08:00:00'),
(4, 3, '2025-06-03 08:05:00'),
(5, 6, '2025-06-01 12:00:00'),
(5, 7, '2025-06-01 12:05:00'),
(5, 8, '2025-06-02 13:00:00'),
(5, 9, '2025-06-02 13:05:00'),
(5, 1, '2025-06-03 15:00:00'),
(5, 19, '2025-06-03 15:05:00');

What does this code do?

This inserts 29 play events spread across all 5 users over three days in June 2025. Each row logs which user played which track and exactly when.

Notice that some tracks appear multiple times (different users playing the same song). This is what makes the data interesting for analytics. You will be able to ask questions like "which tracks are most popular?" and "which users share listening habits?"

  • Save seed_data.sql.
  • You already inserted artists, albums, and tracks earlier. To avoid duplicate rows, drop and rebuild the database before running the complete file. Run these commands:
mysql -u root -e "DROP DATABASE music_streaming;"
mysql -u root < streaming_setup.sql
mysql -u root music_streaming < schema.sql
mysql -u root music_streaming < seed_data.sql

Why drop and rebuild?

Your seed_data.sql now includes ALL the INSERT statements (artists through listening_history). Running it on top of the data you already inserted would create duplicate rows. Dropping and recreating ensures a clean slate with exactly the data you expect.

Before you verify, predict: how many rows do you expect in listening_history?

  • Verify your row counts across all tables by running:
mysql -u root music_streaming -e "SELECT 'artists' AS tbl, COUNT(*) AS cnt FROM artists UNION ALL SELECT 'tracks', COUNT(*) FROM tracks UNION ALL SELECT 'listening_history', COUNT(*) FROM listening_history;"

You should see 5 artists, 22 tracks, and 29 listening_history records.

What is UNION ALL?

UNION ALL stacks the results of multiple SELECT statements into one result set. Each SELECT must return the same number of columns. It is a quick way to check several tables at once without running separate commands.

Seeing 0 rows or an error?

If you see 0 rows, the INSERT statements did not run. Check that seed_data.sql starts with USE music_streaming; on the first line.

If you see a duplicate entry error, the database still had data from a previous run. Re-run the four commands above (DROP, setup, schema, seed) in order.

Help me fix my seed_data.sql INSERT errors

✔️ Awesome, I've got everything!

Great. Double check you have saved seed_data.sql and your row counts match: 5 artists, 22 tracks, 29 listening_history records.

ⓧ I'd like to double check the full code

Your seed_data.sql should contain these sections in order. Scroll up and confirm each one is present:

  • USE music_streaming; at the top.
  • Artists INSERT (5 rows: Aurora Wave, The Rust Collective, Jade Thompson, Pine & Maple, Neon Drift).
  • Albums INSERT (10 rows, 2 per artist, with release years).
  • Tracks INSERT (22 rows with durations between 189 and 298 seconds).
  • Users INSERT (5 rows: alex_listens, beat_seeker, chloe_m, dave_waves, emma_tunes).
  • Listening History INSERT (29 rows spanning June 1-3, 2025).

Your database is loaded with 5 artists, 10 albums, 22 tracks, 5 users, and 29 play events. Next up, you will write multi-table JOIN queries that reconnect this data and answer real questions about listening patterns.

Write Multi-Table JOIN Queries

Your database has 29 listening records, 22 tracks, 10 albums, 5 artists, and 5 users. Right now, all of that data lives in separate tables connected only by ID numbers.

If you query listening_history on its own, you see rows like user_id = 1, track_id = 6. That tells you nothing useful. JOINs are how you reconnect related tables to answer real questions like "what did alex_listens hear on June 1st?" or "which artist has the most total play time?"

In this step, get ready to:
  • Write a multi-table JOIN that turns IDs into readable names.
  • Build aggregate queries with GROUP BY and HAVING to rank artists.
  • Use a subquery to filter users based on computed values.
Write a basic JOIN to see what users listened to

Your first query will span all 5 tables at once: listening_history connects to users, tracks, albums, and artists. The result replaces cryptic IDs with human-readable names.

  • In VS Code or Cursor, create a new file called queries.sql in the same folder as your other SQL files.
  • Add this multi-table JOIN query:
USE music_streaming;

-- Query 1: What did each user listen to? (multi-table JOIN)
SELECT
    u.username,
    t.title AS track,
    ar.name AS artist,
    lh.listened_at
FROM listening_history lh
INNER JOIN users u ON lh.user_id = u.user_id
INNER JOIN tracks t ON lh.track_id = t.track_id
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
ORDER BY lh.listened_at;

What does this query do?

The query starts at listening_history (aliased as lh) and reaches outward through four INNER JOINs.

  • Each JOIN matches a foreign key in one table to the primary key in another (e.g. lh.user_id = u.user_id).
  • To get artist names, you must hop through tracks to albums to artists because tracks only know their album, not their artist directly.
  • The aliases (lh, u, t, al, ar) keep the query readable instead of repeating full table names.

Before you run this, what do you think the output will look like? Will you see IDs, or names?

  • Save queries.sql.
  • Run the file against your database:
mysql -u root music_streaming < queries.sql

You should see a table with 29 rows showing columns like alex_listens, Morning Code, Aurora Wave, and a timestamp. Every ID has been replaced with a readable name.

Seeing an empty result or error?

  • If you see ERROR 1049, check that the first line of your file is USE music_streaming; and that the database exists.
  • If you see 0 rows, confirm your seed data was inserted by running mysql -u root music_streaming -e "SELECT COUNT(*) FROM listening_history;" and expecting 29.
  • If a column name is wrong, double-check your JOIN conditions match exactly: lh.track_id = t.track_id, not lh.track_id = t.id.

Help me debug my multi-table JOIN query

Write an aggregate query with GROUP BY and HAVING

Now that you can see individual plays, the next question is: which artist has the most total listen time across all users? This requires grouping rows by artist and summing their track durations.

  • In queries.sql, add this aggregate query below your first one:
-- Query 2: Top artists by total listen time
SELECT
    ar.name AS artist,
    SUM(t.duration_seconds) AS total_listen_seconds
FROM listening_history lh
INNER JOIN tracks t ON lh.track_id = t.track_id
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
GROUP BY ar.artist_id, ar.name
ORDER BY total_listen_seconds DESC;

What does GROUP BY do here?

Without GROUP BY, MySQL would try to collapse all rows into one giant sum. GROUP BY ar.artist_id, ar.name tells MySQL to create separate groups for each artist, then apply SUM() within each group.

The result is one row per artist with their total play time in seconds, sorted highest first.

  • Save queries.sql and run it:
mysql -u root music_streaming < queries.sql

You now see two result sets. The second one is a ranked leaderboard with artist names and total seconds. The top artist should have the highest combined play time.

That leaderboard includes every artist, even those with just one play. What if you only want artists with significant play time? That is what HAVING does.

  • Add this filtered version below Query 2:
-- Query 3: Top artists with HAVING filter (more than 300 seconds)
SELECT
    ar.name AS artist,
    SUM(t.duration_seconds) AS total_listen_seconds
FROM listening_history lh
INNER JOIN tracks t ON lh.track_id = t.track_id
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
GROUP BY ar.artist_id, ar.name
HAVING total_listen_seconds > 300
ORDER BY total_listen_seconds DESC;

Why HAVING instead of WHERE?

WHERE filters individual rows before grouping happens. HAVING filters groups after the aggregation is complete. Since total_listen_seconds does not exist until after SUM() runs, you must use HAVING to filter on it.

  • Save and run the file again:
mysql -u root music_streaming < queries.sql

Compare the second and third result sets. Query 3 should show fewer artists than Query 2 because artists with 300 seconds or less of total play time are now excluded.

Write a query using a subquery

Your final query answers a different kind of question: which users have listened to tracks by more than 2 different artists? This requires computing a value per user (their distinct artist count) and then using that computation to filter the outer query. A subquery handles this by nesting one SELECT inside another.

  • Add this subquery below Query 3 in queries.sql:
-- Query 4: Users who listened to more than 2 different artists (subquery)
SELECT username
FROM users
WHERE user_id IN (
    SELECT lh.user_id
    FROM listening_history lh
    INNER JOIN tracks t ON lh.track_id = t.track_id
    INNER JOIN albums al ON t.album_id = al.album_id
    GROUP BY lh.user_id
    HAVING COUNT(DISTINCT al.artist_id) > 2
);

How does the subquery work?

  • The inner SELECT finds user IDs whose listening history spans more than 2 distinct artists. It joins listening_history through tracks to albums (which holds the artist_id), groups by user, and counts distinct artists.
  • The outer SELECT picks usernames from users whose user_id appears in that inner result set.
  • This pattern (outer query + WHERE ... IN (subquery)) lets you filter rows based on a computed condition from related tables.

Before you run this, think about your sample data. How many of your 5 users do you think listened to more than 2 different artists?

  • Save queries.sql and run it:
mysql -u root music_streaming < queries.sql

The fourth result set shows usernames of power listeners who explored more than 2 artists. Check whether the result matches your prediction by thinking back to the seed data you inserted.

Subquery returning no results?

  • Make sure the HAVING threshold is > 2 (more than 2), not >= 2. With your seed data, some users listened to exactly 2 artists.
  • Confirm your subquery joins through tracks to albums (where artist_id lives). Joining only to tracks misses the artist connection.

Help me debug my subquery

✔️ Awesome, I've got everything!

Great. Double check you have saved queries.sql with all four queries.

ⓧ I'd like to double check the full code

Your queries.sql should contain these sections in order. Scroll up and confirm each is present:

  • USE music_streaming; at the top.
  • Query 1: the 5-table JOIN selecting username, track, artist, and listened_at from listening_history joined to users, tracks, albums, and artists.
  • Query 2: the GROUP BY aggregate selecting artist name and SUM of duration_seconds, ordered descending.
  • Query 3: same as Query 2 but with HAVING total_listen_seconds > 300 added before the ORDER BY.
  • Query 4: the subquery selecting usernames from users WHERE user_id IN a subquery that groups by user and counts distinct artists > 2.

You now have four queries that demonstrate the core patterns employers look for: multi-table JOINs, GROUP BY with aggregate functions, HAVING for group-level filtering, and subqueries for computed conditions. Next up, you will package your best queries into reusable views so anyone can run them with a single line.

Create Views for Reusable Analytics

Your queries from the last step work. But imagine needing to re-type that 12-line JOIN every time you want a quick artist leaderboard.

Complex queries are hard to remember and error-prone to re-type. MySQL views solve this by saving a query as a virtual table. Anyone can SELECT from a view without knowing the underlying JOINs.

In this step, get ready to:
  • Create a view that packages your artist leaderboard query for one-line access.
  • Create a view that summarises each user's listening activity.
Create a view for the artist leaderboard

A view wraps a query behind a name. Once created, you query it exactly like a table.

  • Switch back to queries.sql in your editor.
  • Add the following code at the bottom of the file, below Query 4:
-- View 1: Top artists leaderboard
CREATE OR REPLACE VIEW top_artists AS
SELECT
    ar.name AS artist,
    COUNT(*) AS total_plays,
    SUM(t.duration_seconds) AS total_listen_seconds
FROM listening_history lh
INNER JOIN tracks t ON lh.track_id = t.track_id
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
GROUP BY ar.artist_id, ar.name
ORDER BY total_listen_seconds DESC;

What does this code do?

CREATE OR REPLACE VIEW top_artists AS saves the entire SELECT query behind the name top_artists. From now on, SELECT * FROM top_artists runs the full JOIN for you automatically.

The OR REPLACE part means you can re-run this file without errors if the view already exists. It just overwrites the old definition.

  • Save queries.sql.
  • Run the file to create the view by running this command in your terminal:
mysql -u root music_streaming < queries.sql

You will see output from the earlier SELECT queries in the file. That is expected. The CREATE VIEW statement executes silently at the end.

  • Verify the view works by running:
mysql -u root music_streaming -e "SELECT * FROM top_artists;"

You should see a table with artist names, total plays, and total listen seconds, sorted from highest to lowest. The same data your earlier aggregate query produced, but accessed with a single line.

Seeing an error about the view not existing?

Make sure you saved queries.sql before running the file. The view is only created when MySQL executes the CREATE OR REPLACE VIEW statement.

Check that your view code appears after Query 4, not inside it. A misplaced semicolon or missing line break can cause the parser to fail silently.

Help me debug my CREATE VIEW statement.

Create a view for user activity summary

The artist leaderboard answers one question. Now create a view that gives a complete snapshot of each user's listening behaviour.

  • In queries.sql, add the following code below View 1:
-- View 2: User activity summary
CREATE OR REPLACE VIEW user_activity AS
SELECT
    u.username,
    COUNT(*) AS total_plays,
    COUNT(DISTINCT lh.track_id) AS unique_tracks,
    COUNT(DISTINCT ar.artist_id) AS unique_artists,
    SUM(t.duration_seconds) AS total_listen_time
FROM users u
INNER JOIN listening_history lh ON u.user_id = lh.user_id
INNER JOIN tracks t ON lh.track_id = t.track_id
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
GROUP BY u.user_id, u.username;

What does this view do?

This view joins across all 5 tables to produce one row per user. Each row shows how many total plays they have, how many unique tracks and artists they explored, and their total listening time in seconds.

COUNT(DISTINCT lh.track_id) counts unique tracks (ignoring replays), while COUNT(*) counts every single play event.

  • Save queries.sql.
  • Run the file again to create the second view:
mysql -u root music_streaming < queries.sql

Before you verify, predict: which user do you think has the most total listen time based on the seed data you inserted?

  • Query the view to check by running:
mysql -u root music_streaming -e "SELECT * FROM user_activity ORDER BY total_listen_time DESC;"

You should see a table with one row per user, showing their username, total plays, unique tracks, unique artists, and total listen time. The results are sorted by listen time, highest first.

Seeing a column mismatch or empty results?

If the view creates but returns no rows, check that the JOIN starts from users u with an INNER JOIN to listening_history. Users with no listening history won't appear in an INNER JOIN.

If you see a syntax error, make sure there is a semicolon at the end of View 1 before View 2 begins.

Help me fix my user_activity view.

Why views instead of saving queries in a file?

You already have your queries saved in queries.sql. Views go one step further: they live inside the database itself. Any application, dashboard tool, or teammate can run SELECT * FROM top_artists without needing access to your SQL file or knowing the JOINs behind it.

Double check your queries file

✔️ Awesome, I've got everything!

Great. Confirm you have saved queries.sql with both views at the bottom.

ⓧ I'd like to double check the full code

Your queries.sql should contain the following sections in this order. Scroll through your file and confirm each is present:

  • USE music_streaming; at the top.
  • Query 1: the multi-table JOIN selecting username, track, artist, and listened_at.
  • Query 2: top artists by total listen time with GROUP BY and ORDER BY.
  • Query 3: the same query with a HAVING filter for more than 300 seconds.
  • Query 4: the subquery finding users who listened to more than 2 artists.
  • View 1: CREATE OR REPLACE VIEW top_artists with artist name, total_plays, and total_listen_seconds.
  • View 2: CREATE OR REPLACE VIEW user_activity with username, total_plays, unique_tracks, unique_artists, and total_listen_time.

You now have two reusable views living inside your database. Anyone with access to music_streaming can run a simple SELECT and get a full analytical report without writing a single JOIN. Next up, a secret mission that pushes your SQL further with a self-join pattern.

Secret mission

Build a "Similar Artists" Recommendation

Your database knows which users listened to which artists. Can you use that implicit relationship to find pairs of artists who share fans? This requires a self-join, one of the most powerful patterns in SQL.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • A MySQL database called music_streaming running locally.
  • MySQL server running as a Homebrew service.
  • SQL files: streaming_setup.sql, schema.sql, seed_data.sql, queries.sql, and similar_artists.sql.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the project with more queries.

  • MySQL will continue running in the background. It uses minimal resources on your machine.
  • Your music_streaming database and all SQL files remain intact and ready to use.
  • You can reconnect anytime by running mysql -u root music_streaming in Terminal.

Pause - I'll come back to this later

Shut down the MySQL server to free up memory. Your database and files stay on disk so you can resume later.

  • Stop the MySQL server by running:
brew services stop mysql
  • Your database data stays on disk. Start it again anytime with:
brew services start mysql
  • Your SQL files remain where you saved them for future reference.

Delete - I don't want to use this again

Remove all project resources and start fresh. This fully removes the database, the MySQL server, and the project files.

  • Drop the database by running:
mysql -u root -e "DROP DATABASE music_streaming;"
  • Stop and uninstall MySQL by running:
brew services stop mysql
brew uninstall mysql
  • Delete your SQL files. Navigate to the folder where you saved them and run:
rm streaming_setup.sql schema.sql seed_data.sql queries.sql similar_artists.sql
  • Confirm the files are gone by running ls in the same folder. You should no longer see any of those files listed.

Nice Work!

Nice Work!

You just built a fully functioning music streaming analytics database from scratch. From schema design to multi-table JOINs to reusable views, you now have hands-on experience with the SQL skills that show up in every data role.

Here's what you accomplished:

  • Designed a 5-table relational schema with enforced foreign key constraints that model real-world relationships between artists, albums, tracks, users, and listening history.
  • Wrote multi-table JOIN queries that reconnect data across the entire schema, plus GROUP BY with HAVING to build aggregate reports and a subquery to filter based on computed values.
  • Created reusable views that package complex analytical queries into simple, one-line interfaces anyone can query.
  • Secret Mission: Built a similar artists recommendation using a self-join pattern that discovers implicit relationships between artists through shared listeners.

Ready to quiz yourself?