L602.md

LeetCode 602

Friend Requests II: Who Has the Most Friends

In social network like Facebook or Twitter, people send friend requests and accept others' requests as well.
Table request_accepted holds the data of friend acceptance, while requester_id and accepter_id both are the id of a person.
| requester_id | accepter_id | accept_date|
|--------------|-------------|------------|
| 1            | 2           | 2016_06-03 |
| 1            | 3           | 2016-06-08 |
| 2            | 3           | 2016-06-08 |
| 3            | 4           | 2016-06-09 |
Write a query to find the the people who has most friends and the most friends number. For the sample data above, the result is:
| id | num |
|----|-----|
| 3  | 3   |
Note:
It is guaranteed there is only 1 people having the most friends.
The friend request could only been accepted once, which mean there is no multiple records with the same requester_id and accepter_id value.
Explanation:
The person with id '3' is a friend of people '1', '2' and '4', so he has 3 friends in total, which is the most number than any others.

Idea

• Use UNION ALL s.t results from both SELECT are combined
• Note JOIN Issue would be handling NULL values
  • LEFT JOIN need to handle potential duplicate ones
  • JOIN would leave out requester_id that DOES NOT APPEAR in accepter_id

Solution

SELECT v1.id, SUM(v1.num) AS num 
FROM

( SELECT requester_id AS id, COUNT(requester_id) AS num 
  FROM request_accepted 
  GROUP BY 1
UNION ALL
 SELECT accepter_id AS id, COUNT(accepter_id) AS num
  FROM request_accepted
  GROUP BY 1 ) v1

GROUP BY v1.id
ORDER BY num DESC
LIMIT 1	

Solution 1

select ids as id, cnt as num
from
(
select ids, count(*) as cnt
   from
   (
        select requester_id as ids from request_accepted
        union all
        select accepter_id from request_accepted
    ) as tbl1
   group by ids
   ) as tbl2
order by cnt desc
limit 1
;	

##Follow-up: ####In the real world, multiple people could have the same most number of friends, can you find all these people in this case?

• Idea: to create v1 again and this time extract the max value
  • now put this max value to current v1 with condition >=