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”
Exercise instructions
- Write a subquery using
generate_series()
to get all dates between themin()
andmax()
date_created
inevanston311
. - Write another subquery to select all values of
date_created
as dates fromevanston311
. - Both subqueries should produce values of type
date
(look for the::
). - Select dates (
day
) from the first subquery that areNOT IN
the results of the second subquery. This gives you days that are not indate_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
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 datesExercise 12: Custom aggregation periodsExercise 13: Monthly average with missing datesExercise 14: Time between eventsExercise 15: Longest gapExercise 16: Rats!Exercise 17: Wrap-upWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.