1+ -- 614. Second Degree Follower
2+ -- In facebook, there is a follow table with two columns: followee, follower.
3+ --
4+ -- Please write a sql query to get the amount of each follower’s follower if he/she has one.
5+ --
6+ -- For example:
7+ --
8+ -- +-------------+------------+
9+ -- | followee | follower |
10+ -- +-------------+------------+
11+ -- | A | B |
12+ -- | B | C |
13+ -- | B | D |
14+ -- | D | E |
15+ -- +-------------+------------+
16+ -- should output:
17+ -- +-------------+------------+
18+ -- | follower | num |
19+ -- +-------------+------------+
20+ -- | B | 2 |
21+ -- | D | 1 |
22+ -- +-------------+------------+
23+ -- Explaination:
24+ -- 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.
25+ -- Note:
26+ -- Followee would not follow himself/herself in all cases.
27+ -- Please display the result in follower's alphabet order.
28+
29+ select f1 .follower ,count (distinctf2 .follower )as num
30+ from follow f1
31+ inner join follow f2on f1 .follower = f2 .followee
32+ group by f1 .follower