Get startedGet started for free

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

View Course

Exercise instructions

  • Find the top three hours of the day according to news article views using EXTRACT(); 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 ___
___;
Edit and Run Code