User Consecutive Three-Day Step Average

Medium

A fitness analytics team wants a stable trend metric that smooths noisy daily activity. Table: Steps user_id: unique user identifier steps_count: number of steps recorded on that date steps_date: calendar date (YYYY-MM-DD) Task: For each user, compute a 3-day rolling average of steps_count. A row is included only when all of the following are true: The current row belongs to a user U on date D. There are exactly two earlier rows for user U that are the previous two calendar days (D-1 and D-2). rolling_average = average(steps on D-2, D-1, D), rounded to 2 decimals. Output requirements: Return columns exactly in this order: user_id steps_date rolling_average Return rows ordered by user_id ASC, steps_date ASC. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Steps:
| user_id | steps_count | steps_date  |
|---------|-------------|-------------|
| 1       | 400         | 2024-01-01  |
| 1       | 600         | 2024-01-02  |
| 1       | 700         | 2024-01-03  |
| 1       | 500         | 2024-01-05  |
| 2       | 100         | 2024-02-10  |
| 2       | 200         | 2024-02-11  |
| 2       | 350         | 2024-02-12  |

Output

[
  {"user_id":1,"steps_date":"2024-01-03","rolling_average":566.67},
  {"user_id":2,"steps_date":"2024-02-12","rolling_average":216.67}
]

Explanation

User 1 qualifies on 2024-01-03 using Jan 1-3. Date 2024-01-05 is excluded because 2024-01-04 is missing. User 2 has one valid 3-day consecutive window ending on 2024-02-12.

Example 2

Input

Steps:
| user_id | steps_count | steps_date  |
|---------|-------------|-------------|
| 3       | 1000        | 2024-03-01  |
| 3       | 900         | 2024-03-02  |
| 3       | 950         | 2024-03-03  |
| 3       | 1100        | 2024-03-04  |
| 3       | 1200        | 2024-03-05  |

Output

[
  {"user_id":3,"steps_date":"2024-03-03","rolling_average":950.0},
  {"user_id":3,"steps_date":"2024-03-04","rolling_average":983.33},
  {"user_id":3,"steps_date":"2024-03-05","rolling_average":1083.33}
]

Explanation

With five consecutive days, three output rows are produced, one for each possible end date of a 3-day consecutive window.

Example 3

Input

Steps:
| user_id | steps_count | steps_date  |
|---------|-------------|-------------|
| 7       | 800         | 2024-04-01  |
| 7       | 900         | 2024-04-02  |
| 8       | 500         | 2024-05-10  |
| 8       | 700         | 2024-05-12  |
| 8       | 900         | 2024-05-13  |

Output

[]

Explanation

No user has a full sequence of 3 consecutive calendar days, so the result is empty.

Constraints

1 <= number of rows in Steps <= 200000
1 <= user_id <= 1000000000
0 <= steps_count <= 1000000
steps_date is a valid date in YYYY-MM-DD format
(user_id, steps_date) is unique
A rolling row is emitted only when D-2, D-1, D all exist for the same user
rolling_average must be rounded to 2 decimals
Return exactly columns: user_id, steps_date, rolling_average
Sort output by user_id ASC, steps_date ASC
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with the exact required columns

Steps: | user_id | steps_count | steps_date | |---------|-------------|-------------| | 1 | 400 | 2024-01-01 | | 1 | 600 | 2024-01-02 | | 1 | 700 | 2024-01-03 | | 1 | 500 | 2024-01-05 | | 2 | 100 | 2024-02-10 | | 2 | 200 | 2024-02-11 | | 2 | 350 | 2024-02-12 |

Steps: | user_id | steps_count | steps_date | |---------|-------------|-------------| | 3 | 1000 | 2024-03-01 | | 3 | 900 | 2024-03-02 | | 3 | 950 | 2024-03-03 | | 3 | 1100 | 2024-03-04 | | 3 | 1200 | 2024-03-05 |

[ {"user_id":3,"steps_date":"2024-03-03","rolling_average":950.0}, {"user_id":3,"steps_date":"2024-03-04","rolling_average":983.33}, {"user_id":3,"steps_date":"2024-03-05","rolling_average":1083.33} ]

Steps: | user_id | steps_count | steps_date | |---------|-------------|-------------| | 7 | 800 | 2024-04-01 | | 7 | 900 | 2024-04-02 | | 8 | 500 | 2024-05-10 | | 8 | 700 | 2024-05-12 | | 8 | 900 | 2024-05-13 |