Rolling Average Steps - Practice Coding Problems

Rolling Average Steps - Problem

Database Medium

Calculate 3-Day Rolling Average Steps for Fitness Tracking

Imagine you're building a fitness tracking application that helps users analyze their daily step patterns. Given a database table containing user step counts over multiple days, your task is to calculate the 3-day rolling average for each user.

The Steps table contains:
• user_id (int): Unique identifier for each user
• steps_count (int): Number of steps taken on a specific date
• steps_date (date): The date when steps were recorded

Rolling Average Calculation:
For each day, calculate the average of 3 consecutive days of step counts ending on that day. If fewer than 3 days of data are available ending on that day, the rolling average is not defined.

Output Requirements:
• Return user_id, steps_date, and rolling_average
• Round rolling average to two decimal places
• Order results by user_id and steps_date in ascending order
• Only include dates where a 3-day rolling average can be calculated

Input & Output

example_1.sql — Basic Rolling Average

$ Input: Steps table: | user_id | steps_count | steps_date | |---------|-------------|------------| | 1 | 1000 | 2023-01-01 | | 1 | 1200 | 2023-01-02 | | 1 | 1100 | 2023-01-03 | | 1 | 900 | 2023-01-04 | | 2 | 2000 | 2023-01-01 | | 2 | 1800 | 2023-01-02 |

› Output: | user_id | steps_date | rolling_average | |---------|------------|----------------| | 1 | 2023-01-03 | 1100.00 | | 1 | 2023-01-04 | 1066.67 |

💡 Note: For user 1 on 2023-01-03: avg(1000, 1200, 1100) = 1100.00. For user 1 on 2023-01-04: avg(1200, 1100, 900) = 1066.67. User 2 only has 2 days of data, so no 3-day rolling average can be calculated.

example_2.sql — Multiple Users

$ Input: Steps table: | user_id | steps_count | steps_date | |---------|-------------|------------| | 1 | 1000 | 2023-01-01 | | 1 | 1500 | 2023-01-02 | | 1 | 2000 | 2023-01-03 | | 2 | 3000 | 2023-01-01 | | 2 | 3200 | 2023-01-02 | | 2 | 2800 | 2023-01-03 |

› Output: | user_id | steps_date | rolling_average | |---------|------------|----------------| | 1 | 2023-01-03 | 1500.00 | | 2 | 2023-01-03 | 3000.00 |

💡 Note: User 1: avg(1000, 1500, 2000) = 1500.00. User 2: avg(3000, 3200, 2800) = 3000.00. Both users have exactly 3 days of consecutive data.

example_3.sql — Insufficient Data

$ Input: Steps table: | user_id | steps_count | steps_date | |---------|-------------|------------| | 1 | 1000 | 2023-01-01 | | 1 | 1200 | 2023-01-02 | | 2 | 2000 | 2023-01-05 |

› Output: Empty result set

💡 Note: No user has 3 consecutive days of data. User 1 has only 2 days, User 2 has only 1 day. Rolling average requires exactly 3 days of data.

Constraints

1 ≤ user_id ≤ 10⁴
1 ≤ steps_count ≤ 10⁶
steps_date is a valid date in 'YYYY-MM-DD' format
Each (user_id, steps_date) combination is unique
Rolling average must be calculated for exactly 3 consecutive days

Visualization

Tap to expand

Understanding the Visualization

Window Formation

Create a 3-day sliding window ending on current date

Average Calculation

Calculate mean of steps in the window

Window Slide

Move window forward by one day, drop oldest, add newest

Result Filtering

Only output results where full 3-day window exists

Key Takeaway

🎯 Key Insight: SQL window functions with ROWS frame specification provide the most efficient solution for rolling average calculations, automatically managing the sliding window while maintaining optimal performance.

Asked in

G Google 45 a Amazon 38 f Meta 32 ⊞ Microsoft 28 Apple 25

The optimal solution uses SQL window functions with AVG() OVER() and a ROWS 2 PRECEDING frame to efficiently calculate the 3-day rolling average. This approach processes data in a single pass while maintaining perfect accuracy, making it ideal for large-scale fitness tracking applications.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function (Optimal)	O(n log n)	O(1)	Use SQL window functions to calculate rolling average in a single pass
Brute Force (Correlated Subqueries)	O(n²)	O(1)	Use correlated subqueries to find previous 2 days for each date

Window Function (Optimal) — Algorithm Steps

Partition data by user_id to handle each user separately
Order by steps_date within each partition
Use AVG() with ROWS frame to calculate rolling average
Filter results to only include rows with exactly 3 days of data
Round results to 2 decimal places

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition by User

Data is partitioned by user_id and ordered by date

Sliding Window

AVG() function maintains a 3-row sliding window

Single Pass

All calculations done in one efficient pass through the data

Code -

solution.c — C

// Optimal SQL solution using window functions
#include <stdio.h>

int main() {
    char query[] = """
    WITH rolling_avg AS (
        SELECT 
            user_id,
            steps_date,
            steps_count,
            AVG(steps_count) OVER (
                PARTITION BY user_id 
                ORDER BY steps_date 
                ROWS 2 PRECEDING
            ) as rolling_average,
            COUNT(steps_count) OVER (
                PARTITION BY user_id 
                ORDER BY steps_date 
                ROWS 2 PRECEDING
            ) as days_count
        FROM Steps
    )
    SELECT 
        user_id,
        steps_date,
        ROUND(rolling_average, 2) as rolling_average
    FROM rolling_avg
    WHERE days_count = 3
    ORDER BY user_id, steps_date;
    """;
    
    printf("%s\n", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting by user_id and date

⚡ Linearithmic

Space Complexity

O(1)

Window function processes data in streaming fashion

✓ Linear Space

28.4K Views

High Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler