614 Second Degree Follower

Description

Table: Follow

• (followee, follower) is the primary key (combination of columns with unique values) for this table.
• Each row of this table indicates that the user follower follows the user followee on a social network.
• There will not be a user following themselves.

A second-degree follower is a user who: - follows at least one user, and - is followed by at least one user.

Problem Statement

Write a solution to report the second-degree users and the number of their followers.

Return the result table ordered by follower in alphabetical order.

The result format is in the following example.

Example 1:

Input:

• Follow table:

Output:

Explanation:

• User Bob has 2 followers. Bob is a second-degree follower because he follows Alice, so we include him in the result table.
• User Donald has 1 follower. Donald is a second-degree follower because he follows Bob, so we include him in the result table.
• User Alice has 1 follower. Alice is not a second-degree follower because she does not follow anyone, so we do not include her in the result table.

Solution

The problem asked us to report second-degree users. These are users who follow at least one user and who is followed by at least one user. You can find this by using SELF JOIN based on followee and follower columns.

1SELECT f1.follower, f2.follower
2    FROM Follow f1
3    JOIN Follow f2
4    ON f1.follower = f2.followee;

This will output only three rows.

Once, you’ve this as output, you need to find the number of followers for each second-degree users. You can do this by using COUNT() aggregation function.

So, the final answer is as below.

1SELECT f1.follower, COUNT(DISTINCT f2.follower) AS num
2    FROM Follow f1
3    JOIN Follow f2
4    ON f1.follower = f2.followee
5    GROUP BY f1.follower;