Truncating dates

You've been asked to create an output of a date in descending order and the average daily humidity in Coffee County; however, the table is setup in 20 minute increments. While you could do this by CASTing the timestamp to a date, let's use one of the functions we just learned to make this faster.

This exercise is part of the course

Introduction to Redshift

View Course

Exercise instructions

Use a function on the date timestamp field to get just the date from the timestamp.
Calculate the average of the humidity by day,hourlyrelativehumidity, and order in reverse chronological order.
Group by the date, and order by the date descending.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Select just the actual date from the date field
SELECT ___(___) as day,
       -- Calculate the average daily humidity from hourlyrelativehumidity
       ___(___)
  FROM public_intro_redshift.coffee_county_weather
 -- Group by the day
 GROUP BY ___
 -- Order by the day in reverse chronological order.
 ORDER BY day ___;

Edit and Run Code