728x90
문제
Table: RequestAccepted
+----------------+---------+
| Column Name | Type |
+----------------+---------+
| requester_id | int |
| accepter_id | int |
| accept_date | date |
+----------------+---------+(requester_id, accepter_id) is the primary key (combination of columns with unique values) for this table.
This table contains the ID of the user who sent the request, the ID of the user who received the request, and the date when the request was accepted.
Write a solution to find the people who have the most friends and the most friends number.
The test cases are generated so that only one person has the most friends.
https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/description/
예시
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 |
+----+-----+
Explanation:
The person with id 3 is a friend of people 1, 2, and 4, so he has three friends in total, which is the most number than any others.문제 풀이
SELECT requester_id AS id,
COUNT(*) AS num
FROM (
SELECT requester_id
FROM RequestAccepted
UNION ALL
SELECT accepter_id AS requester_id
FROM RequestAccepted
) AS merged
GROUP BY requester_id
ORDER BY num DESC
LIMIT 1;SQL을 독학하시는 분들에게 도움이 되길 바라며,
혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.
728x90
