I’m currently working through a problem and which requires me to create a with statement in order to use a strtftime() calculation and I am having trouble finding where I went wrong!
Problem:
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.)
Current solution:
with old_review as
(
select *
from reviews
where strftime(“%Y”, review_date) = review_date
)
select old_review
from reviews
join places
on places.id = reviews.place_id
where old_review = 2020;
I’d suggest double checking you temporary query separately so you can be sure of the rows returned. Consider the contents of the review_date column, here’s an example of the data held '2016-10-17'. What does strftime("%Y", review_date) look like?
I believe there’s a second issue too, in brief 1 is not equal to '1'.
Direct comparison with strings is tricky, if you’re not 100% sure of matches then it might be wise to check your condition very carefully (consider testing with a subset of the data if you can).
The purpose of the “WITH” clause at the start of your code is to determine the year that you want to check so you should say:
with old_review as
(
select *
from reviews
where strftime(“%Y”, review_date) = ‘2020’
)
Strftime returns a string and that is why it is ‘2020’
Try to solve it from there or look below for the rest
SPOILER
Now you want to select your data from the newly adjusted frame that we named reviews_2020 and so you need to join it to places with the according syntax.
SELECT *
FROM reviews_2020
JOIN places
ON places.id = reviews_2020.place_id;