SQL Query: Second Degree Follower

QUESTION: 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: 

Step1) implement self join on follow table
Step2) join follower of left table to the followee of right table using inner join
Step3) group by left table follower id and take the count

select f.follower , count(fr.followee) as num from follow f
inner join follow fr 
on f.follower = fr.followee
group by f.follower

Post Reference: Vikram Aristocratic Elfin Share