Popularity Percentage solution and explanation
1 more video on this problem
The 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.
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.
Approach overview
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.
Solution
# Write your MySQL query statement below
WITH
F AS (
SELECT * FROM Friends
UNION
SELECT user2, user1 FROM Friends
),
T AS (SELECT COUNT(DISTINCT user1) AS cnt FROM F)
SELECT DISTINCT
user1,
ROUND(
(COUNT(1) OVER (PARTITION BY user1)) * 100 / (SELECT cnt FROM T),
2
) AS percentage_popularity
FROM F
ORDER BY 1;Complexity analysis
| 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 |
Frequently asked questions
Is Popularity Percentage easy or hard?
Popularity Percentage is classified as a Hard database problem because it requires transforming bidirectional relationships and combining multiple SQL operations such as UNION ALL, distinct user counting, aggregation, and percentage calculations in a single query.
Popularity Percentage Python/Java solution
This problem is designed for SQL rather than general-purpose languages. On platforms like FleetCode and LeetCode, the intended solution uses MySQL queries with UNION ALL, GROUP BY, and aggregate functions to compute the popularity percentage for each user.
How to solve Popularity Percentage in O(n)?
Scan the Friends table once and normalize relationships using UNION ALL so both users in each pair appear as separate rows. Then GROUP BY the user ID to count friends and compute the percentage using the total number of distinct users from a subquery. SQL aggregation handles the counting efficiently, giving an overall O(n) solution.
What is the best approach for Popularity Percentage?
The UNION ALL plus GROUP BY aggregation approach is the most reliable solution. It converts each friendship pair into two rows so that both users receive a friend count. After normalizing the data, aggregate by user and divide by the total number of users to compute the popularity percentage. The query runs in O(n) time relative to the number of friendship rows.
Is Popularity Percentage asked at Google/Amazon/Meta?
Database aggregation and relationship-counting problems similar to Popularity Percentage appear in SQL interviews at large tech companies. The pattern of transforming bidirectional relationships and computing metrics with GROUP BY is commonly tested in data engineering and backend interview rounds.
What data structure is used in Popularity Percentage?
The problem relies on relational tables and SQL aggregation rather than traditional data structures like arrays or hash maps. Internally, the database engine may use hash-based or sort-based grouping to compute counts during the GROUP BY step.
What is the time complexity of Popularity Percentage?
The typical SQL solution runs in O(n) time where n is the number of rows in the Friends table. The UNION ALL operation scans the table once and the GROUP BY aggregation processes the resulting rows. Space complexity is O(n) because the intermediate normalized dataset may contain up to twice the number of original rows.