Popularity Percentage - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min read

Problem Statement

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

Code

MySQL

Try this approach in the editor →

Detailed 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

Video Solution

Leetcode HARD 2720 - Popularity Percentage SUBQUERY in SQL : Explained by Everyday Data Science • Everyday Data Science • 875 views views

Watch 1 more video solutions →

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.

Ready to solve this problem?

Practice Popularity Percentage with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance52.8%

Approaches1

Reading time5 min

Practice this problem

Open in Editor