Question

614. Second Degree Follower

中文文档

Description

Table: Follow

+-------------+---------+
| Column Name | Type  |
+-------------+---------+
| followee  | varchar |
| follower  | varchar |
+-------------+---------+
(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 themself.

 

A second-degree follower is a user who:

• follows at least one user, and
• is followed by at least one user.

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:
+----------+----------+
| followee | follower |
+----------+----------+
| Alice  | Bob    |
| Bob    | Cena   |
| Bob    | Donald   |
| Donald   | Edward   |
+----------+----------+
Output: 
+----------+-----+
| follower | num |
+----------+-----+
| Bob    | 2   |
| Donald   | 1   |
+----------+-----+
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 don not include her in the result table.

Solutions

Solution 1

# Write your MySQL query statement below
WITH
  T AS (
    SELECT f1.follower AS follower, f2.follower AS followee
    FROM
      Follow AS f1
      JOIN Follow AS f2 ON f1.follower = f2.followee
  )
SELECT follower, COUNT(DISTINCT followee) AS num
FROM T
GROUP BY 1
ORDER BY 1;