Get the Second Most Recent Activity — LeetCode 1369 Python Solution

Table: UserActivity +---------------+---------+ | Column Name | Type | +---------------+---------+ | username | varchar | | activity | varchar | | startDate | Date | | endDate | Date | +---------------+---------+ This table may contain duplicates rows. This table contains information about the activity performed by each user in a period of time.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| username      | varchar |
| activity      | varchar |
| startDate     | Date    |
| endDate       | Date    |
+---------------+---------+
This table may contain duplicates rows.
This table contains information about the activity performed by each user in a period of time.
A person with username performed an activity from startDate to endDate.

import duckdb
import pandas as pd

def solution(user_activity: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("UserActivity", user_activity)
    return con.execute("""SELECT
    username,
    activity,
    startdate,
    enddate
FROM
    (
        SELECT
            *,
            RANK() OVER (
                PARTITION BY username
                ORDER BY startdate DESC
            ) AS rk,
            COUNT(username) OVER (PARTITION BY username) AS cnt
        FROM UserActivity
    ) AS a
WHERE a.rk = 2 OR a.cnt = 1;""").df()