Popularity Percentage — LeetCode 2720 Python Solution

Table: Friends +-------------+------+ | Column Name | Type | +-------------+------+ | user1 | int | | user2 | int | +-------------+------+ (user1, user2) is the primary key (combination of unique values) of this table. Each row contains information about friendship where user1 and user2 are friends.

+-------------+------+
| Column Name | Type |
+-------------+------+
| user1       | int  |
| user2       | int  |
+-------------+------+
(user1, user2) is the primary key (combination of unique values) of this table.
Each row contains information about friendship where user1 and user2 are friends.

import duckdb
import pandas as pd

def solution(friends: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("Friends", friends)
    return con.execute("""WITH
    F AS (
        SELECT * FROM Friends
        UNION
        SELECT user2, user1 FROM Friends
    ),
    T AS (SELECT COUNT(DISTINCT user1) AS cnt FROM F)
SELECT DISTINCT
    user1,
    ROUND(
        (COUNT(1) OVER (PARTITION BY user1)) * 100 / (SELECT cnt FROM T),
        2
    ) AS percentage_popularity
FROM F
ORDER BY 1;""").df()