1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL
Connected

Exercise

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.

Instructions

100 XP
  • 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.