Busiest hour of day
As a data analyst you may be called upon to perform an ad hoc query. The news app you work for has a popular feature that gives the top five news articles of the past week. The tables provided contain the five time series, one time series per news article. dc_news_dim contains the metadata for the five articles, and dc_news_fact contains the five time series, one for each article.
These news articles are read over the course of several days.
Reading behavior is cyclic in nature. Some hours of the day are busier than others. The editor wants to know what three hours of the day were the busiest in terms of the total number of times one of these articles was read.
The times are available in the ts field.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Exercise instructions
- Find the top three hours of the day according to news article
viewsusingEXTRACT(); remember to aggregate the views.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Find the top three reading hours of the day
SELECT
___ AS hour_of_day,
___ AS views
FROM ___
___
ORDER BY ___
___;