FAQ: Code Challenge: Multiple Tables - Code Challenge 9

This community-built FAQ covers the “Code Challenge 9” exercise from the lesson “Code Challenge: Multiple Tables”.

Paths and Courses
This exercise can be found in the following Codecademy content:

Web Development

FAQs on the exercise Code Challenge 9

Join the Discussion. Help a fellow learner on their journey.

Ask or answer a question about this exercise by clicking reply (reply) below!

Agree with a comment or answer? Like (like) to up-vote the contribution!

Need broader help or resources? Head here.

Looking for motivation to keep learning? Join our wider discussions.

Learn more about how to use this guide.

Found a bug? Report it!

Have a question about your account or billing? Reach out to our customer support team!

None of the above? Find out where to ask other questions here!

If I want to try answering this exercise using the window function, what would it look like?

This is my attempt:

SELECT
songs.title,
songs.artist,
COUNT(*) OVER(PARTITION BY plays.song_id) AS times_played
FROM plays LEFT JOIN songs ON plays.song_id = songs.id ;

Quick question on Joins, because I’ve gotten the join mix up multiple times throughout this exercise and I just want to see if there is an explanation to understand (apologies in advance if this sounds like a basic question).

The following code I wrote is as follows:

SELECT songs.title, songs.artist, play_count.times_played
FROM songs
JOIN play_count
ON
songs.id = play_count.song_id;

This returns nothing, making it wrong, and the correct query is as follows:

SELECT songs.title,
songs.artist,
play_count.times_played
FROM play_count
JOIN songs
ON play_count.song_id = songs.id;

The question I want to pose here is how do we determine the tables FROM and JOIN? I seem to be mixing up this order in multiple exercises, and I wanted to see if there was an easy way to understand when to use the correct columns.

Here is my code, which is the same as the solution. But I don’t understand why this cannot run

WITH play_count AS

(

SELECT song_id,

COUNT(*) AS times_played

FROM plays

GROUP BY song_id;

)

SELECT songs.title, songs.artist, play_count.times_played

FROM play_count

JOIN songs

ON play_count.song_id = songs.id;

1 Like

I spotted there is a semicolon past your ‘‘GROUP BY song_id’’ in line 6 & after removing works all fine

Both of the two answers below returned the same result. I was wondering if there really is any difference?

Answer 1:
WITH play_count AS(
SELECT song_id,
COUNT(*) AS ‘times_played’
FROM plays
GROUP BY song_id
)
SELECT songs.title, songs.artist, play_count.times_played
FROM play_count
JOIN songs
ON play_count.song_id = songs.id;

Answer 2:
SELECT songs.title, songs.artist, COUNT(*) AS “times_played”
FROM plays
JOIN songs
ON songs.id = plays.song_id
GROUP BY plays.song_id;

Hi Yunjiam,

You are correct. In terms of getting the desired output, these queries are similar.

However, the main idea behind this exercise was to create a new(smaller) table/version of the table ‘plays’. So, to create this new table, ‘play_count’, we used the with clause.

Hope this solves your query. :slight_smile: