user_id	follower_id
0	1
1	0
2	0
2	1

Output:

user_id	followers_count
0	1
1	1
2	2

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

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

Code

[{"language":"MySQL","code":"SELECT user_id, COUNT(follower_id) AS followers_count\nFROM Followers\nGROUP BY user_id\nORDER BY user_id ASC;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> user_id, </span><span style=\"color:#005CC5;--shiki-dark:#79B8FF\">COUNT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\">(follower_id) </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=

Related Tags