Get startedGet started for free

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

View Course

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;
Edit and Run Code