Running Totals

🎬 Scenario: Tracking Cumulative Engagement on Meta Posts#

Your manager at Meta, Jordan, is interested in analyzing post engagement trends over time.

Can you provide cumulative engagement statistics, like running totals of likes and the average engagement per post?

Your task is to use window functions (SUM() OVER(), AVG() OVER(), COUNT() OVER()) to generate engagement trends. Use the meta_posts table to solve the question.

1. Understanding Window Functions for Aggregations#

Unlike GROUP BY, window functions perform aggregations while preserving individual rows in the result.

Syntax for Window Aggregations

SELECT column1, column2,  
       function() OVER (PARTITION BY column ORDER BY column) AS result  
FROM table_name;

• PARTITION BY groups data into subsets (optional).
• ORDER BY defines the ranking within the window.

2. Using SUM() OVER() to Calculate Running Totals#

Jordan wants a cumulative sum of likes to track engagement growth.

Example Query: Running Total of Likes

SELECT post_id, post_date, likes,  
       SUM(likes) OVER (ORDER BY post_date) AS running_total_likes  
FROM meta_posts;

What’s Happening?

• SUM(likes) OVER (ORDER BY post_date) adds up the likes progressively, creating a running total.
• Helps track cumulative engagement growth over time.

3. Using AVG() OVER() to Calculate Moving Averages#

Jordan wants to find the average number of likes per post over time.

Example Query: Rolling Average of Likes

SELECT post_id, post_date, likes,  
       AVG(likes) OVER (ORDER BY post_date) AS avg_likes_over_time  
FROM meta_posts;

What’s Happening?

• AVG(likes) OVER (ORDER BY post_date) calculates a moving average of likes per post.
• Helps identify trends in engagement over time.

4. Using COUNT() OVER() to Track Post Activity#

Jordan wants to track how many posts have been published over time.

Example Query: Running Count of Posts

SELECT post_id, post_date,  
       COUNT(*) OVER (ORDER BY post_date) AS running_post_count  
FROM meta_posts;

What’s Happening?

• COUNT(*) OVER (ORDER BY post_date) generates a running count of posts over time.
• Helps track content creation trends.

✍️ SQL Exercises#

Exercise 1: Running Total of Likes#

Jordan wants to calculate a running total of likes to track engagement growth.

Filter condition:

• Retrieve post ID, post date, likes, and running total likes.
• Sort by post_date in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Tracking Running Post Count#

Jordan also wants to track how many posts have been published over time.

Filter condition:

• Retrieve post ID, post date, and running post count.
• Sort by post_date in ascending order.

Write an SQL query to return the requested data.