1. Learn
    2. /
  2. Courses
    3. /
  3. Time Series Analysis in PostgreSQL

Exercise

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.

Instructions

100 XP
  • Find the top three hours of the day according to news article views using EXTRACT(); remember to aggregate the views.