Databases Reporting with SQL Date and Time Functions Calculating Dates

What to do here?

Very lost. This is the prompt:

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 ordered yesterday and have the status of 'shipped'. Alias it to ordered_yesterday_and_shipped.

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

2 Answers

Steven Parker
Steven Parker
201,999 Points

Here's a few hints:

  • use the "COUNT" function
  • give the column the correct alias name
  • filter (using "WHERE") both the status AND the date
  • you can represent "yesterday" by "date("now", "-1 day")"

Give it your best shot, and if you still have trouble, show your query code for more specific help.

Shain Jones
Shain Jones
1,308 Points

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

This was my response and I am getting an error they they were "expecting a count of 14."

Any suggestions?

Steven Parker
Steven Parker
201,999 Points

You're really close! But you forgot to specify a unit with your date offset. Take a look at the hint I gave to Fairooz Adams about that.

And Fairooz — were you able to solve this challenge yourself?