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
    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;


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
82 The top search query on new year's day facebook easy
83 Top search_query in US and UK on new year's day facebook medium
84 Click through rate on new year's day facebook medium
85 Top 4 queries based on click through rate on new year's day facebook hard
96 Overall acceptance rate facebook easy
97 Social influencer facebook easy
98 Most popular user facebook easy
99 Page recommendation facebook medium
100 Advertiser ROI facebook medium
172 Comments distribution facebook hard
174 Closed accounts facebook easy