Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Reporting with SQL Date and Time Functions Calculating Dates

Challenge appears to be flawed.

When attempting to answer the challenge that follows this video I typed in:

SELECT COUNT(*) AS shipped_yesterday FROM orders WHERE status = "shipped" AND DATE("now", "-1 day");

This returns an error, because you get shipped_yesterday with 149 orders shipped.

I was able to get the "correct" answer by writing:

SELECT COUNT(*) AS shipped_yesterday FROM orders WHERE status = "shipped" AND ordered_on = DATE("now", "-1 day");

This of course makes no sense, since the challenge asks for a count of the products shipped yesterday, not ordered yesterday. Any comments?

Jonathan Wadsworth
Jonathan Wadsworth
20,196 Points

I agree. This is a bug in both challenges. shipped_today and shipped_yesterday are worded incorrectly to be answered with the columns in the db. They still haven't fixed this....3 months later.

8 Answers

Steven Parker
Steven Parker
231,184 Points

Well, the problem with the first query is that DATE("now", "-1 day") is not a comparison expression in itself.

But you're right about the question being off. To do what is being asked, you'd really need another column like shipped_on. For this answer, the question should be "count of the shipped products ordered yesterday".

You may want to report this to Support (see the "?" in the circle on the lower left of the page). :arrow_lower_left:

Thanks for the reply. I see what you mean about the first query. I will let support know something needs to be changed.

Agreed. The wording for the question is strange for the database schema given.

Ben Jakuben
STAFF
Ben Jakuben
Treehouse Teacher

I am sorry that this specific challenge was unaddressed for so long! I have just updated this challenge to make it more clear. While teachers are always monitoring courses for problems and opportunities for improvement, we are also now having a small "Code Challenge Health" team proactively identify and fix issues like this one that aren't technically broken but could be improved.

:(

Dang, its been almost two years since this hasn't been fixed. Woa is me!

Ben Jakuben
Ben Jakuben
Treehouse Teacher

For some context...despite the problems with this challenge, enough students were getting through successfully and rating it positively that it wasn't triggering some reporting we have set up. I'm working to improve that reporting, though, to give us better insights about trouble spots like this. We recognize that poorly worded or broken code challenges can be frustrating and discouraging, and our entire organization is committed to the constant monitoring, maintenance, and improvement we need to be an effective place of learning.

Stevan Lay
Stevan Lay
8,306 Points

Found this thread after running into trouble with the question. Used the SQL playground to run the syntax and gave me the answer of 14 whilst the quiz said it was incorrect. I'm guessing it is still not fixed?

Ben Jakuben
Ben Jakuben
Treehouse Teacher

If you still have the code you ran, can you paste it in here for comparison? I can check it against our tests to see if anything else needs to be fixed.

Stevan Lay
Stevan Lay
8,306 Points

Hi Ben, Here were my code that I ran: SELECT COUNT(ordered_on) AS shipped_yesterday FROM orders WHERE status = "shipped" AND ordered_on = DATE("now", "-1 day");

Problem has since been answered and fixed by using "count(*)" and replacing "ordered_yesterday_and_shipped".

Cheers!

KWAN yen SEE
KWAN yen SEE
1,697 Points

Hey Stevan, try using COUNT(*) instead.

Kevin Gates
Kevin Gates
15,053 Points

This worked for me. I believe I answered the updated question:

SELECT COUNT(*) AS ordered_yesterday_and_shipped
FROM orders 
WHERE status = 'shipped' 
    AND ordered_on = date("now", "-1 day");
Stacy Villibord
Stacy Villibord
2,017 Points

Hi all, 2020 here. I wrote the code below but I am getting an error in my syntax. Can someone help. Thanks!

SELECT COUNT (*) AS "orderd_yesterday_and_shipped" FROM orders WHERE status = 'shipped' AND ordered_on = DATE("now, "-1 day");

I had a bit of trouble with this one as well. Here's what worked for me (included comments to explain why I did what):

```/* select ALL with the alias ordered_yesterday_and_shipped

SELECT COUNT(*) AS ordered_yesterday_and_shipped

*/ from the orders table

FROM orders

*/ status column must be "shipped" AND the ordered_on must be yesterday ie "now" - 1 day

WHERE status = "shipped"

AND ordered_on = DATE("now", "-1 day");```

I got tripped up because in the video "=" wasn't used before DATE.

Hope this helps!