Filtering with CTEs

You got a new assignment to create a series of queries based on December's air temperature (hourlydrybulbtemperature) data reading from Coffee County, TN, USA. You decide to build a CTE of just December data so that you can focus on just the main queries and to help make it easier to iterate on all the questions. The first query is supposed to return just the routine hourly weather report at air terminals (FM-15).

This exercise is part of the course

Introduction to Redshift

View Course

Exercise instructions

Build a CTE named december_readings that selects the provided columns from coffee_county_weather.
Filter to dates between '2023-12-01' and '2024-12-31'.
Select the date and air_temp columns from the CTE.
Further, filter the results to only 'FM-15' report_type.

Hands-on interactive exercise

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

-- Build a CTE named december_reading
___ ___ ___ (
    SELECT date,
           hourlydrybulbtemperature AS air_temp,
           report_type
      FROM public_intro_redshift.coffee_county_weather
     -- Date is in December using between syntax
     WHERE date ___ '2023-12-01' AND '2024-12-31'
)
SELECT date,
       air_temp
  -- From the CTE
  FROM ___
 -- For the FM-15 report type
 WHERE ___ = 'FM-15';

Edit and Run Code