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

1SELECT column1, column2,  
2       function() OVER (PARTITION BY column ORDER BY column) AS result  
3FROM 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

1SELECT post_id, post_date, likes,  
2       SUM(likes) OVER (ORDER BY post_date) AS running_total_likes  
3FROM 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

1SELECT post_id, post_date, likes,  
2       AVG(likes) OVER (ORDER BY post_date) AS avg_likes_over_time  
3FROM 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

1SELECT post_id, post_date,  
2       COUNT(*) OVER (ORDER BY post_date) AS running_post_count  
3FROM 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.