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.
Cet exercice fait partie du cours
Time Series Analysis in PostgreSQL
Exercice interactif pratique
Essayez cet exercice en complétant cet exemple de 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;