Second Degree Follower 5-10-22

link -  https://www.codingninjas.com/codestudio/problems/second-degree-follower_2119337?topList=top-100-sql-problems&leftPanelTab=0

Problem Statement

In facebook, there is a follow table with two columns: followee, follower.

Please write a sql query to get the amount of each follower’s follower if he/she has one.

For example:

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+
Should output:
+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+
Explanation:
Both B and D exist in the follower list, when as a followee, B's follower is C and D, and D's follower is E. A does not exist in follower list.
Note:
Followee would not follow himself/herself in all cases.
Please display the result in follower's alphabet order.


----------------- solution ----------------------

select followee as follower ,count(follower) as num from follow 
where followee in (select follower from follow)
group by 1