Having trouble with Welp Project

[data8847296587]

https://www.codecademy.com/paths/analyze-data-with-sql/tracks/analyze-data-sql-join-data/modules/analyze-data-sql-practice-joins/projects/sql-welp
Hello, am having a hard time with the following question : Sometimes on Welp, there are some old reviews that aren’t useful anymore.

Write a query using the WITH clause to select all the reviews that happened in 2020. JOIN the places to your WITH query to see a log of all reviews from 2020.

Although i think i’ve managed to right down the query the right way i can’t find which ON statement to use.

Here’s my query :

https://gist.github.com/codecademydev/50a8c7e24ff46d2876eec2350edfb6bc

[tgrtim]

What would be the key shared between these two tables? I’d imagine you’ve already used it in previous steps.

[data8847296587]

Can’t find any key shared between them . Since WITH creates a new temporary table . It’s a bonus question so i haven’t used is before. places table doesn’t have any column that says something about review date or something .

[tgrtim]

You use * in your select statement so your table would be almost (with the exception of the rows filtered out by WHERE …) identical to the original reviews table. Perhaps it would be worthwhile just querying this table (perhaps with LIMIT) so you know exactly what you did when creating the temporary table.

[data8847296587]

I think * is necessary because we want the other information aswell. T

https://gist.github.com/codecademydev/1e6e1a405115c9d47c72351e56ab1923

Tried something like this doesn’t seem to work either.

[selectall]

One tip to work with this is to make sure your query returns the results you expect before you try to use it in a WITH

@data8847296587

select *
from reviews
where strftime("%Y", review_date) = 2020

will not return any results, so there is nothing to join. Focus on getting results with the subquery first, then you should be good to go

[tgrtim]

Be careful with your comparisons in this is instance, double check what strftime actually returns.

[data8847296587]

Thanks for the feed back ! It seems ’ ’ were missing from 2020 . On to the next one !

[extrasolare]

But why that sentence don’t even return nothing.
I have try to use the missing quotes in the 2020 and others comparisons whit strftime but I even still get nothing.
Can you explain a little bit more please.

[tgrtim]

The previous issue boils down to the fact that '2020' is not equal to 2020 due to the comparison between different data types. What have you tried?

You can trial the output of strftime if you wanted to test things, see for example the following (you could even throw in a WHERE clause to check the other side of the comparison should you wish to)-

select strftime("%Y", review_date)
from reviews;

[micro3588364061]

Interesting. As I went through the reviews one by one, I see a couple in the year 2020. Not sure why it would not return anything for 2020.

[vaisakh.nair25hotmai]

missing quotes in 2020 .
It should be

with reviews20 as (
select *
from reviews
where strftime("%Y", review_date) = ‘2020’
)
select *
from reviews20
join places
on reviews20.place_id = places.id;

[ednavas9655736681]

Thanks for this contribution - i didn’t have this issue but just seeing other explain around a situation and also how to troubleshoot is really helpful

[xiyali3038623102]

[coffeencake]

Hello @xiyali3038623102 ,

Welcome to the forums!

Your query looks good.

Were you just posting the solution or having a problem?

[ajbet]

This had me scratching my head for an hour. Thanks for the suggestion!

[annach818]

Hello everyone! I desperately need your help! I’ve been trying to figure out what is wrong in my code for hours - but I just don’t see the mistake. The system won’t accept my answer. Could you please help me?
Thanks in advance!

[code8766386556]

Variation of the syntax which returns dates

WITH Review2020 AS (

SELECT places.name, reviews.review_date

FROM places

JOIN reviews

on reviews.place_id = places.id

)

SELECT review_date

FROM Review2020

WHERE strftime(“%Y”, review_date) = ‘2020’;

review_date
2020-01-01
2020-01-06
2020-01-14
2020-01-23
2020-01-27
2020-01-30
2020-02-07
2020-02-11
2020-02-20
2020-02-23
2020-02-23
2020-02-25
2020-03-07
2020-03-12
2020-03-17
2020-03-18
2020-03-27
2020-03-29
2020-04-01
2020-04-15
2020-04-16
2020-04-18
2020-04-19
2020-04-20
2020-04-21
2020-04-24
2020-04-26
2020-05-02
2020-05-07
2020-05-09
2020-05-23
2020-06-01
2020-06-01
2020-06-04
2020-06-04
2020-06-08
2020-06-08
2020-06-18
2020-06-21
2020-06-22
2020-06-26
2020-06-28
2020-06-28
2020-07-07
2020-07-08
2020-07-09
2020-07-10
2020-07-12
2020-07-17

[giga0220796106]

WELP
PLEASE GIVE EXPERT OPINION ON MY WORK! THANKS
STEP 6: Now write a query to do a LEFT JOIN on the tables, selecting the same columns as the previous question. How are the results of this query different? Would this or the INNER JOIN be more useful for a log of reviews?

-- STEP 6: 
SELECT places.name, places.average_rating,
  reviews.username, reviews.rating, reviews.review_date, reviews.note
  FROM places
LEFT JOIN reviews
  ON places.id = reviews.place_id
WHERE places.type = 'Restaurant';

INNER JOIN: This type of join only includes rows where there is a match in both tables. So, with INNER JOIN, you’ll get only the reviews for restaurants that have at least one review.

LEFT JOIN: This type of join includes all places (restaurants) regardless of whether they have reviews or not. If a restaurant has no reviews, the columns from the “reviews” table will have NULL values.

If you want a log of all restaurants and their reviews, including those without any reviews, then a LEFT JOIN might be more useful. If you only want a log of restaurants with at least one review, then an INNER JOIN would be more appropriate.

STEP 7: What about the places without reviews in our dataset? Write a query to find all the ids of places that currently do not have any reviews in our reviews table.

-- STEP 7:
SELECT *
FROM places
LEFT JOIN reviews
  ON places.id = reviews.place_id
WHERE reviews.place_id IS NULL;

STEP 8: Sometimes on Welp, there are some old reviews that aren’t useful anymore. Write a query using the WITH clause to select all the reviews that happened in 2020. JOIN the places to your WITH query to see a log of all reviews from 2020.

-- STEP 8:
WITH reviews_2020 AS (
SELECT *
FROM reviews
WHERE strftime('%Y', review_date) = '2020'
)
SELECT *
FROM places
LEFT JOIN reviews_2020
  ON places.id = reviews_2020.place_id
LIMIT 10;

STEP 9: Businesses want to be on the lookout for …ahem… difficult reviewers. Write a query that finds the reviewer with the most reviews that are BELOW the average rating for places.

--STEP 9: 
SELECT reviews.username, COUNT(*) 
FROM reviews
JOIN places
  ON reviews.place_id = places.id
WHERE reviews.rating < (SELECT AVG(average_rating)
  FROM places)
GROUP BY 1
ORDER BY 2 DESC
LIMIT 1;