Watch 5 video solutions for Popularity Percentage, a hard level problem involving Database. This walkthrough by Data Engineering has 2,551 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text 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.
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.
The result format is in the following example.
Example 1:
Input: Friends table: +-------+-------+ | user1 | user2 | +-------+-------+ | 2 | 1 | | 1 | 3 | | 4 | 1 | | 1 | 5 | | 1 | 6 | | 2 | 6 | | 7 | 2 | | 8 | 3 | | 3 | 9 | +-------+-------+ 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 | +-------+-----------------------+ Explanation: There are total 9 users on the platform. - User "1" has friendships with 2, 3, 4, 5 and 6. Therefore, the percentage popularity for user 1 would be calculated as (5/9) * 100 = 55.56. - User "2" has friendships with 1, 6 and 7. Therefore, the percentage popularity for user 2 would be calculated as (3/9) * 100 = 33.33. - User "3" has friendships with 1, 8 and 9. Therefore, the percentage popularity for user 3 would be calculated as (3/9) * 100 = 33.33. - User "4" has friendships with 1. Therefore, the percentage popularity for user 4 would be calculated as (1/9) * 100 = 11.11. - User "5" has friendships with 1. Therefore, the percentage popularity for user 5 would be calculated as (1/9) * 100 = 11.11. - User "6" has friendships with 1 and 2. Therefore, the percentage popularity for user 6 would be calculated as (2/9) * 100 = 22.22. - User "7" has friendships with 2. Therefore, the percentage popularity for user 7 would be calculated as (1/9) * 100 = 11.11. - User "8" has friendships with 3. Therefore, the percentage popularity for user 8 would be calculated as (1/9) * 100 = 11.11. - User "9" has friendships with 3. Therefore, the percentage popularity for user 9 would be calculated as (1/9) * 100 = 11.11. user1 is sorted in ascending order.
Problem Overview: The Popularity Percentage problem asks you to calculate how popular each user is based on friendships stored in a Friends table. Each row represents a friendship between two users. For every user, you need to count how many friends they have and convert that count into a percentage relative to the total number of users in the system.
Approach 1: UNION + Aggregation (O(n) time, O(n) space)
The key challenge is that each friendship row contains two users, so every relationship contributes to the friend count of both participants. The clean approach is to normalize the table by using UNION ALL to convert each pair into two rows: (user1_id → user2_id) and (user2_id → user1_id). After this transformation, each row represents a single user having one friend. You then GROUP BY the user ID to count friends and compute the percentage using the total number of distinct users. This relies on standard SQL aggregation patterns commonly used in database interview questions.
Next, compute the total number of users using a subquery that extracts distinct IDs from both columns. The popularity percentage is calculated by dividing the friend count by the total number of users and multiplying by 100. Many implementations use ROUND() to format the result to two decimal places. The final query combines the aggregated friend counts with the total user count using a cross join or scalar subquery.
This structure keeps the logic simple: normalize relationships, count friends per user, then compute the percentage. SQL engines handle the heavy lifting through grouping and aggregation, which makes the query efficient even for large datasets.
Recommended for interviews: The UNION ALL + GROUP BY aggregation pattern is the expected solution. Interviewers want to see that you can normalize bidirectional relationships before aggregation and correctly compute derived metrics like percentages. A naive attempt that counts only one column misses half the relationships, while the normalized approach shows strong understanding of SQL transformations and data aggregation techniques.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Direct Count on Single Column | O(n) | O(1) | Only works if friendships are stored in a single-direction format |
| UNION ALL + GROUP BY Aggregation | O(n) | O(n) | General solution for bidirectional friendship tables |
| CTE + Aggregation | O(n) | O(n) | Preferred when structuring complex SQL queries for readability |
