Problem

Given a table Followers with columns user_id and follower_id, write a query to find the number of followers for each user. Return the result table ordered by user_id in ascending order.

Examples

Example 1

Followers table:

user_idfollower_id
01
10
20
21

Output:

user_idfollowers_count
01
11
22
1
2
3
4

Explanation: 
The followers of 0 are {1}
The followers of 1 are {0}
The followers of 2 are {0,1}

Constraints

  • Each row in the table represents a follower relationship.
  • Each user_id and follower_id is an integer.
  • The table may contain multiple users and followers.

Solution

Method 1 – Group By and Count

Intuition

To find the number of followers for each user, we count how many times each user_id appears in the Followers table. Each row represents a follower relationship, so grouping by user_id and counting gives the answer.

Approach

  1. Group the table by user_id.
  2. Count the number of rows for each user_id to get the number of followers.
  3. Return the result ordered by user_id in ascending order.

Code

1
2
3
4

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id ASC;
1
2
3
4

SELECT user_id, COUNT(follower_id) AS followers_count
FROM Followers
GROUP BY user_id
ORDER BY user_id ASC;
1
2
3
4
5

def followers_count(df: 'pd.DataFrame') -> 'pd.DataFrame':
    ans = df.groupby('user_id', as_index=False)['follower_id'].count()
    ans.rename(columns={'follower_id': 'followers_count'}, inplace=True)
    ans.sort_values('user_id', inplace=True)
    return ans

Complexity

  • ⏰ Time complexity: O(n) where n is the number of rows in the Followers table, since each row is processed once for grouping and counting.
  • 🧺 Space complexity: O(m) where m is the number of unique user_ids, as the result stores one row per user.