Question 8 from Extra Practice with Multiple Tables

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp

I’m struggling with question 8 of this exercise. I am having a hard time to get a grip on the strftime function, but moreover I wasn’t able to join the places to my WITH query after consulting the hint. And that will I was under the impression that I had mastered that part by now.

Is there someone who could explain me the strftime function and moreover help me with creating a join function in this given case?

Did you check the documentation? That might’ve been useful. Here’s a link that explains the strfdate function in a way I personally like.

2 Likes

Also, about the join…

  1. Using WITH and strftime, create the temporary table containing the reviews that happened in 2020. Maybe you could call it, I don’t know…reviews_2020? :sweat_smile:

  2. All you gotta do now is join reviews_2020 with places, using the places_id.

I’m not sure exactly what you were having trouble with but I hope this helped you :sweat_smile:

2 Likes

Thanks I get the strftime function now!

1 Like

Yeah I did just that in the join section. Therefore I’m wondering what’s wrong here:
SELECT *

FROM reviews_2020
JOIN places
ON places.id = reviews_2020.id;

It should actually be reviews_2020.places_id

1 Like

And how come? I haven’t selected places in my WITH statement?

No but, each review has a place_id to associate it with its respective place.

1 Like

Firstly, thanks a lot for your help. This is my first time coding and Multiple Tables are the first really hard part for me. The query still doesn’t get any response. I’ll just type my query here:
WITH reviews_2020 AS (

SELECT *

FROM reviews

WHERE strftime("%Y", review_date) = 2020

)

SELECT *

FROM reviews_2020

JOIN places

ON reviews_2020.places_id = reviews_2020.id;

1 Like

No problem :relaxed:
Yeah, I struggled a bunch too when I was going trough that lesson.

On your code…
strftime returns a string, so you should be typing this:

WHERE strftime("%Y", review_date) = "2020"

instead of this:

WHERE strftime("%Y", review_date) = 2020

Second, your ON condition should look like this:

ON reviews_2020.place_id = places.id

The reviews table has a FOREIGN KEY called place_id, which connects each review to a place in the places table. When joining two tables, you usually wanna make sure they both have something in common, so the records of each table get matched correctly.

I believe those two changes should make your query work.

2 Likes

Thanks a lot for your help!

1 Like