Objective

Write a SQL query to calculate the 3-day rolling average of posts for each user.

Table Schema:

• posts

Task Requirements:

• Calculate a 3-day rolling average of posts for each user
• The rolling average should include the current day and the 2 preceding days
• Round the rolling average to 2 decimal places
• Order results by user_id and post_date
  
  

Output columns: user_id, post_date, rolling_avg_3d

SELECT 
    user_id,
    post_date,
    ROUND(AVG(post_count) OVER (
        PARTITION BY user_id 
        ORDER BY post_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_3d
FROM posts
ORDER BY user_id, post_date;

Explanation

Step 1: Understanding Rolling Averages

A rolling average calculates the average over a sliding window. A 3-day rolling average includes the current day plus 2 preceding days.

Step 2: Using Window Functions

AVG(post_count) OVER (...)

AVG() as a window function calculates the average without collapsing rows, retaining individual row data.

Step 3: Partitioning by User

PARTITION BY user_id

Divides data into groups per user so the rolling average is calculated independently for each user.

Step 4: Defining the Window Frame

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Defines a 3-row sliding window (current row + 2 preceding rows). First row averages 1 day, second averages 2 days, third onward averages all 3.

Step 5: Rounding and Ordering

ROUND(..., 2) AS rolling_avg_3d
ORDER BY user_id, post_date

Rounds to 2 decimal places; final sort by user then date for chronological tracking.