Music Streaming Analytics with SQL

Design a multi-table PostgreSQL database and write analytics queries.

Introduction

30 Second Summary

Every time you press play on a song, a database somewhere logs that listen. The "Top 5 Songs" charts and personalized recommendations in your favorite music app are all built by pulling patterns from that data.

In this project, you will build a music streaming analytics database from scratch using PostgreSQL. You will design tables, fill them with sample data, and write SQL queries that answer questions like "what are the top 5 most-played songs?"

What You'll Build

Open your terminal, type a single query, and watch a ranked chart of the top 5 most-played songs appear alongside artist names and play counts.

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

  • A running PostgreSQL database with five related tables where you can trace any song back through its album, artist, and genre.
  • Queries that connect data across tables using JOINs to answer questions like "show me every song by a specific artist, with the album name and release year."
  • Analytics queries using aggregate functions like COUNT, SUM, and AVG with GROUP BY to answer "what are the top 5 most-played songs?" and "what is the average song duration per genre?"
  • Secret Mission: Add a playlists feature with a many-to-many relationship and write queries to find the most popular playlists.

Are there any prerequisites?

No prerequisites needed. This project includes full setup instructions for PostgreSQL and all other tools.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. Take a moment to think about the project goals before any hands-on work begins.

Install PostgreSQL and Connect

Every query you write in this project runs against a live PostgreSQL database. Without a running database server and a way to talk to it, there is nothing to query.

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

In this step, get ready to:
  • Install PostgreSQL 18 using Homebrew.
  • Start the database server and verify the installation.
  • Create the music_streaming database.
Install PostgreSQL 18 via Homebrew and start the server

PostgreSQL is an open-source relational database that stores data in structured tables. You will install it through Homebrew, which is the fastest path since Homebrew is already on your Mac.

  • Press Cmd+Space to open Spotlight search.
  • Type Terminal and press Enter to open it.
  • Install PostgreSQL 18 by running this command:
brew install postgresql@18

What does this command do?

Homebrew downloads and installs the postgresql@18 formula, which includes both the PostgreSQL database server and psql, the command-line client you will use to interact with your database.

Homebrew also automatically initializes a default database cluster, so no manual setup is needed.

This may take a minute

Homebrew downloads the PostgreSQL package and its dependencies. It is normal for this to take a minute or two depending on your connection.

  • Start the PostgreSQL server by running this command:
brew services start postgresql@18

You should see a message confirming the service started successfully, similar to Successfully started `postgresql@18`.

What does this do?

This starts PostgreSQL as a background service managed by Homebrew. The server will keep running even if you close Terminal, and it will automatically start again when you restart your Mac.

Seeing an error about the service?

If you see a message about the service already running or failing to start, try stopping it first and restarting:

brew services stop postgresql@18
brew services start postgresql@18

Help me troubleshoot my PostgreSQL server startup issue with Homebrew on macOS.

Verify the installation and connect

PostgreSQL is installed and the server is running. Now confirm that the psql command-line client is available and working.

  • Check which version of PostgreSQL is installed by running:
psql --version

✔️ I see version 18.x

You should see output like psql (PostgreSQL) 18.4. PostgreSQL is ready to go.

ⓧ I see an older version

An older version of PostgreSQL is in your PATH, shadowing the version you just installed. The postgresql@18 formula is keg-only, which means Homebrew does not automatically add its binaries to your PATH.

  • Add the PostgreSQL 18 binaries to your PATH by running:
echo 'export PATH="$(brew --prefix postgresql@18)/bin:$PATH"' >> ~/.zshrc && source ~/.zshrc
  • Run psql --version again. You should now see psql (PostgreSQL) 18.4 or a similar 18.x version.

ⓧ Command not found

The postgresql@18 formula is keg-only, which means Homebrew does not automatically add its binaries to your PATH. You need to tell your shell where to find psql.

  • Add the PostgreSQL 18 binaries to your PATH by running:
echo 'export PATH="$(brew --prefix postgresql@18)/bin:$PATH"' >> ~/.zshrc && source ~/.zshrc
  • Run psql --version again. You should now see psql (PostgreSQL) 18.4 or a similar 18.x version.
  • Connect to the default PostgreSQL database by running:
psql postgres

You should see the postgres=# prompt appear. This means you are connected to the PostgreSQL server and ready to run commands.

What is psql?

psql is the interactive terminal for PostgreSQL. It lets you type SQL commands directly and see results instantly. The postgres after the command is the name of the default database that Homebrew created during installation.

Connection refused or role does not exist?

Make sure the PostgreSQL server is running. Run brew services list and check that postgresql@18 shows a status of started. If it is not running, start it again with brew services start postgresql@18.

Help me troubleshoot connecting to PostgreSQL with psql postgres on macOS.

Create the project database

You are connected to the default postgres database, but you do not want to store your music data here. A dedicated database keeps your project data separate and organized.

  • Create the project database by running this SQL command inside psql:
CREATE DATABASE music_streaming;

You should see CREATE DATABASE printed as confirmation.

Why a separate database?

The postgres database is a default utility database. Creating a dedicated music_streaming database gives your project its own space. If you ever want to start fresh, you can drop this database without affecting anything else on your server.

Before you connect, what prompt do you think will replace postgres=#?

  • Switch to your new database by running:
\c music_streaming

You should see You are now connected to database "music_streaming" and the prompt changes to music_streaming=#.

Seeing a "database does not exist" error?

Double check that you typed the CREATE DATABASE music_streaming; command with the semicolon at the end. Without the semicolon, psql waits for more input instead of running the command. You will know it is waiting if the prompt shows postgres-# instead of postgres=#.

If you see the postgres-# prompt, type a semicolon and press Enter to complete the statement.

Help me fix a database creation error in psql.

Your PostgreSQL server is running and your music_streaming database is ready. Next up, you will design and create your first tables to store artists, genres, albums, and songs.

Design and Create Your First Tables

Your PostgreSQL server is running and you have a fresh music_streaming database ready to fill.

A relational database is only as good as its schema. Without well-defined tables, data types, and constraints, your data has no structure and no rules to keep it consistent. In this step, you will design four tables that model the core data behind a music streaming service and populate them with sample data.

In this step, get ready to:
  • Create the genres and artists tables with primary keys and a foreign key relationship.
  • Add albums and songs tables to complete the four-table schema.
  • Populate all four tables with sample data in the correct insertion order.
Create the genres and artists tables

Every table in a relational database defines the shape of the data it stores. Each column has a specific data type and can carry constraints that enforce rules about what values are allowed.

  • Create the genres table by pasting this SQL into your psql session:
-- Create the genres table
CREATE TABLE genres (
    genre_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

What does this code do?

SERIAL PRIMARY KEY makes genre_id an auto-incrementing unique identifier. Every time you insert a new genre, PostgreSQL automatically assigns the next number.

VARCHAR(50) stores text up to 50 characters. NOT NULL prevents empty values. UNIQUE ensures no two genres share the same name.

You should see CREATE TABLE printed in your terminal, confirming the table was created.

Pasting multi-line SQL in psql

You can paste the entire statement at once. psql waits for the semicolon before executing, so it handles multiple lines automatically.

  • Create the artists table by pasting this SQL:
-- Create the artists table
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50),
    genre_id INTEGER REFERENCES genres(genre_id)
);

What is a foreign key?

REFERENCES genres(genre_id) creates a foreign key constraint. It tells PostgreSQL that every value in artists.genre_id must match an existing genre_id in the genres table.

This is the core difference between a relational database and something like MongoDB. The database itself enforces that relationships are valid. You cannot insert an artist with a genre that does not exist.

  • Verify both tables exist by running this command in psql:
\dt

You should see both genres and artists listed as tables.

Seeing an error when creating the artists table?

  • Check that you created the genres table first. The artists table references genres, so it must already exist.
  • Make sure every line matches exactly. A missing comma or parenthesis causes a syntax error.
  • Help me fix my CREATE TABLE error in psql.
Create the albums and songs tables

The next two tables follow the same pattern. Albums belong to artists, and songs belong to albums.

  • Create the albums table by pasting this SQL:
-- Create the albums table
CREATE TABLE albums (
    album_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    artist_id INTEGER REFERENCES artists(artist_id),
    release_year INTEGER
);

What does this code do?

artist_id INTEGER REFERENCES artists(artist_id) links each album to an artist through another foreign key. release_year INTEGER stores a plain number with no constraints, since not every album has a known release year.

You should see CREATE TABLE printed again.

  • Create the songs table by pasting this SQL:
-- Create the songs table
CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    album_id INTEGER REFERENCES albums(album_id),
    duration_seconds INTEGER,
    track_number INTEGER
);

How do these tables connect?

Each table references the one above it. Songs belong to albums, albums belong to artists, and artists belong to genres. This chain of foreign keys creates the relational structure.

To find which genre a song belongs to, you follow the chain: song to album to artist to genre. You will write queries that traverse this chain in the next step.

Do you think \dt will show two tables or four? Try it and find out.

  • Run \dt to verify all four tables exist:
\dt

You should see albums, artists, genres, and songs all listed.

  • Run \d artists to inspect the artists table structure:
\d artists

You should see each column listed with its data type. Look for genre_id listed as integer with a foreign key constraint referencing genres(genre_id).

Missing a table in the list?

  • Scroll up in your psql session to look for error messages. A typo in REFERENCES or a misspelled table name is the most common cause.
  • If a table was partially created, drop it with DROP TABLE table_name; and try again.
  • Help me figure out why my table is missing from \dt output.

Now save your CREATE TABLE statements to a reference file.

  • Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
  • Type VS Code and press Enter to open it.
  • Press Cmd+N (macOS) or Ctrl+N (Windows) to create a new file.
  • Paste all four CREATE TABLE statements into the file. Use the reference tab below to double check the content.
  • Save the file as 01_create_tables.sql by pressing Cmd+S (macOS) or Ctrl+S (Windows). Save it to your Desktop.

✔️ Awesome, I've got everything!

Great. Make sure you have saved 01_create_tables.sql before moving on.

ⓧ I'd like to double check the full code

Your 01_create_tables.sql file should contain exactly this:

-- Create the genres table
CREATE TABLE genres (
    genre_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Create the artists table
CREATE TABLE artists (
    artist_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(50),
    genre_id INTEGER REFERENCES genres(genre_id)
);

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

-- Create the songs table
CREATE TABLE songs (
    song_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    album_id INTEGER REFERENCES albums(album_id),
    duration_seconds INTEGER,
    track_number INTEGER
);
Insert sample data into all four tables

Tables must be populated in order because foreign key constraints check that referenced rows already exist. If you try to insert an artist before its genre exists, PostgreSQL will reject the insert.

  • Insert five genres by pasting this SQL into your psql session:
-- Insert genres
INSERT INTO genres (name) VALUES
    ('Rock'),
    ('Pop'),
    ('Hip-Hop'),
    ('Jazz'),
    ('Electronic');

What does this code do?

INSERT INTO genres (name) tells PostgreSQL which table and column to fill. The VALUES clause lists the data to add. Each parenthesized group becomes one new row.

You do not need to provide a genre_id value. The SERIAL type handles that automatically, starting from 1.

You should see INSERT 0 5, confirming five rows were added.

  • Insert seven artists by pasting this SQL:
-- Insert artists
INSERT INTO artists (name, country, genre_id) VALUES
    ('The Echoes', 'United States', 1),
    ('Luna Park', 'United Kingdom', 2),
    ('MC Dynamo', 'United States', 3),
    ('Blue Note Quartet', 'France', 4),
    ('Synthwave Collective', 'Germany', 5),
    ('Neon Lights', 'Japan', 2),
    ('The Groove', 'Australia', 1);

You should see INSERT 0 7.

  • Verify your data by running this query:
-- Check the artists and their genres
SELECT a.name AS artist, g.name AS genre
FROM artists a
INNER JOIN genres g ON a.genre_id = g.genre_id;

You should see all seven artists listed alongside their genre names. This confirms that the foreign key relationship between artists and genres is working.

  • Insert fourteen albums by pasting this SQL:
-- Insert albums
INSERT INTO albums (title, artist_id, release_year) VALUES
    ('Reverb Dreams', 1, 2020),
    ('Concrete Waves', 1, 2023),
    ('Starlight', 2, 2021),
    ('Midnight Glow', 2, 2024),
    ('Street Anthems', 3, 2022),
    ('Velocity', 3, 2024),
    ('Cafe Sessions', 4, 2019),
    ('After Hours', 4, 2023),
    ('Digital Sunrise', 5, 2021),
    ('Neon Pulse', 5, 2024),
    ('Tokyo Nights', 6, 2022),
    ('Prism', 6, 2024),
    ('Outback Sound', 7, 2020),
    ('Red Horizon', 7, 2023);

You should see INSERT 0 14.

  • The songs table has 30 rows. Insert the first batch of 15 songs by pasting this SQL:
-- Insert songs
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES
    ('Echo Chamber', 1, 245, 1),
    ('Fading Signal', 1, 198, 2),
    ('Reverb Loop', 1, 312, 3),
    ('City Rain', 2, 220, 1),
    ('Concrete Sky', 2, 267, 2),
    ('Star Falling', 3, 195, 1),
    ('Moonwalk', 3, 230, 2),
    ('Glow Stick', 4, 184, 1),
    ('Midnight Drive', 4, 275, 2),
    ('Late Bloom', 4, 310, 3),
    ('Block Party', 5, 198, 1),
    ('Flow State', 5, 245, 2),
    ('Turbo', 6, 176, 1),
    ('Acceleration', 6, 290, 2),
    ('Espresso', 7, 342, 1);

You should see INSERT 0 15.

  • Insert the remaining 15 songs by pasting this SQL:
-- Insert remaining songs
INSERT INTO songs (title, album_id, duration_seconds, track_number) VALUES
    ('Smooth Noir', 7, 298, 2),
    ('Last Call', 8, 265, 1),
    ('Velvet Dawn', 8, 318, 2),
    ('Pixel Rain', 9, 240, 1),
    ('Circuit Breaker', 9, 195, 2),
    ('Bass Drop', 10, 178, 1),
    ('Laser Grid', 10, 302, 2),
    ('Shibuya Lights', 11, 210, 1),
    ('Paper Crane', 11, 188, 2),
    ('Kaleidoscope', 12, 225, 1),
    ('Crystal Wave', 12, 260, 2),
    ('Dusty Road', 13, 280, 1),
    ('Campfire', 13, 315, 2),
    ('Crimson Sky', 14, 235, 1),
    ('Horizon Line', 14, 290, 2);

You should see INSERT 0 15 again.

Before you run the next query, how many total songs do you think are in the database?

  • Confirm the total count by running:
-- Count all songs in the database
SELECT COUNT(*) FROM songs;

The result is 30. Every song is accounted for across all 14 albums.

Seeing a foreign key violation?

  • This means you tried to reference a row that does not exist yet. Make sure you inserted data in the correct order: genres first, then artists, then albums, then songs.
  • Run SELECT * FROM genres; to confirm the genres table has data. If it is empty, start by inserting genres.
  • Help me fix a foreign key violation in PostgreSQL.

Now save your INSERT statements to a second reference file.

  • Switch back to VS Code.
  • Press Cmd+N (macOS) or Ctrl+N (Windows) to create a new file.
  • Paste all five INSERT statements into the file. These are the genres, artists, albums, and both songs blocks you just ran.
  • Save the file as 02_insert_data.sql on your Desktop.

✔️ Awesome, I've got everything!

Great. Make sure you have saved 02_insert_data.sql before moving on.

ⓧ I'd like to double check the full code

Your 02_insert_data.sql file should contain these INSERT blocks in order:

  • INSERT INTO genres with 5 rows (Rock, Pop, Hip-Hop, Jazz, Electronic).
  • INSERT INTO artists with 7 rows (The Echoes through The Groove).
  • INSERT INTO albums with 14 rows (Reverb Dreams through Red Horizon).
  • INSERT INTO songs with all 30 rows. If you split into two INSERT statements, both should be in the file.

Scroll through each block and confirm the values match what you pasted in psql.

Your four-table schema is built and loaded with sample data. In the next step, you will start writing queries to filter, sort, and explore this data.

Query Your Data with SELECT, WHERE, and ORDER BY

Your tables are loaded with 5 genres, 7 artists, 14 albums, and 30 songs. You have a real dataset ready to explore.

Right now, each table sits on its own. You can ask questions about songs or about artists, but you cannot connect a song to its artist because that information spans multiple tables. In this step, you will see what single-table queries can do and where they fall short.

In this step, get ready to:
  • Write SELECT queries with WHERE to filter rows by specific conditions.
  • Sort and limit results with ORDER BY and LIMIT.
  • Discover the limitation of single-table queries.
Write basic SELECT queries with WHERE filtering

The WHERE clause filters which rows a query returns. Without it, SELECT returns every row in the table.

  • In your psql session connected to music_streaming, run these queries to filter data with WHERE:
-- All artists from the United States
SELECT name, country FROM artists WHERE country = 'United States';

-- Songs longer than 240 seconds
SELECT title, duration_seconds FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;

-- Albums released after 2022
SELECT title, release_year FROM albums WHERE release_year > 2022 ORDER BY release_year;

What do these queries do?

  • The first query uses WHERE country = 'United States' to find only artists from the US. The = operator matches an exact value.
  • The second query uses > to find songs longer than 240 seconds. ORDER BY duration_seconds DESC sorts the results from longest to shortest.
  • The third query filters albums by release year. ORDER BY release_year sorts them in ascending order (oldest first) by default.

You should see 2 artists for the first query (The Echoes and MC Dynamo), several songs over 240 seconds for the second, and albums from 2023 and 2024 for the third.

Not seeing any results?

  • Make sure you are connected to the music_streaming database, not the default postgres database. Your prompt should read music_streaming=#. If it does not, run \c music_streaming to switch.
  • Check that you inserted sample data in the previous step. Run SELECT COUNT(*) FROM artists; and confirm you see 7.
  • String values in WHERE must be in single quotes. Double quotes or missing quotes cause a syntax error.

Why isn't my WHERE query returning results in psql?

More WHERE operators to try

WHERE supports more than = and >. Try LIKE for pattern matching (e.g., WHERE name LIKE 'The%') and IN for matching multiple values (e.g., WHERE country IN ('France', 'Japan')).

Sort and limit results

ORDER BY controls the order rows appear in your results. LIMIT caps how many rows come back. Together, they let you build "top N" charts from your data.

  • Run these queries in psql to sort and limit your results:
-- Top 5 longest songs
SELECT title, duration_seconds FROM songs ORDER BY duration_seconds DESC LIMIT 5;

-- 3 most recent albums
SELECT title, release_year FROM albums ORDER BY release_year DESC LIMIT 3;

-- Songs sorted by album then track number
SELECT title, album_id, track_number FROM songs ORDER BY album_id, track_number;

What do these queries do?

  • DESC sorts from highest to lowest. Without it, the default is ascending (lowest to highest).
  • LIMIT 5 returns only the first 5 rows after sorting. This is how you build a "Top 5" chart.
  • The third query sorts by two columns: first by album_id, then by track_number within each album. This groups songs by album in track order.

The top 5 longest songs query should show Espresso (342s) at the top, followed by Velvet Dawn (318s), Campfire (315s), Reverb Loop (312s), and Late Bloom (310s).

Results in the wrong order?

  • If the shortest songs appear first, check that you included DESC after ORDER BY. Without DESC, SQL defaults to ascending order.
  • If you see more than 5 rows, check that LIMIT 5 appears at the very end of your query, after ORDER BY.

My ORDER BY and LIMIT query isn't sorting correctly. Help me debug.

Notice the limitation of single-table queries

Look at the last query you ran. The album_id column shows numbers like 1, 2, and 10. Those numbers identify albums, but they do not tell you the album name. The name lives in the albums table, and your query only touched songs.

Before you run the next query, think about what you expect to see. Will the output show the album name, or just the album_id number?

  • Run this query to find all songs longer than 240 seconds with their album information:
-- Notice the limitation: album_id is just a number
SELECT title, album_id FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;

The results show each song title next to its album_id as a plain number. You can see that Espresso belongs to album 7, but what is album 7? You would have to run a separate query against the albums table to find out.

Why is this a problem?

Your schema splits data across tables on purpose. Genres, artists, albums, and songs each have their own table. Foreign keys connect them, but a single-table SELECT can only reach one table at a time.

To see a song's title alongside its album name and artist name in one query, you need to combine rows from multiple tables. That is exactly what JOINs do.

  • Switch to VS Code and create a new file called 03_select_queries.sql in the same folder as your other .sql files.
  • Copy all seven queries you just ran into this file so you have a reference for later:
-- All artists from the United States
SELECT name, country FROM artists WHERE country = 'United States';

-- Songs longer than 240 seconds
SELECT title, duration_seconds FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;

-- Albums released after 2022
SELECT title, release_year FROM albums WHERE release_year > 2022 ORDER BY release_year;

-- Top 5 longest songs
SELECT title, duration_seconds FROM songs ORDER BY duration_seconds DESC LIMIT 5;

-- 3 most recent albums
SELECT title, release_year FROM albums ORDER BY release_year DESC LIMIT 3;

-- Songs sorted by album then track number
SELECT title, album_id, track_number FROM songs ORDER BY album_id, track_number;

-- Notice the limitation: album_id is just a number
SELECT title, album_id FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;
  • Save 03_select_queries.sql.

✔️ Awesome, I've got everything!

Great. Double check you have saved 03_select_queries.sql with all seven queries.

ⓧ I'd like to double check the full code

Here is the complete 03_select_queries.sql file. Make sure yours matches:

-- All artists from the United States
SELECT name, country FROM artists WHERE country = 'United States';

-- Songs longer than 240 seconds
SELECT title, duration_seconds FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;

-- Albums released after 2022
SELECT title, release_year FROM albums WHERE release_year > 2022 ORDER BY release_year;

-- Top 5 longest songs
SELECT title, duration_seconds FROM songs ORDER BY duration_seconds DESC LIMIT 5;

-- 3 most recent albums
SELECT title, release_year FROM albums ORDER BY release_year DESC LIMIT 3;

-- Songs sorted by album then track number
SELECT title, album_id, track_number FROM songs ORDER BY album_id, track_number;

-- Notice the limitation: album_id is just a number
SELECT title, album_id FROM songs WHERE duration_seconds > 240 ORDER BY duration_seconds DESC;

You can filter, sort, and limit results from any single table. But your data spans multiple tables, and right now you cannot see an artist's name alongside their songs. Next up, you will connect tables with JOINs to unlock the full power of your relational database.

Connect Tables with JOINs

In the last step, you ran a query for songs longer than 240 seconds and saw album_id as a plain number instead of the album name. That happened because the album name lives in a different table, and a single-table query has no way to reach it.

A JOIN combines rows from two or more tables based on a related column. In this step, you will write JOINs that show a song's title alongside its album name and artist name in a single result.

In this step, get ready to:
  • Write an INNER JOIN that connects songs to albums.
  • Chain JOINs across three tables to display song, album, and artist data together.
  • Use a LEFT JOIN to reveal gaps in your data.
Write a two-table INNER JOIN

An INNER JOIN matches rows from two tables on a shared column and returns only the rows that have a match on both sides. The songs table has an album_id column that matches album_id in the albums table. That shared column is the bridge.

  • Create a new file called 04_join_queries.sql in VS Code. This is where you will save all the queries from this step.
  • Return to your music_streaming=# prompt in Terminal. Join songs to albums by pasting this query:
-- Two-table JOIN: songs with album titles
SELECT s.title AS song, a.title AS album
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id;

What does this query do?

  • s and a are table aliases. Instead of typing songs.title every time, you can write s.title.
  • ON s.album_id = a.album_id tells PostgreSQL which column to match on. Every song row is paired with the album row that shares its album_id.
  • AS song and AS album rename the output columns so both don't show up as title.

You should see 30 rows, each with a song name in the song column and the album it belongs to in the album column. No more mystery numbers.

Seeing a syntax error?

  • Make sure you included the semicolon at the end of the query. Without it, psql waits for more input instead of executing.
  • Check that the column name is album_id on both sides of the ON clause. A typo like albumid will cause an error.
  • Confirm you are connected to music_streaming (your prompt should say music_streaming=#). If it says postgres=#, run \c music_streaming to switch.

Help me debug my INNER JOIN query

Chain JOINs across three tables

Your two-table JOIN shows song and album names together. But what about the artist? That information lives in the artists table, one more hop away. You can chain a second INNER JOIN to bring in artist data too.

  • Extend your query to include artist names by running this three-table JOIN:
-- Three-table JOIN: songs with album and artist
SELECT s.title AS song, a.title AS album, ar.name AS artist
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
ORDER BY ar.name, a.release_year, s.track_number;

What does this query do?

  • The first INNER JOIN connects songs to albums on album_id. The second connects albums to artists on artist_id.
  • The chain follows the foreign key relationships you designed: songs point to albums, albums point to artists.
  • ORDER BY ar.name, a.release_year, s.track_number sorts by artist name first, then by album release year within each artist, then by track number within each album.

You should see 30 rows with three columns: song, album, and artist. Every song now carries its full context from across three tables.

You can also filter a JOIN with WHERE, just like a regular query.

  • Filter the three-table JOIN to show only songs by The Echoes by running this query:
-- Filter the three-table JOIN for a specific artist
SELECT s.title AS song, a.title AS album, a.release_year
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
WHERE ar.name = 'The Echoes'
ORDER BY a.release_year, s.track_number;

What changed here?

WHERE ar.name = 'The Echoes' filters the joined result to only rows where the artist name matches. Even though you are selecting columns from songs and albums, you can filter on any column from any joined table.

You should see 5 rows: The Echoes' songs across their two albums, sorted by release year then track number.

Getting 0 rows back?

  • String comparisons in SQL are case-sensitive. Make sure 'The Echoes' matches exactly, including the capital T and E.
  • Check that the string is wrapped in single quotes, not double quotes. PostgreSQL uses single quotes for string values.

Help me debug my filtered JOIN query

Use a LEFT JOIN to find gaps in your data

An INNER JOIN only returns rows that have a match on both sides. If an artist has no albums, INNER JOIN silently drops them from the results. A LEFT JOIN keeps every row from the left table, filling in NULL for columns that have no match on the right side.

  • Run this LEFT JOIN to see all artists alongside their albums:
-- LEFT JOIN shows all artists, even those with no albums
SELECT ar.name AS artist, a.title AS album
FROM artists ar
LEFT JOIN albums a ON ar.artist_id = a.artist_id
ORDER BY ar.name;

Right now, every artist has at least one album. The result looks the same as an INNER JOIN would. To see the real difference, you need an artist who has no albums.

  • Add an artist with no albums by running this INSERT:
-- Add an artist with no albums
INSERT INTO artists (name, country, genre_id) VALUES ('Solo Rider', 'Canada', 1);

Before you re-run the LEFT JOIN, think about what you expect to see. Will Solo Rider appear in the results? If so, what will their album column show?

  • Re-run the same LEFT JOIN query to see the difference:
-- LEFT JOIN shows Solo Rider with NULLs for album columns
SELECT ar.name AS artist, a.title AS album
FROM artists ar
LEFT JOIN albums a ON ar.artist_id = a.artist_id
ORDER BY ar.name;

Solo Rider now appears in the results with NULL in the album column. An INNER JOIN would have dropped Solo Rider entirely.

When would I use LEFT JOIN vs INNER JOIN?

Use INNER JOIN when you only want rows that have data in both tables (e.g., songs that belong to an album). Use LEFT JOIN when you want to see everything from the left table, including gaps (e.g., all artists, even those with no albums yet).

LEFT JOINs are especially useful for finding missing data. If you want to know which artists have not released any albums, a LEFT JOIN followed by WHERE a.album_id IS NULL gives you exactly that list.

Solo Rider not showing up?

  • Make sure you ran the INSERT statement before re-running the SELECT. If you see INSERT 0 1 after the INSERT, it worked.
  • Check that you used LEFT JOIN and not INNER JOIN. An INNER JOIN would hide Solo Rider because they have no matching album rows.

Help me figure out why my LEFT JOIN is not showing all artists

Save your queries to a file
  • Switch back to 04_join_queries.sql in VS Code.
  • Copy all four queries you ran in this step (the two-table JOIN, the three-table JOIN, the filtered JOIN, the INSERT, and the LEFT JOIN) into the file.
  • Save the file.

✔️ Awesome, I've got everything!

Great. Make sure you have saved 04_join_queries.sql before moving on.

ⓧ I'd like to double check the full code

Here is the complete 04_join_queries.sql file. Make sure yours matches:

-- Two-table JOIN: songs with album titles
SELECT s.title AS song, a.title AS album
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id;

-- Three-table JOIN: songs with album and artist
SELECT s.title AS song, a.title AS album, ar.name AS artist
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
ORDER BY ar.name, a.release_year, s.track_number;

-- Filter the three-table JOIN for a specific artist
SELECT s.title AS song, a.title AS album, a.release_year
FROM songs s
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
WHERE ar.name = 'The Echoes'
ORDER BY a.release_year, s.track_number;

-- LEFT JOIN: all artists, including those with no albums
-- First, add an artist with no albums
INSERT INTO artists (name, country, genre_id) VALUES ('Solo Rider', 'Canada', 1);

-- LEFT JOIN shows Solo Rider with NULLs for album columns
SELECT ar.name AS artist, a.title AS album
FROM artists ar
LEFT JOIN albums a ON ar.artist_id = a.artist_id
ORDER BY ar.name;

Your queries can now reach across tables and pull data together in a single result. JOINs solved the album_id mystery from the last step. Next up, you will use aggregate functions and GROUP BY to turn all this raw data into real insights, like "what are the top 5 most-played songs?"

Aggregate Data with GROUP BY

In the previous step, you used JOINs to connect data across multiple tables. You can now see a song's title alongside its album name and artist name in a single result.

But you still cannot answer summary questions like "how many songs does each artist have?" or "what is the average song duration per genre?" Aggregate functions like COUNT, SUM, and AVG combined with GROUP BY collapse many rows into summary statistics. This is how you turn raw data into insights.

In this step, get ready to:
  • Create a listens table to track play data for your songs.
  • Write aggregate queries using COUNT, AVG, and GROUP BY.
  • Filter aggregated results with HAVING to build a "Top 5 Most-Played Songs" chart.
Create the listens table and populate it with play data

Before you can aggregate anything, you need data to aggregate. A listens table will track which songs were played and by whom.

  • In VS Code, create a new file called 05_aggregate_queries.sql in the same folder as your other .sql files.
  • Add the following CREATE TABLE statement to 05_aggregate_queries.sql, then paste and run it in your psql session:
-- Create the listens table
CREATE TABLE listens (
    listen_id SERIAL PRIMARY KEY,
    song_id INTEGER REFERENCES songs(song_id),
    listener_name VARCHAR(100),
    listened_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

What does this table do?

Each row in listens represents one play of a song. The song_id foreign key links back to the songs table, so the database ensures every listen references a song that actually exists.

The listened_at column uses DEFAULT CURRENT_TIMESTAMP, which automatically records the current date and time when a row is inserted.

  • Confirm the table was created by running \dt in psql. You should see listens alongside your other four tables.

Seeing "relation listens already exists"?

This means you ran the CREATE TABLE statement twice. The table already exists and is ready to use. You can safely move on.

If you need to start fresh, run DROP TABLE listens; first, then re-run the CREATE TABLE statement.

Help me fix my CREATE TABLE error.

Now you need some listening data. Some songs should have many more plays than others to create interesting results when you aggregate.

  • Add this insert statement to 05_aggregate_queries.sql below the CREATE TABLE, then paste and run it in psql:
-- Insert listening data (some songs get more plays)
INSERT INTO listens (song_id, listener_name) VALUES
    (1, 'Alice'), (1, 'Bob'), (1, 'Charlie'), (1, 'Diana'), (1, 'Eve'),
    (2, 'Alice'), (2, 'Bob'),
    (3, 'Charlie'), (3, 'Diana'), (3, 'Eve'), (3, 'Alice'), (3, 'Bob'), (3, 'Charlie'),
    (5, 'Alice'), (5, 'Diana'),
    (6, 'Bob'), (6, 'Charlie'), (6, 'Diana'), (6, 'Eve'),
    (8, 'Alice'),
    (9, 'Bob'), (9, 'Charlie'), (9, 'Diana'), (9, 'Eve'), (9, 'Alice'), (9, 'Bob'), (9, 'Charlie'),
    (11, 'Eve'), (11, 'Alice'),
    (12, 'Bob'), (12, 'Charlie'), (12, 'Diana'),
    (15, 'Alice'), (15, 'Bob'), (15, 'Charlie'), (15, 'Diana'), (15, 'Eve'), (15, 'Alice'),
    (19, 'Bob'), (19, 'Charlie'),
    (22, 'Diana'), (22, 'Eve'), (22, 'Alice'), (22, 'Bob'),
    (27, 'Charlie'), (27, 'Diana'), (27, 'Eve');

Why are listens distributed unevenly?

Notice that song 9 (Midnight Drive) and song 15 (Espresso) have 7 and 6 listens respectively, while song 8 (Glow Stick) has only 1. This uneven distribution mimics real streaming data, where a few hits dominate the play counts.

You did not provide a value for listened_at in any row. PostgreSQL automatically fills it with the current timestamp because of the DEFAULT CURRENT_TIMESTAMP you set on the column.

  • Verify the data loaded correctly by running this count query in psql:
SELECT COUNT(*) FROM listens;
  • You should see 47 as the count. This confirms all your listening data is in the table.
  • Save 05_aggregate_queries.sql in VS Code.

Seeing a different count or an error?

If you see a foreign key violation, one of the song_id values does not match a row in the songs table. Make sure you inserted all 30 songs in Step 2 before running this insert.

If you accidentally ran the insert twice and see 94 rows, run DELETE FROM listens; to clear the table, then re-run the insert statement.

Help me fix my listens insert.

Write aggregate queries with GROUP BY

GROUP BY takes all the rows that share the same value in a column and collapses them into a single row. You then use an aggregate function to compute a summary value for each group.

  • Count the number of songs per album by adding this query to 05_aggregate_queries.sql and running it in psql:
-- Songs per album
SELECT a.title AS album, COUNT(*) AS song_count
FROM albums a
INNER JOIN songs s ON a.album_id = s.album_id
GROUP BY a.title
ORDER BY song_count DESC;

What does this query do?

  • INNER JOIN connects each album to its songs, just like in Step 4.
  • GROUP BY a.title collapses all songs belonging to the same album into one row.
  • COUNT(*) counts how many songs fell into each group.

You should see all 14 albums listed, each showing its song count. Albums like Midnight Glow and Reverb Dreams have 3 songs, while most others have 2.

  • Find the average song duration per genre by adding this query to 05_aggregate_queries.sql and running it in psql:
-- Average song duration per genre
SELECT g.name AS genre, ROUND(AVG(s.duration_seconds)) AS avg_duration
FROM genres g
INNER JOIN artists ar ON g.genre_id = ar.genre_id
INNER JOIN albums a ON ar.artist_id = a.artist_id
INNER JOIN songs s ON a.album_id = s.album_id
GROUP BY g.name
ORDER BY avg_duration DESC;

What does this query do?

This query chains four JOINs to connect genres all the way down to songs. AVG(s.duration_seconds) computes the average duration of all songs in each genre, and ROUND() removes the decimal places so the output is cleaner.

You should see 5 genres, each with a rounded average duration. Jazz should appear near the top since its songs (Espresso, Smooth Noir, Last Call, Velvet Dawn) are all over 260 seconds.

  • Count the total listens per song by adding this query to 05_aggregate_queries.sql and running it in psql:
-- Total listens per song
SELECT s.title AS song, ar.name AS artist, COUNT(*) AS total_listens
FROM listens l
INNER JOIN songs s ON l.song_id = s.song_id
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
GROUP BY s.title, ar.name
ORDER BY total_listens DESC;

Why group by two columns?

GROUP BY s.title, ar.name groups by both song title and artist name. Every column in your SELECT that is not inside an aggregate function must appear in the GROUP BY clause. PostgreSQL enforces this rule strictly.

You should see a ranked list with Midnight Drive and Espresso near the top, since they have the most listens in your data.

  • Save 05_aggregate_queries.sql in VS Code.
Use HAVING to filter aggregated results and build the "Top 5" chart

WHERE filters individual rows before grouping. HAVING filters groups after aggregation. This is how you ask questions like "which artists have more than one album?"

  • Find artists with more than one album by adding this query to 05_aggregate_queries.sql and running it in psql:
-- Artists with more than one album
SELECT ar.name AS artist, COUNT(*) AS album_count
FROM artists ar
INNER JOIN albums a ON ar.artist_id = a.artist_id
GROUP BY ar.name
HAVING COUNT(*) > 1
ORDER BY album_count DESC;

What does HAVING do here?

Without HAVING, this query would list every artist with their album count. HAVING COUNT(*) > 1 removes any artist whose count is 1 or less, keeping only those with multiple albums.

You cannot use WHERE COUNT(*) > 1 because WHERE runs before the groups are formed. HAVING runs after.

You should see 7 artists listed, each with an album count of 2. Solo Rider does not appear because they have no albums.

Now for the big one. Before you run the next query, think about what you expect: which songs do you think will be in the top 5 based on the listening data you inserted?

  • Build the "Top 5 Most-Played Songs" query by adding this to 05_aggregate_queries.sql and running it in psql:
-- TOP 5 MOST-PLAYED SONGS
SELECT s.title AS song, ar.name AS artist, COUNT(*) AS total_plays
FROM listens l
INNER JOIN songs s ON l.song_id = s.song_id
INNER JOIN albums a ON s.album_id = a.album_id
INNER JOIN artists ar ON a.artist_id = ar.artist_id
GROUP BY s.title, ar.name
ORDER BY total_plays DESC
LIMIT 5;

What does this query do?

  • The three-table JOIN chain connects listens through songs and albums to artists, so you see both the song title and the artist name.
  • COUNT(*) counts how many times each song was played.
  • ORDER BY total_plays DESC LIMIT 5 sorts by play count (highest first) and keeps only the top 5.

You should see a ranked list of 5 songs with their artist names and total play counts. Midnight Drive by Luna Park and Reverb Loop by The Echoes should appear near the top.

  • Find genres with an average song duration above 250 seconds by adding this final query to 05_aggregate_queries.sql and running it in psql:
-- Genres with average song duration above 250 seconds
SELECT g.name AS genre, ROUND(AVG(s.duration_seconds)) AS avg_duration
FROM genres g
INNER JOIN artists ar ON g.genre_id = ar.genre_id
INNER JOIN albums a ON ar.artist_id = a.artist_id
INNER JOIN songs s ON a.album_id = s.album_id
GROUP BY g.name
HAVING AVG(s.duration_seconds) > 250
ORDER BY avg_duration DESC;

What changed from the earlier average query?

This is the same four-table JOIN you wrote earlier, but now with HAVING AVG(s.duration_seconds) > 250 added. Only genres whose average exceeds 250 seconds survive. You should see fewer genres in the output than before.

  • Save 05_aggregate_queries.sql in VS Code.

✔️ Awesome, I've got everything!

Great. Double check you have saved 05_aggregate_queries.sql.

ⓧ I'd like to double check the full code

Scroll up through this step and confirm each of these blocks is present in your 05_aggregate_queries.sql file, in this order:

  • CREATE TABLE listens statement.
  • INSERT INTO listens with 47 rows of listening data.
  • Songs per album query (COUNT + GROUP BY).
  • Average song duration per genre query (AVG + four-table JOIN).
  • Total listens per song query.
  • Artists with more than one album (HAVING).
  • Top 5 most-played songs query.
  • Genres with average duration above 250 seconds (HAVING AVG).

You just turned raw rows into real insights. You can now count, average, and rank data across your entire database. Next up, you will take on a secret mission to add a playlists feature with a brand new relationship type.

Secret mission

Add Playlists with a Many-to-Many Relationship

Real music services let users create playlists. Playlists introduce a new kind of relationship: a playlist contains many songs, and a single song can appear in many playlists. Can you model this with a junction table and write queries that span 4+ tables?

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:

  • The music_streaming PostgreSQL database (includes all tables: genres, artists, albums, songs, listens, playlists, and playlist_songs).
  • PostgreSQL 18 running as a background service via Homebrew.
  • SQL files created in VS Code (01_create_tables.sql through 06_playlists.sql).

Keep everything running

No action needed. Choose this if you are still actively building or want to experiment with more queries.

  • The music_streaming database persists on disk. You can reconnect anytime by opening Terminal and running psql music_streaming.
  • The PostgreSQL server runs in the background via Homebrew services. It uses minimal resources while idle.
  • Your SQL files remain in VS Code wherever you saved them.

Pause - I'll come back to this later

Stop the PostgreSQL server to free up background resources. Your data is preserved on disk and will be available when you restart.

  • If you are still in the psql prompt, exit by typing \q and pressing Enter.
  • Stop the PostgreSQL server by running this command in Terminal:
brew services stop postgresql@18
  • When you are ready to come back, restart the server by running:
brew services start postgresql@18
  • Then reconnect to your database with psql music_streaming. All your tables and data will be exactly as you left them.

Delete - I don't want to use this again

Remove the database, stop the server, uninstall PostgreSQL, and delete your SQL files.

  • If you are still in the psql prompt, exit by typing \q and pressing Enter.
  • Delete the music_streaming database by running:
dropdb music_streaming
  • Stop the PostgreSQL server and uninstall PostgreSQL by running:
brew services stop postgresql@18 && brew uninstall postgresql@18
  • Delete the SQL files you created during this project (01_create_tables.sql through 06_playlists.sql). In VS Code, right-click each file in the Explorer sidebar and select Delete.

That's a Wrap!

That's a Wrap!

Nice work. You just built a fully functional music streaming analytics database from scratch using PostgreSQL, complete with a multi-table relational schema, sample data, and a collection of queries that answer real analytics questions.

You learned how to:

  • Installed PostgreSQL 18 and designed a multi-table relational schema with primary keys, foreign keys, and proper data types that enforce data integrity at the database level.
  • Wrote SELECT queries with WHERE, ORDER BY, and LIMIT, then connected tables using INNER JOIN and LEFT JOIN to combine data across multiple tables in a single query.
  • Used GROUP BY with aggregate functions like COUNT, SUM, and AVG alongside HAVING to build analytics queries like the "Top 5 Most-Played Songs" chart.
  • Secret Mission: Built a many-to-many relationship using a junction table and wrote multi-table aggregate queries for playlist analytics.

Ready to quiz yourself?