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.

Deze oefening maakt deel uit van de cursus

Exploratory Data Analysis in SQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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

Code bewerken en uitvoeren