602. Friend Requests II

[QuXiangjie]

MySQL does not natively support the FULL OUTER JOIN clause. However, you can emulate it using a combination of LEFT JOIN and UNION.

Question

WITH request AS (
    SELECT requester_id, COUNT(accepter_id) AS accept_num
    FROM RequestAccepted
    GROUP BY requester_id
),
accept AS (
    SELECT accepter_id, COUNT(requester_id) AS request_num
    FROM RequestAccepted
    GROUP BY accepter_id
)
friend_counts AS (
    SELECT COALESCE(request.requester_id, accept.accepter_id) AS user_id,
           COALESCE(request.accept_num, 0) AS num_requests,
           COALESCE(accept.request_num, 0) AS num_accepts
    FROM request
    LEFT JOIN accept ON request.requester_id = accept.accepter_id

    UNION

    SELECT COALESCE(request.requester_id, accept.accepter_id) AS user_id,
           COALESCE(request.accept_num, 0) AS num_requests,
           COALESCE(accept.request_num, 0) AS num_accepts
    FROM request
    RIGHT JOIN accept ON request.requester_id = accept.accepter_id
)
SELECT user_id as id, (num_requests + num_accepts) AS num
FROM friend_counts
ORDER BY num DESC
LIMIT 1;

I think there are more better solutions

with cte as (
select requester_id as id from requestaccepted
union all 
select accepter_id as id from requestaccepted)

select id, count(id) as num from cte
group by id
order by count(id) desc
limit 1

Or, we can use rank function

select id, num
from 
(
   SELECT id, 
      COUNT(id) AS num, 
      RANK () OVER(ORDER BY COUNT(id) DESC) AS rnk
   FROM cte
   GROUP BY id
   )
WHERE rnk=1
)

This solution extract all the id of request and accept then group the id and count the number, it is easy and simple solution