User Network Reach Percentage

Hard

A community analytics platform stores pairwise user connections in a friendship table. Table: Friends user1: first user in a friendship pair user2: second user in a friendship pair Each row means user1 and user2 are connected. Treat friendships as undirected edges. Popularity percentage definition: Let total_users be the count of distinct users appearing in either user1 or user2. Let friend_count(u) be the number of distinct users directly connected to user u. popularity(u) = (friend_count(u) / total_users) * 100 Round to 2 decimal places using HALF_UP behavior. Task: For every distinct user in the table, return: user1 percentage_popularity Output requirements: Return one row per distinct user. Sort rows by user1 in ascending order. If Friends has no rows, return an empty result. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Friends:
| user1 | user2 |
|-------|-------|
| 2     | 1     |
| 1     | 3     |
| 4     | 1     |
| 1     | 5     |
| 1     | 6     |
| 2     | 6     |
| 7     | 2     |
| 8     | 3     |
| 3     | 9     |

Output

[
  {"user1":1,"percentage_popularity":55.56},
  {"user1":2,"percentage_popularity":33.33},
  {"user1":3,"percentage_popularity":33.33},
  {"user1":4,"percentage_popularity":11.11},
  {"user1":5,"percentage_popularity":11.11},
  {"user1":6,"percentage_popularity":22.22},
  {"user1":7,"percentage_popularity":11.11},
  {"user1":8,"percentage_popularity":11.11},
  {"user1":9,"percentage_popularity":11.11}
]

Explanation

There are 9 distinct users total. User 1 has five distinct friends, so percentage is (5/9)*100 = 55.56. The same rule is applied to every user.

Example 2

Input

Friends:
| user1 | user2 |
|-------|-------|
| 10    | 20    |
| 20    | 30    |
| 20    | 40    |
| 50    | 20    |

Output

[
  {"user1":10,"percentage_popularity":20.0},
  {"user1":20,"percentage_popularity":80.0},
  {"user1":30,"percentage_popularity":20.0},
  {"user1":40,"percentage_popularity":20.0},
  {"user1":50,"percentage_popularity":20.0}
]

Explanation

There are 5 users total. User 20 is connected to four users, so percentage is 80.00.

Example 3

Input

Friends:
[]

Output

[]

Explanation

No rows means no users to evaluate, so the result is empty.

Constraints

0 <= number of rows in Friends <= 200000
1 <= user1, user2 <= 1000000000
user1 != user2
(user1, user2) pairs are unique
At most one row exists for each unordered friendship pair
Popularity denominator is the count of distinct users from both columns
Return exactly two columns: user1, percentage_popularity
Round percentage to exactly 2 decimal places using HALF_UP behavior
If Friends is empty, return an empty result set
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: user1, percentage_popularity

Friends: | user1 | user2 | |-------|-------| | 2 | 1 | | 1 | 3 | | 4 | 1 | | 1 | 5 | | 1 | 6 | | 2 | 6 | | 7 | 2 | | 8 | 3 | | 3 | 9 |

[ {"user1":1,"percentage_popularity":55.56}, {"user1":2,"percentage_popularity":33.33}, {"user1":3,"percentage_popularity":33.33}, {"user1":4,"percentage_popularity":11.11}, {"user1":5,"percentage_popularity":11.11}, {"user1":6,"percentage_popularity":22.22}, {"user1":7,"percentage_popularity":11.11}, {"user1":8,"percentage_popularity":11.11}, {"user1":9,"percentage_popularity":11.11} ]

[ {"user1":10,"percentage_popularity":20.0}, {"user1":20,"percentage_popularity":80.0}, {"user1":30,"percentage_popularity":20.0}, {"user1":40,"percentage_popularity":20.0}, {"user1":50,"percentage_popularity":20.0} ]