Friend Requests II: Who Has the Most Friends
Task: Write a solution to find the people who have the most friends and the most friends number.
Table: RequestAccepted
+--------------+------+
| Column Name | Type |
+--------------+------+
| requester_id | INT |
| accepter_id | INT |
| accept_date | date |
+--------------+------+Input: RequestAccepted table:
+--------------+-------------+-------------+
| 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 |
+--------------+-------------+-------------+Output:
+----+-----+
| id | num |
+----+-----+
| 3 | 3 |
+----+-----+View Reference Solution & Explanation ▼
This solution uses the `GROUP BY` clause to aggregate results, `UNION` to combine the result sets of multiple queries, a subquery as a derived table or inline value, and `LIMIT 1` to return only the top record after sorting.
SELECT id, COUNT(*) AS num FROM (SELECT requester_id AS id FROM RequestAccepted UNION ALL SELECT accepter_id AS id FROM RequestAccepted) GROUP BY id ORDER BY num DESC LIMIT 1;