Page Recommendations II — LeetCode 1892 Python Solution

Table: Friendship +---------------+---------+ | Column Name | Type | +---------------+---------+ | user1_id | int | | user2_id | int | +---------------+---------+ (user1_id, user2_id) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates that the users user1_id and user2_id are friends.

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user1_id      | int     |
| user2_id      | int     |
+---------------+---------+
(user1_id, user2_id) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the users user1_id and user2_id are friends.

import duckdb
import pandas as pd

def solution(friendship: pd.DataFrame, likes: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Friendship", friendship)
    con.register("Likes", likes)
    return con.execute("""WITH
    S AS (
        SELECT * FROM Friendship
        UNION
        SELECT user2_id, user1_id FROM Friendship
    )
SELECT user1_id AS user_id, page_id, COUNT(1) AS friends_likes
FROM
    S AS s
    LEFT JOIN Likes AS l ON s.user2_id = l.user_id
WHERE
    NOT EXISTS (
        SELECT 1
        FROM Likes AS l2
        WHERE user1_id = l2.user_id AND l.page_id = l2.page_id
    )
GROUP BY user1_id, page_id;""").df()