How hard is LeetCode 1369. Get the Second Most Recent Activity?

LeetCode 1369. Get the Second Most Recent Activity is rated Hard on LeetCode.

What topics does LeetCode 1369. Get the Second Most Recent Activity cover?

LeetCode 1369. Get the Second Most Recent Activity is tagged Database on LeetCode.

Is LeetCode 1369. Get the Second Most Recent Activity a premium problem?

Yes. LeetCode 1369. Get the Second Most Recent Activity is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.

Get the Second Most Recent Activity — LeetCode 1369 Python Solution

HardLeetCode PremiumDatabase

Problem: #1369
Reading time: 4 min
Source: leetcode.com

Table schema

SQL

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.

Example

SQL

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

Python solution

Python

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()

Complexity

Measure	Complexity
Time	O(n log n) (typical)
Space	O(n) auxiliary

Frequently asked questions

How hard is LeetCode 1369. Get the Second Most Recent Activity?: LeetCode 1369. Get the Second Most Recent Activity is rated Hard on LeetCode.
What topics does LeetCode 1369. Get the Second Most Recent Activity cover?: LeetCode 1369. Get the Second Most Recent Activity is tagged Database on LeetCode.
Is LeetCode 1369. Get the Second Most Recent Activity a premium problem?: Yes. LeetCode 1369. Get the Second Most Recent Activity is a LeetCode Premium problem, so the full statement and test cases require a paid LeetCode subscription.