New Users Daily Count — LeetCode 1107 Python Solution

Table: Traffic +---------------+---------+ | Column Name | Type | +---------------+---------+ | user_id | int | | activity | enum | | activity_date | date | +---------------+---------+ This table may have duplicate rows. The activity column is an ENUM (category) type of ('login', 'logout', 'jobs', 'groups', 'homepage').

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| activity      | enum    |
| activity_date | date    |
+---------------+---------+
This table may have duplicate rows.
The activity column is an ENUM (category) type of ('login', 'logout', 'jobs', 'groups', 'homepage').

import duckdb
import pandas as pd

def solution(traffic: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Traffic", traffic)
    return con.execute("""WITH
    T AS (
        SELECT
            user_id,
            MIN(activity_date) OVER (PARTITION BY user_id) AS login_date
        FROM Traffic
        WHERE activity = 'login'
    )
SELECT login_date, COUNT(DISTINCT user_id) AS user_count
FROM T
WHERE DATEDIFF('2019-06-30', login_date) <= 90
GROUP BY 1;""").df()