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.

Bu egzersiz

Exploratory Data Analysis in SQL

kursunun bir parçasıdır

Kursu Görüntüle

Egzersiz talimatları

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.

Uygulamalı interaktif egzersiz

Bu örnek kodu tamamlayarak bu egzersizi bitirin.

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

Kodu Düzenle ve Çalıştır