Welcome to Subscribe On Youtube
2854. Rolling Average Steps
Description
Table: Steps
+++  Column Name  Type  +++  user_id  int   steps_count  int   steps_date  date  +++ (user_id, steps_date) is the primary key for this table. Each row of this table contains user_id, steps_count, and steps_date.
Write a solution to calculate 3day
rolling averages of steps for each user.
We calculate the nday
rolling average this way:
 For each day, we calculate the average of
n
consecutive days of step counts ending on that day if available, otherwise,nday
rolling average is not defined for it.
Output the user_id
, steps_date
, and rolling average. Round the rolling average to two decimal places.
Return the result table ordered by user_id
, steps_date
in ascending order.
The result format is in the following example.
Example 1:
Input: Steps table: ++++  user_id  steps_count  steps_date  ++++  1  687  20210902   1  395  20210904   1  499  20210905   1  712  20210906   1  576  20210907   2  153  20210906   2  171  20210907   2  530  20210908   3  945  20210904   3  120  20210907   3  557  20210908   3  840  20210909   3  627  20210910   5  382  20210905   6  480  20210901   6  191  20210902   6  303  20210905  ++++ Output: ++++  user_id  steps_date  rolling_average  ++++  1  20210906  535.33   1  20210907  595.67   2  20210908  284.67   3  20210909  505.67   3  20210910  674.67  ++++ Explanation:  For user id 1, the step counts for the three consecutive days up to 20210906 are available. Consequently, the rolling average for this particular date is computed as (395 + 499 + 712) / 3 = 535.33.  For user id 1, the step counts for the three consecutive days up to 20210907 are available. Consequently, the rolling average for this particular date is computed as (499 + 712 + 576) / 3 = 595.67.  For user id 2, the step counts for the three consecutive days up to 20210908 are available. Consequently, the rolling average for this particular date is computed as (153 + 171 + 530) / 3 = 284.67.  For user id 3, the step counts for the three consecutive days up to 20210909 are available. Consequently, the rolling average for this particular date is computed as (120 + 557 + 840) / 3 = 505.67.  For user id 3, the step counts for the three consecutive days up to 20210910 are available. Consequently, the rolling average for this particular date is computed as (557 + 840 + 627) / 3 = 674.67.  For user id 4 and 5, the calculation of the rolling average is not viable as there is insufficient data for the consecutive three days. Output table ordered by user_id and steps_date in ascending order.
Solutions
Solution 1: Window Functions
We can use the window function LAG() OVER()
to calculate the difference in days between the current date and the date before the last date for each user. If the difference is $2$, it means that there are continuous data for $3$ days between these two dates. We can use the window function AVG() OVER()
to calculate the average of these $3$ data.

# Write your MySQL query statement below WITH T AS ( SELECT user_id, steps_date, ROUND( AVG(steps_count) OVER ( PARTITION BY user_id ORDER BY steps_date ROWS 2 PRECEDING ), 2 ) AS rolling_average, DATEDIFF( steps_date, LAG(steps_date, 2) OVER ( PARTITION BY user_id ORDER BY steps_date ) ) = 2 AS st FROM Steps ) SELECT user_id, steps_date, rolling_average FROM T WHERE st = 1 ORDER BY 1, 2;