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.
Questo esercizio fa parte del corso
Exploratory Data Analysis in SQL
Istruzioni dell'esercizio
- Write a subquery to count the number of requests created per day.
- Select the month and average count per month from the
daily_countsubquery.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- 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;