Friend Requests II: Who Has Most Friend?


  • 0

    Click here to see the full article post


  • 0
    L

    FIRST TWO SELECT SHOULD BE COMBINED TO ONE SELECT


  • 0
    L

    The outer select is redundant, can just do
    select id, count(1) as num
    from (
    select requester_id as id from request_accepted
    union all
    select accepter_id as id from request_accepted
    ) t
    group by 1
    order by 2 desc
    limit 1
    ;


  • 0
    C

    Other way - SQL

    select top 1 c.nfriends, id from
    (select id, count(*) as nfriends from
    (select accepterid as id
    from friend
    union all
    select requesterid
    from friend) as a
    group by id) c
    order by nfriends desc


  • 0
    S

    I am a little confused about why we have to keep duplicate record? Consider the following event: (1) id 12 send a request to id 18, then id 18 accept; (2) id 18 send a request to id 12, then id 12 accept. If we use union all, id 12 will have two friend id 18. It looks weird.


  • 0
    R
    SELECT 
         id, 
        sum(cnt) num
    FROM 
        ((select accepter_id id , count(accepter_id) cnt from request_accepted group by accepter_id) 
        UNION ALL
         (select requester_id id, count(requester_id)  cnt from request_accepted group by requester_id) ) friends
    
    GROUP BY 
        id
    ORDER BY
        num desc
    LIMIT 1
    

  • 1
    SELECT ids as id , COUNT(*) as num
    FROM 
      (SELECT requester_id as ids FROM request_accepted
       UNION ALL
       SELECT accepter_id as ids FROM request_accepted) AS u
    GROUP BY ids
    ORDER BY COUNT(*) DESC
    LIMIT 1
    

Log in to reply
 

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