Welp Project - Step 8

Data Science
1

Good morning, all

Working through this ‘Analyze Data with SQL - Welp Project’. I’m on step 8 and can’t figure out why this query I wrote isn’t returning any data. It isn’t bring up an error either. Here is what the question is looking for:

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.

(This will use the WITH clause as well as the strftime() function. See if you can use Google to dig up some information about the function before take a look at the hint.)

Here is the code that I have to accomplish this:

image

Any insight would be greatly appreciated!

2

I think I may have figured out my own question. After reviewing the STRFTIME() query document I noticed that my ‘%y’ should be ‘%Y’. Once I fixed that I got results from my query.

image

3 Likes
3

Hello!
I had the same query to some point but I got different years on the results as shown in image 1, but then I got rid of the STRFTIME function and only used LIKE and it worked, but sill want someone to point the problem with first one.

SQL 2
SQL 2900×542 61.3 KB

4

SQL
SQL899×535 65 KB

5

You can try clear the rows above this code and run again.

6

Xin,
I am dealing with the same issue as Yassine, please what do you mean with “clear the row and run again”? Thanks in advance,

7

Hi, I mean clear all the rows above the code you are running. It might not make much sense technically but that’s how I fixed my code running problem.

8

Hi Xin,
Thanks a lot for the further explanation. I’ve got what you mean, However, instead to cancel up the previous rows, an option is to transform them as comments (using – ) and then the query does work!

9

Hey! maybe do you know whi I have so many same rows?

image
image773×825 100 KB

10

What was your query? Please post your formatted code.

11

WITH reviews_2020 AS (
SELECT *
FROM reviews
WHERE STRFTIME(“%Y”, review_date) =‘2020’
)

SELECT *
FROM reviews_2020
JOIN places
ON reviews_2020.place_id = place_id

12

doesn’t the question ask you to filter out the reviews from 2020? So, those results would be all the reviews that fit your query parameters. You’re also missing a semicolon at the end of your query (after the ON).

13

Yes, but don’t you see that i have many duplicates? with the same id, name etc. Another People here don’t have it

14

Sorry, no, I couldn’t see it. But now I see your query isn’t quite right.

Granted, the question might be a little confusing, " JOIN the places to your WITH query to see a log of all reviews from 2020."

You’re supposed to SELECT * FROM places and then join that with your CTE.

like:

Summary 
WITH reviews2020 AS (
  SELECT *
  FROM reviews
  WHERE strftime("%Y", review_date) =
  '2020'
)
SELECT *
FROM places
JOIN reviews2020 ON
reviews2020.place_id = places.id;
15

I understood my mistake, it should be places.id and not places_id

Thank you!

1 Like