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.
Cet exercice fait partie du cours
Time Series Analysis in PostgreSQL
Instructions
- Find the top three hours of the day according to news article
viewsusingEXTRACT(); remember to aggregate the views.
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de code.
-- Find the top three reading hours of the day
SELECT
___ AS hour_of_day,
___ AS views
FROM ___
___
ORDER BY ___
___;