Windowing to the max

The Idaho field staff has been reporting back sample data for a variety of measures in their water system monitoring system. They'd like you to compare the Alkalinity samples to the maximum for that year to help them analyze trends towards the maximum. The data is kinda messy and you'll have to explicitly convert any dates.

This exercise is part of the course

Introduction to Redshift

View Course

Exercise instructions

Explicitly convert the sample_date to a date aliased as date_sampled and order by it.
Use a window function to calculate the max Alkalinity reading, characteristic_measurementvalue, for the year.

Hands-on interactive exercise

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

SELECT fk_monitoringlocation,
       -- Explicitly convert sample_date to a DATE
       ___(sample_date AS ___) as date_sampled,
       characteristic_measurementvalue,
       -- Find the max measurement
       ___(___) ___ (
            -- For each year window based on the sample date
            ___ BY DATE_PART(___, ___(___ AS DATE))
       ) as yearly_max
  FROM public_intro_redshift.idaho_samples
 WHERE characteristic_name = 'Alkalinity'
 ORDER BY date_sampled DESC,  
          fk_monitoringlocation;

Edit and Run Code