SQL BETWEEN and PRECEDING

🎬 Scenario: Analyzing Engagement Patterns Over Time#

Your manager at Meta, Jordan, wants to analyze engagement trends with rolling averages and cumulative sums.

Can you calculate engagement trends using different time windows? We need insights into how post likes evolve over time?

Your task is to use ROWS BETWEEN and PRECEDING to control window function behavior for rolling analytics.

Use the meta_posts table in this lesson:

1. Understanding ROWS BETWEEN in SQL#

The default window function behavior considers all previous rows up to the current row. However, using ROWS BETWEEN, we can:

• Limit the number of previous rows used in calculations.
• Include future rows for centered moving averages.

Common ROWS BETWEEN Configurations

2. Using ROWS BETWEEN for Running Totals#

Jordan wants to track how engagement accumulates over time for each user’s posts.

Example Query: Running Total of Likes Per User

1SELECT user_id, post_id, post_date, likes,  
2       SUM(likes) OVER (PARTITION BY user_id ORDER BY post_date  
3                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_likes  
4FROM meta_posts;

What’s Happening?

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW adds up likes progressively.
• Each row carries forward the cumulative sum for each user.

3. Using ROWS BETWEEN X PRECEDING AND CURRENT ROW for Moving Averages#

Jordan wants to calculate a 3-post moving average of likes, considering only the last 3 posts.

Example Query: 3-Post Moving Average of Likes

1SELECT user_id, post_id, post_date, likes,  
2       AVG(likes) OVER (PARTITION BY user_id ORDER BY post_date  
3                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_likes  
4FROM meta_posts;

What’s Happening?

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW considers only the last 2 posts + the current post.
• This prevents distortion from much older posts and provides a rolling average.

4. Using ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING for Centered Moving Averages#

Jordan wants a balanced moving average that considers the previous, current, and next post.

Example Query: Centered Moving Average of Likes

1SELECT user_id, post_id, post_date, likes,  
2       AVG(likes) OVER (PARTITION BY user_id ORDER BY post_date  
3                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS centered_avg_likes  
4FROM meta_posts;

What’s Happening?

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING balances the window by including the next row.
• This smooths out variations by considering surrounding values rather than past data alone.

✍️ SQL Exercises#

Exercise 1: Running Total of Likes#

Jordan wants to calculate a running total of likes for each user.

Filter condition:

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

Write an SQL query to return the requested data.

Exercise 2: Centered Moving Average of Likes#

Jordan also wants to calculate a 3-post centered moving average of likes per user.

Filter condition:

• Retrieve user ID, post ID, post date, likes, and centered moving average.
• Sort by user_id and post_date in ascending order.

Write an SQL query to return the requested data.