AC code with Clear Explanation for all corner cases


  • 0

    The first thing we should do is understand the question correctly.

    in the example

    +-------------+------------+
    | followee    | follower   |
    +-------------+------------+
    |     A       |     B      |
    |     B       |     C      |
    |     B       |     D      |
    |     D       |     E      |
    +-------------+------------+
    
    +-------------+------------+
    | follower    | num        |
    +-------------+------------+
    |     B       |  2         |
    |     D       |  1         |
    +-------------+------------+
    

    For A, B, D, they have 1, 2, 1 follower correspondingly.
    While we don't output A-1 because A is not in the column follower, which means it is not a Second Degree Follower

    So I came up with code like these(which is WRONG)

    select f1.followee as follower, count(f1.follower) as num 
    from follow f1 where f1.followee in (select distinct follower from follow) 
    group by f1.followee 
    order by follower;
    

    The reason is that there may be duplicate followee-follower as input

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

    The code above output B-2 while we should output B-1
    While it didn't pass when I change the first line into

    select f1.followee as follower, count(distinct f1.follower) as num 
    

    After a while, I realize that there may be something wrong with the alphabet policy. All my output values were correct, but the followee may change from lower case to upper case (actually I have no idea and MySQL seems to be case insensitive)

    So finally I try another answer credit to @xiaxin and get AC.

    select f1.follower, count(distinct f2.follower) as num
    from follow f1
    join follow f2 on f1.follower = f2.followee
    group by f1.follower
    order by f1.follower;
    

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.