Exercise

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?

Instructions

100 XP
  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.