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 identifierviews: number of views of the news articlets: timestamp for a 20 minute interval
This exercise is part of the course
Time Series Analysis in PostgreSQL
Exercise instructions
- Add a field
avg_previous_twothat calculates the average of the previous two rows.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
-- Get average of the previous two views per row
SELECT
id,
ts,
views,
___ OVER (___
___
___)
AS avg_previous_two
FROM dc_news_fact;