Biggest Window Between Visits — LeetCode 1709 Python Solution

Table: UserVisits +-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | visit_date | date | +-------------+------+ This table does not have a primary key, it might contain duplicate rows. This table contains logs of the dates that users visited a certain retailer.

+-------------+------+
| Column Name | Type |
+-------------+------+
| user_id     | int  |
| visit_date  | date |
+-------------+------+
This table does not have a primary key, it might contain duplicate rows.
This table contains logs of the dates that users visited a certain retailer.

import duckdb
import pandas as pd

def solution(user_visits: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("UserVisits", user_visits)
    return con.execute("""WITH
    T AS (
        SELECT
            user_id,
            DATEDIFF(
                LEAD(visit_date, 1, '2021-1-1') OVER (
                    PARTITION BY user_id
                    ORDER BY visit_date
                ),
                visit_date
            ) AS diff
        FROM UserVisits
    )
SELECT user_id, MAX(diff) AS biggest_window
FROM T
GROUP BY 1
ORDER BY 1;""").df()