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 exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Exercise instructions

Write a subquery using generate_series() to get all dates between the min() and max() date_created in evanston311.
Write another subquery to select all values of date_created as dates from evanston311.
Both subqueries should produce values of type date (look for the ::).
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