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

Exercise

Identifying trending news

A member of your team wants to explore a novel idea for identifying trending news. The idea is to add a field that looks at upticks. An uptick is when the number of views is greater than the average number of views for the previous two rows. Knowing this, we could then identify news that is trending upwards by looking at how many of the previous time intervals have been upticks. Your task is to add the field that calculates the average views over the previous two rows. Another member on your team will then use this to calculate upticks.

The table dc_news_fact is provided. It has the following columns:

  • id : news article identifier
  • views : number of views of the news article
  • ts : timestamp for a 20 minute interval

Instructions

100 XP
  • Add a field avg_previous_two that calculates the average of the previous two rows.