0% completed
Problem
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.
Problem Definition
Write a solution to find the popularity percentage for each user on Meta/Facebook. The popularity percentage is defined as the total number of friends the user has divided by the total number of users on the platform, then converted into a percentage by multiplying by 100, rounded to 2 decimal places.
Return the result table ordered by user1
in ascending order.
Example
Output
Try It Yourself
Solution
To determine the popularity percentage of each user on Meta/Facebook, we need to calculate the proportion of friends each user has relative to the total number of users on the platform. The popularity percentage is defined as:
\text{Popularity Percentage} = \left( \frac{\text{Number of Friends}}{\text{Total Number of Users}} \right) \times 100
The final result should display each user’s user1
and their corresponding percentage_popularity
, rounded to two decimal places. The results must be ordered by user1
in ascending order.
Approach Overview
-
Transform Friendships to Bidirectional Relationships:
- Ensure that each friendship is represented in both directions (i.e., if User A is friends with User B, then User B is also friends with User A). This facilitates accurate counting of friends for each user.
-
Identify All Unique Users:
- Extract all unique users from both
user1
anduser2
columns to determine the total number of users on the platform.
- Extract all unique users from both
-
Calculate the Number of Friends per User:
- For each user, count the number of unique friends they have.
-
Compute Popularity Percentage:
- For each user, divide the number of friends by the total number of unique users and multiply by 100. Round the result to two decimal places.
-
Finalize the Results:
- Present the
user1
and their correspondingpercentage_popularity
in ascending order ofuser1
.
- Present the
SQL Query
WITH two_way_friendships AS ( SELECT user1, user2 FROM Friends UNION ALL SELECT user2 AS user1, user1 AS user2 FROM Friends ), unique_users AS ( SELECT user1 AS user_id FROM two_way_friendships UNION SELECT user2 AS user_id FROM two_way_friendships ), total_users AS ( SELECT COUNT(*) AS total_count FROM unique_users ) SELECT T.user1, ROUND(100.00 * (COUNT(DISTINCT T.user2) / TU.total_count), 2) AS percentage_popularity FROM two_way_friendships T CROSS JOIN total_users TU GROUP BY T.user1, TU.total_count ORDER BY T.user1;
Step-by-Step Approach
Step 1: Transform Friendships to Bidirectional Relationships (two_way_friendships
CTE)
Ensure that each friendship is represented in both directions to accurately count the number of friends each user has.
SQL Snippet:
WITH two_way_friendships AS ( SELECT user1, user2 FROM Friends UNION ALL SELECT user2 AS user1, user1 AS user2 FROM Friends ) SELECT * FROM two_way_friendships;
Explanation:
UNION ALL
:- Combines the original friendships with their reverse, ensuring that friendships are bidirectional.
- Resulting
two_way_friendships
CTE:
+-------+-------+ | user1 | user2 | +-------+-------+ | 2 | 1 | | 1 | 3 | | 4 | 1 | | 1 | 5 | | 1 | 6 | | 2 | 6 | | 7 | 2 | | 8 | 3 | | 3 | 9 | | 1 | 2 | | 3 | 1 | | 5 | 1 | | 6 | 1 | | 6 | 2 | | 2 | 7 | | 3 | 8 | | 9 | 3 | +-------+-------+
Step 2: Identify All Unique Users (unique_users
CTE)
Determine the total number of unique users on the platform by extracting all distinct users from both user1
and user2
columns.
SQL Snippet:
WITH two_way_friendships AS ( SELECT user1, user2 FROM Friends UNION ALL SELECT user2 AS user1, user1 AS user2 FROM Friends ), unique_users AS ( SELECT user1 AS user_id FROM two_way_friendships UNION SELECT user2 AS user_id FROM two_way_friendships ) SELECT * FROM unique_users;
Explanation:
UNION
:- Combines the list of users from
user1
anduser2
into a single list of unique users.
- Combines the list of users from
- Resulting
unique_users
CTE:
+---------+ | user_id | +---------+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | +---------+
Step 3: Calculate the Total Number of Users (total_users
CTE)
Calculate the total number of unique users on the platform to use as the denominator for the popularity percentage calculation.
SQL Snippet:
WITH two_way_friendships AS ( SELECT user1, user2 FROM Friends UNION ALL SELECT user2 AS user1, user1 AS user2 FROM Friends ), unique_users AS ( SELECT user1 AS user_id FROM two_way_friendships UNION SELECT user2 AS user_id FROM two_way_friendships ), total_users AS ( SELECT COUNT(*) AS total_count FROM unique_users ) SELECT * FROM total_users;
Explanation:
COUNT(*) AS total_count
:- Counts the total number of unique users identified in the
unique_users
CTE.
- Counts the total number of unique users identified in the
- Resulting
total_users
CTE:
+-------------+ | total_count | +-------------+ | 9 | +-------------+
Step 4: Calculate the Number of Friends per User and Compute Popularity Percentage
For each user, count the number of unique friends they have and calculate their popularity percentage based on the total number of users.
SQL Snippet:
WITH two_way_friendships AS ( SELECT user1, user2 FROM Friends UNION ALL SELECT user2 AS user1, user1 AS user2 FROM Friends ), unique_users AS ( SELECT user1 AS user_id FROM two_way_friendships UNION SELECT user2 AS user_id FROM two_way_friendships ), total_users AS ( SELECT COUNT(*) AS total_count FROM unique_users ) SELECT T.user1, ROUND(100.00 * (COUNT(DISTINCT T.user2) / TU.total_count), 2) AS percentage_popularity FROM two_way_friendships T CROSS JOIN total_users TU GROUP BY T.user1, TU.total_count ORDER BY T.user1;
Explanation:
CROSS JOIN total_users TU
:- Associates each row from
two_way_friendships
with the total user count to facilitate the percentage calculation.
- Associates each row from
COUNT(DISTINCT T.user2)
:- Counts the number of unique friends (
user2
) eachuser1
has.
- Counts the number of unique friends (
ROUND(100.00 * (COUNT(DISTINCT T.user2) / TU.total_count), 2) AS percentage_popularity
:- Calculates the popularity percentage by dividing the number of friends by the total number of users, multiplying by 100, and rounding to two decimal places.
GROUP BY T.user1, TU.total_count
:- Groups the data by each user to perform the aggregation.
ORDER BY T.user1
:- Sorts the final results by
user1
in ascending order.
- Sorts the final results by
Intermediate Output After Step 4:
+-------+-----------------------+ | user1 | percentage_popularity | +-------+-----------------------+ | 1 | 55.56 | | 2 | 33.33 | | 3 | 33.33 | | 4 | 11.11 | | 5 | 11.11 | | 6 | 22.22 | | 7 | 11.11 | | 8 | 11.11 | | 9 | 11.11 | +-------+-----------------------+
Explanation of Output:
-
User 1:
- Number of Friends: 5 (Users 2, 3, 4, 5, 6)
- Popularity Percentage: (5 / 9) × 100 ≈ 55.56%
-
User 2:
- Number of Friends: 3 (Users 1, 6, 7)
- Popularity Percentage: (3 / 9) × 100 = 33.33%
-
User 3:
- Number of Friends: 3 (Users 1, 8, 9)
- Popularity Percentage: (3 / 9) × 100 = 33.33%
Step 5: Finalize the Results
Present the user1
and their corresponding percentage_popularity
in ascending order of user1
.
Final Output:
+-------+-----------------------+ | user1 | percentage_popularity | +-------+-----------------------+ | 1 | 55.56 | | 2 | 33.33 | | 3 | 33.33 | | 4 | 11.11 | | 5 | 11.11 | | 6 | 22.22 | | 7 | 11.11 | | 8 | 11.11 | | 9 | 11.11 | +-------+-----------------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible