Get Started

Find missing dates

The generate_series() function can be useful for identifying missing dates.

Recall:

generate_series(from, to, interval)

where from and to are dates or timestamps, and interval can be specified as a string with a number and a unit of time, such as '1 month'.

Are there any days in the Evanston 311 data where no requests were created?

This is a part of the course

“Exploratory Data Analysis in SQL”

View Course

Exercise instructions

  1. Write a subquery using generate_series() to get all dates between the min() and max() date_created in evanston311.
  2. Write another subquery to select all values of date_created as dates from evanston311.
  3. Both subqueries should produce values of type date (look for the ::).
  4. Select dates (day) from the first subquery that are NOT IN the results of the second subquery. This gives you days that are not in date_created.

Hands-on interactive exercise

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

SELECT day
-- 1) Subquery to generate all dates
-- from min to max date_created
  FROM (SELECT generate_series(___(___),
                               ___(___),
                               ___)::___ AS day
          -- What table is date_created in?
          FROM ___) AS all_dates
-- 4) Select dates (day from above) that are NOT IN the subquery
 WHERE day ___ 
       -- 2) Subquery to select all date_created values as dates
       (SELECT ___::___
          FROM evanston311);
Edit and Run Code