1. Learn
    2. /
  2. Courses
    3. /
  3. Introduction to Redshift
Connected

Exercise

Comparing periods with lag

The Idaho field office wants to see how many tests are performed in each test period (year and month) that it is selected, and compare it with the prior one. They'd like to start with test site 13903 in Minidoka county.

Instructions

100 XP
  • Build a CTE, minidoka_13903, selecting all the columns from idaho_sample and converting the sample_date to a date as an additional column (date_sampled) for station 13903.
  • In the main query:
    • Count the records for the current month
    • Count the records for the prior month via a window function.