sql notes: leetcode#614 second degree follower

Problem

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:

1
2
3
4
5
6
7
8

+-------------+------------+
| followee    | follower   |
+-------------+------------+
|     A       |     B      |
|     B       |     C      |
|     B       |     D      |
|     D       |     E      |
+-------------+------------+

should output:

1
2
3
4
5
6

+-------------+------------+
| follower    | num        |
+-------------+------------+
|     B       |  2         |
|     D       |  1         |
+-------------+------------+

Explaination:
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.

Analysis

Join 2 follow tables. Let followee of the second table be equal to the follower of the first table so that follower in the second table is follower’s follower in the first table. Then group by follower of the first table and count number of follower in the second table for each follower in the first table. Finally exclude duplicates and sort by follower of the first table alphabetically.

Solution

1
2
3

SELECT f1.follower, COUNT(DISTINCT f2.follower) AS num FROM
follow AS f1 JOIN follow AS f2 ON f1.follower = f2.followee
GROUP BY f1.follower ORDER BY f1.follower;

Link

614. Second Degree Follower
(中文版) SQL 笔记: Leetcode#614 Second Degree Follower