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

View Course

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 from date, which has all dates, NOT day.
Use coalesce() to replace NULL count 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;

Edit and Run Code