173. User Popularity Percentage

medium facebook

  • ****************************************************************************
    • ***************************
  • ************************************************************************

Table: friends

Users and their friends, each row is a pair of friends.

  col_name   | col_type
-------------+--------------------------
 user_id     | bigint
 friend_id   | bigint

Sample results

 user_id |     pop_percentage
---------+------------------------
       1 |     1.0020040080160321
       3 |     1.0020040080160321

Solution postgres

WITH all_users AS (
    SELECT DISTINCT user_id AS user_id FROM friends
    UNION
    SELECT DISTINCT friend_id AS user_id FROM friends
)
SELECT user_id, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM all_users) AS pop_percentage
FROM friends
WHERE user_id IN (1,3,5,7,9)
GROUP BY user_id;
    

Explanation

This query is used to calculate the percentage of a user's popularity among a group of users.

Firstly, it creates a temporary table called 'all_users' that includes all unique user IDs from the 'friends' table. It does this by selecting all distinct user IDs from the 'friends' table and then all distinct friend IDs from the 'friends' table and combining them using the UNION operator.

Next, it selects the user IDs and calculates the popularity percentage for each user. The popularity percentage is calculated by dividing the count of each user's friendships by the total number of users in the 'all_users' table, then multiplying by 100.

Finally, it filters the results to only include users with specific IDs (1,3,5,7,9) and groups the results by user ID.

Overall, this query is useful for analyzing the popularity of specific users within a larger group.

Expected results


More Facebook questions

ID Title Level FTPR
81 How many people searched on new year's day facebook easy
24%
82 The top search query on new year's day facebook easy
13%
83 Top search_query in US and UK on new year's day facebook medium
6%
84 Click through rate on new year's day facebook medium
9%
85 Top 4 queries based on click through rate on new year's day facebook hard
7%
96 Overall acceptance rate facebook easy
13%
97 Social influencer facebook easy
10%
98 Most popular user facebook easy
33%
99 Page recommendation facebook medium
13%
100 Advertiser ROI facebook medium
8%
172 Comments distribution facebook hard
13%
174 Closed accounts facebook easy
23%