Formatted question description: https://leetcode.ca/all/614.html

614. Second Degree Follower

Level

Medium

Description

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         |
+-------------+------------+

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.

Solution

Decompose this problem into two steps. The first step is to select each follower’s number of followers, where the followers with zero followers are also selected. The second step is to select the entries from the first step that have numbers greater than zero.

For the first step, join follower table with itself and use count(distinct) and group by to obtain the number of followers of each follower.

For the second step, based on the result of the first step, only select the entries with num greater than 0, and sort the entries according to follower in ascending order.

# Write your MySQL query statement below
select follower, num from (
    select f1.follower as follower, count(distinct f2.follower) as num
        from follow f1 left join follow f2
        on f1.follower = f2.followee
        group by f1.follower) as nums
    where num > 0
    order by follower;

All Problems

All Solutions