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).
Cet exercice fait partie du cours
Introduction to Redshift
Instructions
- Build a CTE named
december_readingsthat selects the provided columns fromcoffee_county_weather. - Filter to dates between
'2023-12-01'and'2024-12-31'. - Select the
dateandair_tempcolumns from the CTE. - Further, filter the results to only
'FM-15'report_type.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de 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';