Databases Reporting with SQL Date and Time Functions Differences Between Databases

Some hints to make up for some limited/bad diagnostic messages in the challenge answer engine..

If any of you have gotten this far and are still struggling let me advise you that

some of the Bummer! messages that say something about your "count" being wrong

or unhelpful messages saying error near such and such

will probably not be as helpful in getting to the right answer

as re-watching the video that precedes the challenge question.


This SQL Reporting course (unlike many at Treehouse) does really try to provide

a reasonably tight "coupling" between the SQL code presented in the videos

and what SQL code is expected in the challenges (as least better than using google

to search for relevant SQL code since their are many different SQL language variations possible).


This course tends toward using SQLite mostly,

but the cheat sheets it provides (in lieu of not having a downloadable zip file for the course)

do include links to multiple types/flavors of SQL:

https://github.com/treehouse/cheatsheets/blob/master/sql_basics/cheatsheet.md

https://github.com/treehouse/cheatsheets/blob/master/reporting_with_sql/cheatsheet.md


A few "hints' for some of the challenges in the Date and Time functions section:

.

Link to challenge:

https://teamtreehouse.com/library/reporting-with-sql/date-and-time-functions/todays-report

SELECT COUNT(*) AS shipped_today FROM orders WHERE status = "shipped" AND ordered_on = DATE("now");

Link to challenge: https://teamtreehouse.com/library/reporting-with-sql/date-and-time-functions/calculating-dates-2

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

Link to challenge:

https://teamtreehouse.com/library/reporting-with-sql/date-and-time-functions/formatting-dates-and-times

SELECT title, STRFTIME("%m/%Y", date_released) AS month_year_released FROM movies;
Winthrop Granville
Winthrop Granville
23,803 Points

Another thing that threw me in the first two challenges above, is that they are asking for orders that have the status of shipped today or shipped yesterday. While there is a "status" column where we can use the condition to check WHERE status = "shipped", there is NOT a way to actually check what day the order actually shipped.

For example, the only date column available is the "ordered_on" column. So in both of these challenges, what we really are selecting is orders that "shipped" AND orders that were "ordered_on" either today or yesterday. But this isn't necessarily providing us with the ship date. For example, in the second challenge, while we SELECT orders that we "ordered_on" yesterday, it's possible that they didn't actually ship until today.

It's possible that I am misunderstanding the question or overlooking something, and if this is the case, please advise! But I wanted to put this out there in case anyone else is experiencing the same issue, because when I went to complete both challenges, I sat there trying to figure out a way to determine what day these orders actually shipped.

3 Answers

Michael Lefkowitz
Michael Lefkowitz
12,295 Points

I agree with Winthrop. The challenges are poorly worded. They should be written like this:

In an ecommerce database there's an orders table with the columns id, product_id, user_id, address_id, ordered_on, status and cost. Count the total number of orders that were both ordered_on and shipped today. Alias it to shipped_today.

&

In an ecommerce database there's an orders table with the columns id, product_id, user_id, address_id, ordered_on, status and cost. Count the total number of orders that have both the status of shipped and were ordered_on yesterday. Alias it to shipped_yesterday.

Amber Touch
Amber Touch
2,339 Points

I doubt anyone is still reading here, but I agree that these challenges are poorly worded. Since there is no instructor to help the student, and the challenges are extremely difficult in many cases, it would be nice if the error code was more helpful. I'm on the verge of cancelling my account and going elsewhere because I see no point in frustrating myself unnecessarily.

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.