First and Last Call On the Same Day — LeetCode 1972 Python Solution

Table: Calls +--------------+----------+ | Column Name | Type | +--------------+----------+ | caller_id | int | | recipient_id | int | | call_time | datetime | +--------------+----------+ (caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table. Each row contains information about the time of a phone call between caller_id and recipient_id.

+--------------+----------+
| Column Name  | Type     |
+--------------+----------+
| caller_id    | int      |
| recipient_id | int      |
| call_time    | datetime |
+--------------+----------+
(caller_id, recipient_id, call_time) is the primary key (combination of columns with unique values) for this table.
Each row contains information about the time of a phone call between caller_id and recipient_id.

import duckdb
import pandas as pd

def solution(calls: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Calls", calls)
    return con.execute("""with s as (
    select
        *
    from
        Calls
    union
    all
    select
        recipient_id,
        caller_id,
        call_time
    from
        Calls
),
t as (
    select
        caller_id user_id,
        FIRST_VALUE(recipient_id) over(
            partition by DATE_FORMAT(call_time, '%Y-%m-%d'),
            caller_id
            order by
                call_time asc
        ) first,
        FIRST_VALUE(recipient_id) over(
            partition by DATE_FORMAT(call_time, '%Y-%m-%d'),
            caller_id
            order by
                call_time desc
        ) last
    from
        s
)
select
    distinct user_id
from
    t
where
    first = last""").df()