Colab Practice: Problem Solving with SQL

Step 0: PostgreSQL Playground

Open PostgreSQL Colab Playground

Instructions:

• Run the setup cell to create the Users, Songs, and Listens tables.

• Run sql-practice-dataset.sql to add the extended songs, listens, and Recommendations table.

• Run one SELECT to confirm the tables loaded.

Practice 1: Writing Debug Tables

Creating a Debug Table forces you to evaluate logical execution ordering: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT.

Tip: Check your work: All answers and interactive logic traces are available in the Practice Solutions Colab.

1.1: Filtering Strategies (JOIN vs IN vs EXISTS)

Analyze these four queries. Do they all pull the same genres? Which is the most efficient for checking existence in a large dataset?

-- Query 1: INNER JOIN
SELECT DISTINCT s.genre FROM Songs s
INNER JOIN Listens l ON s.song_id = l.song_id;

-- Query 2: INNER JOIN (No Distinct - what happens?)
SELECT s.genre FROM Songs s
INNER JOIN Listens l ON s.song_id = l.song_id;

-- Query 3: IN Clause
SELECT DISTINCT s.genre FROM Songs s
WHERE s.song_id IN (SELECT song_id FROM Listens);

-- Query 4: EXISTS Clause
SELECT DISTINCT s.genre FROM Songs s
WHERE EXISTS (SELECT 1 FROM Listens l WHERE l.song_id = s.song_id);

1.2: Set Operations (UNION vs. EXCEPT)

Mickey (user 1) has a listening history and a set of recommendations. Load sql-practice-dataset.sql so the Recommendations table exists (see The Practice Dataset below), then compare the genres he plays against the genres he is recommended.

-- Query 5: Mickey's Listening History
SELECT s.genre FROM Songs s INNER JOIN Listens l ON s.song_id = l.song_id WHERE l.user_id = 1;

-- Query 6: Mickey's Recommendations
SELECT s.genre FROM Songs s INNER JOIN Recommendations r ON s.song_id = r.song_id WHERE r.user_id = 1;

-- Query 7: UNION (History OR Recommendations)
SELECT s.genre FROM Songs s INNER JOIN Listens l ON s.song_id = l.song_id WHERE l.user_id = 1
UNION
SELECT s.genre FROM Songs s INNER JOIN Recommendations r ON s.song_id = r.song_id WHERE r.user_id = 1;

-- Query 8: EXCEPT (History BUT NOT recommended)
SELECT s.genre FROM Songs s INNER JOIN Listens l ON s.song_id = l.song_id WHERE l.user_id = 1
EXCEPT
SELECT s.genre FROM Songs s INNER JOIN Recommendations r ON s.song_id = r.song_id WHERE r.user_id = 1;

1.3: The LEFT JOIN

Check this query for User 1. Does it yield more or fewer rows than an INNER JOIN? Why?

-- Query 9: All genres, matched against Mickey's listens
SELECT DISTINCT s.genre 
FROM Songs s 
LEFT JOIN Listens l ON s.song_id = l.song_id AND l.user_id = 1;

The Practice Dataset

The advanced challenges run on an extended dataset: it adds songs, listens, and a Recommendations table on top of the canonical 4 users, 10 songs, and 9 listens. Load it by running sql-practice-dataset.sql in the Colab after the base tables. The canonical rows are unchanged, so the four users stand and Pluto is still the only user with no listens.

Added Songs (song_id 11 to 18)

Genres stay consistent with the base data: Taylor Swift is Pop (Bad Blood, song 9, is her one Rock track), Ed Sheeran is Rock, the Beatles are Classic, and Adele joins as a second Pop artist. The added listens come from users 1 to 3, so Pluto keeps zero listens; run qry_count_songs in the Colab to see each song's totals.

Recommendations (a new table)

Recommendations(user_id, song_id) records songs the system suggests to a user. Queries 5 to 8 in 1.2 read Mickey's rows:

Practice 2: Building Intuition with Window Functions

Goal: Compare near-identical ranking queries and see why their outputs differ.

2.1: The Base Count (qry_count_songs)

Find the total listens for each song, grouped by genre, song ID, and title. This is your baseline for ranking queries.

2.2: Ranking by Genre (qry_top_songs_genre)

Identify the top-k ranked songs per genre based on listens (try k = 1, 2, and 3).

• How does competition shift when ranking within a genre versus the entire dataset?

2.3: Ranking by Artist (qry_top_songs_artist)

Identify the top-k ranked songs per artist based on listens.

• If an artist has multiple songs, which one leads?

2.4: Finding the Global Peak (qry_max_listens)

Find the song with the highest number of listens across the platform.

2.5: The Artist Champion (qry_max_listens_artist)

Identify the song with the most listens for each artist.

• This is a "Top-1 per Artist" query. Compare this to 2.3.

2.6: Multi-Dimensional Ranking (qry_top_songs_genre_artist)

Find the top-k ranked songs per (genre, artist) based on listens.

• Observe how the partitions shrink. What happens to the rank values?

Challenge: When Do These Match?

Run these two queries in the Colab and compare their row counts.

-- Query A: Rank 1 per Genre
runSql('Query top_songs_genre', qry_top_songs_genre.format(k=1))

-- Query B: Rank 1 per (Genre, Artist)
runSql('Query top_songs_genre_artist', qry_top_songs_genre_artist.format(k=1))

Query A returns one song per genre (3 rows). Query B returns one per (genre, artist), so it returns more (5 rows): Pop has both Taylor Swift and Adele, and Rock has both Ed Sheeran and Taylor Swift, so each of those genres yields two rows.

• The two queries agree only on a genre with a single artist. Classic (Beatles only) is the one genre where Query A and Query B return the same song.

• What would you change in the data to make Query A and Query B return identical rows for every genre?