Custom aggregation periods
Find the median number of Evanston 311 requests per day in each six month period from 2016-01-01 to 2018-06-30. Build the query following the three steps below.
Recall that to aggregate data by non-standard date/time intervals, such as six months, you can use generate_series() to create bins with lower and upper bounds of time, and then summarize observations that fall in each bin.
Remember: you can access the slides with an example of this type of query using the PDF icon link in the upper right corner of the screen.
This exercise is part of the course
Exploratory Data Analysis in SQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Generate 6 month bins covering 2016-01-01 to 2018-06-30
-- Create lower bounds of bins
SELECT generate_series('2016-01-01', -- First bin lower value
___, -- Last bin lower value
'___'::interval) AS lower,
-- Create upper bounds of bins
generate_series('2016-07-01', -- First bin upper value
___, -- Last bin upper value
'___'::interval) AS upper;