Date truncation

Unlike date_part() or EXTRACT(), date_trunc() keeps date/time units larger than the field you specify as part of the date. So instead of just extracting one component of a timestamp, date_trunc() returns the specified unit and all larger ones as well.

Recall the syntax:

date_trunc('field', timestamp)

Using date_trunc(), find the average number of Evanston 311 requests created per day for each month of the data. Ignore days with no requests when taking the average.

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Exercise instructions

Write a subquery to count the number of requests created per day.
Select the month and average count per month from the daily_count subquery.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Aggregate daily counts by month
SELECT ___ AS month,
       ___(___)
  -- Subquery to compute daily counts
  FROM (SELECT ___ AS day,
               ___ AS count
          FROM evanston311
         GROUP BY ___) AS daily_count
 GROUP BY ___
 ORDER BY month;

Edit and Run Code