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.

Diese Übung ist Teil des Kurses

Introduction to Redshift

Kurs anzeigen

Anleitung zur Übung

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.

Interaktive Übung

Versuche dich an dieser Übung, indem du diesen Beispielcode vervollständigst.

WITH minidoka_13903 AS (
    SELECT *,
           -- Converts the sample_date to a date
           ___(___ AS ___) as date_sampled
    FROM public_intro_redshift.idaho_samples
    WHERE fk_monitoringlocation = 13903
)
SELECT DATE_PART('year', date_sampled) as sample_year,
       DATE_PART('month', date_sampled) as sample_month,
       -- Count the records
       ___,
       -- Count the records for the prior month
       ___(___, ___) OVER (
           ORDER BY DATE_PART('year', date_sampled),
                    DATE_PART('month', date_sampled)
       ) AS prior_readings
  -- Use the prefiltered CTE you created above
  FROM ___
 GROUP BY sample_year,
          sample_month
 ORDER BY sample_year DESC,
          sample_month DESC;

Code bearbeiten und ausführen