Putting it all together
Many of the techniques you've learned in this course will be useful when building queries to extract data for model training. Now let's use some date/time functions to extract and manipulate some DVD rentals data from our fictional DVD rental store.
In this exercise, you are going to extract a list of customers and their rental history over 90 days. You will be using the EXTRACT(), DATE_TRUNC(), and AGE() functions that you learned about during this chapter along with some general SQL skills from the prerequisites to extract a data set that could be used to determine what day of the week customers are most likely to rent a DVD and the likelihood that they will return the DVD late.
This exercise is part of the course
Functions for Manipulating Data in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
-- Extract the day of week date part from the rental_date
___(___ ___ ___) AS dayofweek,
AGE(return_date, rental_date) AS rental_days
FROM rental AS r
WHERE
-- Use an INTERVAL for the upper bound of the rental_date
rental_date BETWEEN CAST('2005-05-01' AS ___)
AND CAST('2005-05-01' AS ___) + ___ '90 day';