Monthly average with missing dates
Find the average number of Evanston 311 requests created per day for each month of the data.
This time, do not ignore dates with no requests.
This exercise is part of the course
Exploratory Data Analysis in SQL
Exercise instructions
- Generate a series of dates from 2016-01-01 to 2018-06-30.
- Join the series to a subquery to count the number of requests created per day.
- Use
date_trunc()
to get months fromdate
, which has all dates, NOTday
. - Use
coalesce()
to replace NULLcount
values with 0. Compute the average of this value.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- generate series with all days from 2016-01-01 to 2018-06-30
WITH all_days AS
(SELECT generate_series(___,
___,
___::___) AS date),
-- Subquery to compute daily counts
daily_count AS
(SELECT date_trunc('day', date_created) AS day,
count(*) AS count
FROM evanston311
GROUP BY day)
-- Aggregate daily counts by month using date_trunc
SELECT ___ AS month,
-- Use coalesce to replace NULL count values with 0
avg(coalesce(___, ___)) AS average
FROM all_days
LEFT JOIN daily_count
-- Joining condition
ON all_days.___=daily_count.___
GROUP BY month
ORDER BY month;