Welcome to Subscribe On Youtube
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;