Popularity Percentage - Practice Coding Problems

Popularity Percentage - Problem

Database Hard

Social Media Popularity Calculator

You're working at Meta/Facebook and need to calculate each user's popularity percentage on the platform. Given a friendship table where each row represents a mutual friendship between two users, your task is to determine how popular each user is relative to the entire user base.

The popularity percentage is calculated as:
(Number of friends a user has / Total number of users on platform) × 100

Your solution should:
• Calculate each user's friend count
• Determine the total number of unique users
• Compute popularity percentage rounded to 2 decimal places
• Return results ordered by user ID ascending

Table Schema:
Friends
+-------------+------+
| Column Name | Type |
+-------------+------+
| user1 | int |
| user2 | int |
+-------------+------+
Note: (user1, user2) is the primary key. Each friendship is bidirectional.

Input & Output

example_1.sql — Basic Friendship Network

$ Input: Friends table: +-------+-------+ | user1 | user2 | +-------+-------+ | 1 | 2 | | 2 | 3 | +-------+-------+

› Output: +-------+------------+ | user1 | percentage | +-------+------------+ | 1 | 33.33 | | 2 | 66.67 | | 3 | 33.33 | +-------+------------+

💡 Note: Total users: 3 (1,2,3). User 1: 1 friend (2) → 1/3*100 = 33.33%. User 2: 2 friends (1,3) → 2/3*100 = 66.67%. User 3: 1 friend (2) → 1/3*100 = 33.33%.

example_2.sql — Larger Network

$ Input: Friends table: +-------+-------+ | user1 | user2 | +-------+-------+ | 1 | 2 | | 1 | 3 | | 2 | 3 | | 2 | 4 | +-------+-------+

› Output: +-------+------------+ | user1 | percentage | +-------+------------+ | 1 | 50.00 | | 2 | 75.00 | | 3 | 50.00 | | 4 | 25.00 | +-------+------------+

💡 Note: Total users: 4 (1,2,3,4). User 1: friends with 2,3 → 2/4*100 = 50%. User 2: friends with 1,3,4 → 3/4*100 = 75%. User 3: friends with 1,2 → 2/4*100 = 50%. User 4: friends with 2 → 1/4*100 = 25%.

example_3.sql — Single Friendship

$ Input: Friends table: +-------+-------+ | user1 | user2 | +-------+-------+ | 1 | 2 | +-------+-------+

› Output: +-------+------------+ | user1 | percentage | +-------+------------+ | 1 | 50.00 | | 2 | 50.00 | +-------+------------+

💡 Note: Total users: 2 (1,2). Each user has exactly 1 friend, so both have 1/2*100 = 50% popularity.

Visualization

Tap to expand

Understanding the Visualization

Identify All Students

Collect every unique student ID from both sides of friendship pairs

Count Each Student's Friends

For each student, count how many friendships they appear in

Calculate School Size

Determine the total number of students in the school

Compute Popularity Percentage

Divide each student's friend count by total students, multiply by 100

Key Takeaway

🎯 Key Insight: Use CTEs to efficiently calculate friend counts and total users in a single query, avoiding the O(n²) complexity of nested subqueries while maintaining clean, readable SQL code.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data with efficient joins

✓ Linear Growth

Space Complexity

O(n)

Space for CTEs and result set

⚡ Linearithmic Space

Constraints

1 ≤ Friends table rows ≤ 500
1 ≤ user1, user2 ≤ 10⁶
user1 ≠ user2 (no self-friendships)
Each friendship pair appears only once
Important: Friendships are bidirectional (if user1 friends user2, then user2 friends user1)

Asked in

f Meta 85 in LinkedIn 65 🐦 Twitter 45 📷 Instagram 40 👻 Snapchat 25

The optimal solution uses Common Table Expressions (CTEs) to break down the problem efficiently: First CTE extracts all unique users, second CTE counts friends per user, third CTE gets total user count, then the final query calculates popularity percentages by dividing friend count by total users and multiplying by 100. This approach achieves O(n) time complexity with a single pass through the data.

Common Approaches

Approach	Time	Space	Notes
✓ CTE-Based Optimal Solution	O(n)	O(n)	Use Common Table Expressions to calculate friend counts and total users efficiently in one query
Multiple Subqueries (Brute Force)	O(n²)	O(n)	Use separate subqueries for each calculation step

CTE-Based Optimal Solution — Algorithm Steps

Create CTE to get all unique users from both columns
Create CTE to count friends for each user by checking both user1 and user2 columns
Calculate total unique users count
Compute popularity percentage using friend count / total users * 100
Round to 2 decimal places and order by user1

Visualization

Tap to expand

Step-by-Step Walkthrough

CTE 1: All Users

Extract unique users from both columns

CTE 2: Friend Counts

Count friends for each user efficiently

CTE 3: Total Users

Count total unique users once

Final Calculation

Join CTEs and calculate percentages

Code -

solution.c — C

// Optimal SQL solution using CTEs
#include <stdio.h>

const char* get_optimal_query() {
    static const char* query = 
        "WITH all_users AS (\n"
        "    SELECT user1 AS user_id FROM Friends\n"
        "    UNION\n"
        "    SELECT user2 AS user_id FROM Friends\n"
        "), \n"
        "friend_counts AS (\n"
        "    SELECT \n"
        "        u.user_id,\n"
        "        COUNT(*) AS friend_count\n"
        "    FROM all_users u\n"
        "    LEFT JOIN Friends f ON u.user_id = f.user1 OR u.user_id = f.user2\n"
        "    GROUP BY u.user_id\n"
        "), \n"
        "total_users AS (\n"
        "    SELECT COUNT(*) AS total_count\n"
        "    FROM all_users\n"
        ") \n"
        "SELECT \n"
        "    fc.user_id AS user1,\n"
        "    ROUND((fc.friend_count * 100.0 / tu.total_count), 2) AS percentage\n"
        "FROM friend_counts fc\n"
        "CROSS JOIN total_users tu\n"
        "ORDER BY fc.user_id;";
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data with efficient joins

✓ Linear Growth

Space Complexity

O(n)

Space for CTEs and result set

⚡ Linearithmic Space

Constraints

1 ≤ Friends table rows ≤ 500
1 ≤ user1, user2 ≤ 10⁶
user1 ≠ user2 (no self-friendships)
Each friendship pair appears only once
Important: Friendships are bidirectional (if user1 friends user2, then user2 friends user1)

67.2K Views

High Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

CTE-Based Optimal Solution — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler