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
LeetCode Database - Medium
LeetCode 177
LeetCode 178
LeetCode 180
LeetCode 184
LeetCode 534
LeetCode 550
LeetCode 570
LeetCode 574
LeetCode 578
LeetCode 580
LeetCode 585
LeetCode 602
LeetCode 608
LeetCode 612
LeetCode 614
LeetCode 626
LeetCode 1045
LeetCode 1070
LeetCode 1077
LeetCode 1098
LeetCode 1107
LeetCode 1112
LeetCode 1126
More LeetCode Database
MySQL Tutorials