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:
Algorithm
Being friends is bidirectional, so if one person accepts a request from another person, both of them will have one more friend.
Thus, we can union column requester_id and accepter_id, and then count the number of the occurrence of each person.
select requester_id as ids from request_accepted union all select accepter_id from request_accepted;
Note: Here we should use
union all
instead ofunion
becauseunion all
will keep all the records even the 'duplicated' one.
Taking the sample as an example, the output is:
ids |
---|
1 |
1 |
2 |
3 |
2 |
3 |
3 |
4 |
Then it will be fairly easy to get the 'ids' with most occurrence using the same technique as mentioned in problem 580. Customer Placing the Largest Number of Orders.
MySQL
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 ;