Variation by day of week

Does the time required to complete a request vary by the day of the week on which the request was created?

We can get the name of the day of the week by converting a timestamp to character data:

to_char(date_created, 'day')

But character names for the days of the week sort in alphabetical, not chronological, order. To get the chronological order of days of the week with an integer value for each day, we can use:

EXTRACT(DOW FROM date_created)

DOW stands for "day of week."

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Exercise instructions

Select the name of the day of the week the request was created (date_created) as day.
Select the mean time between the request completion (date_completed) and request creation as duration.
Group by day (the name of the day of the week) and the integer value for the day of the week (use a function).
Order by the integer value of the day of the week using the same function used in GROUP BY.

Hands-on interactive exercise

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

-- Select name of the day of the week the request was created 
SELECT ___(___, ___) AS day, 
       -- Select avg time between request creation and completion
       ___(___ - ___) AS duration
  FROM evanston311 
 -- Group by the name of the day of the week and 
 -- integer value of day of week the request was created
 GROUP BY day, EXTRACT(___ ___ ___)
 -- Order by integer value of the day of the week 
 -- the request was created
 ORDER BY EXTRACT(___ ___ ___);

Edit and Run Code