Most active hours of day per news article
Knowing users' usage patterns is extremely important for improving the usability of an app. As a member of the data team on a news distribution platform, you are asked by content publishers to provide them with metrics that can help them create better content.
One publisher wants to know what hours of day were the most active for a collection of news articles. That collection is in the tables dc_news_dim and dc_news_fact.
We'll need to proceed in stages. First, we will manipulate the granularity, then we'll find the average, and, finally, calculate the top three most active hours of day for each news article.
This exercise is part of the course
Time Series Analysis in PostgreSQL
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Change time granularity from 20m to 1h
SELECT
id,
___ AS hour,
___ AS hourly_views
FROM dc_news_fact
GROUP BY id, hour
ORDER BY id, hour;