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);

This exercise is part of the course

Exploratory Data Analysis in SQL

IntermediateSkill Level
4.5+
64 reviews

Learn how to explore what's available in a database: the tables, relationships between them, and data stored in them.

What time is it? In this chapter, you'll learn how to find out. You'll aggregate date/time data by hour, day, month, or year and practice both constructing time series and finding gaps in them.

Exercise 1: Date/time types and formatsExercise 2: ISO 8601Exercise 3: Date comparisonsExercise 4: Date arithmeticExercise 5: Completion time by categoryExercise 6: Date/time components and aggregationExercise 7: Date partsExercise 8: Variation by day of weekExercise 9: Date truncationExercise 10: Aggregating with date/time seriesExercise 11: Find missing dates
Exercise 12: Custom aggregation periodsExercise 13: Monthly average with missing datesExercise 14: Time between eventsExercise 15: Longest gapExercise 16: Rats!Exercise 17: Wrap-up

What is DataCamp?

Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.

Start Learning for Free