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

Exercise

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.

Instructions 1/3

undefined XP
    1
    2
    3
  • Convert the granularity of dc_news_fact from a 20-minute sample period to a one hour sample period using DATE_TRUNC().