Leetcodify Similar Friends — LeetCode 1919 Python Solution

Table: Listens +-------------+---------+ | Column Name | Type | +-------------+---------+ | user_id | int | | song_id | int | | day | date | +-------------+---------+ This table may contain duplicate rows. Each row of this table indicates that the user user_id listened to the song song_id on the day day.

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| song_id     | int     |
| day         | date    |
+-------------+---------+
This table may contain duplicate rows.
Each row of this table indicates that the user user_id listened to the song song_id on the day day.

import duckdb
import pandas as pd

def solution(listens: pd.DataFrame, friendship: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Listens", listens)
    con.register("Friendship", friendship)
    return con.execute("""SELECT DISTINCT user1_id, user2_id
FROM
    Friendship AS f
    LEFT JOIN Listens AS l1 ON user1_id = l1.user_id
    LEFT JOIN Listens AS l2 ON user2_id = l2.user_id
WHERE l1.song_id = l2.song_id AND l1.day = l2.day
GROUP BY 1, 2, l1.day
HAVING COUNT(DISTINCT l1.song_id) >= 3;""").df()