Rolling Average Steps - Problem

You have a table called Steps that tracks daily step counts for users. Write a SQL query to calculate the 3-day rolling average of steps for each user.

A 3-day rolling average for a given date is the average of step counts for that date and the 2 preceding consecutive dates. If there aren't enough preceding dates, the rolling average is not defined for that date.

Requirements:

  • Calculate 3-day rolling averages ending on each date
  • Round the rolling average to 2 decimal places
  • Order results by user_id, then steps_date in ascending order
  • Only include dates where a 3-day rolling average can be calculated

Table Schema

Steps
Column Name Type Description
user_id PK int User identifier
steps_count int Number of steps taken on the date
steps_date PK date Date when steps were recorded
Primary Key: (user_id, steps_date)
Note: Each row contains daily step count for a specific user on a specific date

Input & Output

Example 1 — Basic Rolling Average
Input Table:
user_id steps_count steps_date
1 8000 2023-02-01
1 9000 2023-02-02
1 7000 2023-02-03
1 10000 2023-02-04
2 6000 2023-02-01
2 8000 2023-02-02
Output:
user_id steps_date rolling_average
1 2023-02-01 8000
1 2023-02-02 8500
1 2023-02-03 8000
1 2023-02-04 8666.67
2 2023-02-01 6000
2 2023-02-02 7000
💡 Note:

For user 1: The first 3-day rolling average is available on 2023-02-03, averaging days 01, 02, 03: (8000+9000+7000)/3 = 8000.00. On 2023-02-04, we average days 02, 03, 04: (9000+7000+10000)/3 = 8666.67. User 2 only has 2 days of data, so no 3-day rolling average can be calculated.
Example 2 — Multiple Users
Input Table:
user_id steps_count steps_date
1 5000 2023-01-01
1 6000 2023-01-02
1 7000 2023-01-03
2 4000 2023-01-01
2 5000 2023-01-02
2 6000 2023-01-03
2 8000 2023-01-04
Output:
user_id steps_date rolling_average
1 2023-01-01 5000
1 2023-01-02 5500
1 2023-01-03 6000
2 2023-01-01 4000
2 2023-01-02 4500
2 2023-01-03 5000
2 2023-01-04 6333.33
💡 Note:

Both users have enough data for rolling averages. User 1: (5000+6000+7000)/3 = 6000.00 on Jan 3rd. User 2: (4000+5000+6000)/3 = 5000.00 on Jan 3rd, and (5000+6000+8000)/3 = 6333.33 on Jan 4th.
Example 3 — Insufficient Data
Input Table:
user_id steps_count steps_date
3 12000 2023-03-01
3 15000 2023-03-02
Output:
user_id steps_date rolling_average
3 2023-03-01 12000
3 2023-03-02 13500
💡 Note:

User 3 only has 2 days of step data, which is insufficient to calculate a 3-day rolling average. The result is empty as no rolling averages can be computed.

Constraints

  • 1 ≤ user_id ≤ 1000
  • 1 ≤ steps_count ≤ 100000
  • steps_date is a valid date
  • Each (user_id, steps_date) combination appears at most once

Visualization

Tap to expand
Rolling Average Steps - SQL Solution INPUT: steps_log Table user_id date steps 1 2024-01-01 5000 1 2024-01-02 7000 1 2024-01-03 6000 1 2024-01-04 8000 2 2024-01-01 3000 2 2024-01-02 4000 2 2024-01-03 5000 3-Day Window Example: Day 1 Day 2 Day 3 AVG of these 3 days ALGORITHM STEPS 1 PARTITION BY user_id Group data per user 2 ORDER BY steps_date Sort by date ascending 3 Define Window Frame ROWS 2 PRECEDING to CURRENT ROW 4 Calculate AVG + ROUND ROUND(AVG(steps), 2) SELECT user_id, steps_date, ROUND(AVG(steps) OVER( PARTITION BY user_id ORDER BY steps_date ROWS 2 PRECEDING ), 2) AS rolling_avg FINAL RESULT user_id date rolling_avg 1 01-01 5000.00 1 01-02 6000.00 1 01-03 6000.00 1 01-04 7000.00 2 01-01 3000.00 2 01-02 3500.00 2 01-03 4000.00 Example: User 1, Day 3 (5000 + 7000 + 6000) / 3 = 18000 / 3 = 6000.00 OK - Ordered by user_id, steps_date Key Insight: The SQL window function AVG() with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW creates a sliding window that automatically handles edge cases: Day 1 averages just itself, Day 2 averages 2 days, and Day 3+ averages all 3 days. PARTITION BY ensures each user's calculation is independent. No joins or subqueries needed! TutorialsPoint - Rolling Average Steps | Optimal Solution using Window Functions

Related Problems

Asked in
Meta 28 Apple 22 Amazon 18
23.4K Views
Medium Frequency
~12 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