[SQL_Q] 602. Friend Requests II: Who Has the Most Friends

https://leetcode.com/problems/friend-requests-ii-who-has-the-most-friends/

문제

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.

The result format is in the following example.

 

Example 1:

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.
 

Follow up: In the real world, multiple people could have the same most number of friends. Could you find all these people in this case?

내 풀이

with a as (
SELECT DISTINCT requester_id,accepter_id FROM RequestAccepted
UNION 
SELECT DISTINCT accepter_id,requester_id FROM RequestAccepted
)
SELECT requester_id as id, count(*) as num
FROM a 
GROUP BY requester_id
order by num DESC
LIMIT 1

다른 사람 풀이

SELECT id,
       COUNT(*) AS num
FROM(
    SELECT requester_id AS id
    FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id
    FROM RequestAccepted
) AS all_id
GROUP BY id
ORDER BY num DESC
LIMIT 1

다른 사람 풀이 2

WITH all_ids AS (
   SELECT requester_id AS id 
   FROM RequestAccepted
   UNION ALL
   SELECT accepter_id AS id
   FROM RequestAccepted)
SELECT id, 
   COUNT(id) AS num
FROM all_ids
GROUP BY id
ORDER BY COUNT(id) DESC
LIMIT 1

내 쿼리 문제점

단점

• UNION은 중복 제거로 약간 느림.
• DISTINCT는 불필요한 연산 추가.

성능

• UNION과 DISTINCT로 인해 약간 비효율적.

제약조건 - (requester_id, accepter_id) is the primary key 를 보면 중복이 있을 수 없다는 것을 알아야했다. -> 그러므로 distinct, UNION 을 쓸 필요가 없다는 얘기.

수정된 SQL

with all_Ids as (
    SELECT requester_id as id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id as id FROM RequestAccepted
)
SELECT id, count(id) as num
FROM all_Ids
GROUP BY id
order by count(id) DESC
LIMIT 1

생각해 볼 포인트 : Distinct, Union, Union all 선택 조건 생각해보기.