Friend Requests II: Who Has The Most Friends Problem


Description

LeetCode Problem 602.

In social network like Facebook or Twitter, people send friend requests and accept others’ requests as well.

Table request_accepted

1
2
3
4
5
6
7
8
9
+--------------+-------------+------------+
| 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 |
+--------------+-------------+------------+
This table holds the data of friend acceptance, while requester_id and accepter_id both are the id of a person.

Write a query to find the the people who has most friends and the most friends number under the following rules:

  • 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.

For the sample data above, the result is:

1
2
3
4
5
6
7
Result table:
+------+------+
| id   | num  |
|------|------|
| 3    | 3    |
+------+------+
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.


MySQL Solution

1
2
3
4
5
6
7
8
9
10
11
12
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




Related Posts

Human Traffic Of Stadium Problem

LeetCode 601. Write an SQL query to display the records...

Friend Requests II: Who Has The Most Friends Problem

LeetCode 602. In social network like Facebook or Twitter, people...

Tree Node Problem

LeetCode 608. Given a table tree, id is identifier of...

Friend Requests I: Overall Acceptance Rate Problem

LeetCode 597. Write an SQL query to find the overall...

Sales Person Problem

LeetCode 607. Given three tables, salesperson, company, orders.

Consecutive Available Seats Problem

LeetCode 603. Several friends at a cinema ticket office would...