User Consecutive Three-Day Step Average

MEDIUM20 pts

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:
1. user_id
2. steps_date
3. rolling_average
Return rows ordered by user_id ASC, steps_date ASC.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Steps =

[{"user_id":1,"steps_count":600,"steps_date":"2024-01-02"},{"user_id":2,"steps_count":350,"steps_date":"2024-02-12"},{"user_id":1,"steps_count":700,"steps_date":"2024-01-03"},{"user_id":2,"steps_count":200,"steps_date":"2024-02-11"},{"user_id":2,"steps_count":100,"steps_date":"2024-02-10"},{"user_id":1,"steps_count":500,"steps_date":"2024-01-05"},{"user_id":1,"steps_count":400,"steps_date":"2024-01-01"}]

User Consecutive Three-Day Step Average

Hints

User Consecutive Three-Day Step Average

Hints