Rolling Average Steps — LeetCode 2854 Python Solution

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.

+-------------+------+ 
| 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.

import duckdb
import pandas as pd

# Pass input tables as keyword arguments matching the SQL table names.
def solution(**tables) -> pd.DataFrame:
    con = duckdb.connect()
    for name, df in tables.items():
        con.register(name, df)
    return con.execute("""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;""").df()